Formula to lookup named reference based on value

  • Thread starter Thread starter Santa-D
  • Start date Start date
S

Santa-D

I cant' figure out what the exact words that I'm looking for and if I
did I would most likely find the answer.

I've got a spreadsheet that has a series of numbers in say column A
and it has the numbers 1,2,3,4,5

Further on in the spreadsheet I have named values like CA.1, CA.2, CA.
3

What I want to do is in the formula go:

=(X1/SUM(X:X,X,X:X)*CA.2) (for the named range CA.2)
Is it possible to have something that goes....

=(X1/SUM(X:X,X,X:X)*"CA"&B4) where B4 = 2 Thus the outcome would be
CA.2 ?????
 
=(X1/SUM(X:X,X,X:X)*INDIRECT("CA"&B4))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I cant' figure out what the exact words that I'm looking for and if I
| did I would most likely find the answer.
|
| I've got a spreadsheet that has a series of numbers in say column A
| and it has the numbers 1,2,3,4,5
|
| Further on in the spreadsheet I have named values like CA.1, CA.2, CA.
| 3
|
| What I want to do is in the formula go:
|
| =(X1/SUM(X:X,X,X:X)*CA.2) (for the named range CA.2)
| Is it possible to have something that goes....
|
| =(X1/SUM(X:X,X,X:X)*"CA"&B4) where B4 = 2 Thus the outcome would be
| CA.2 ?????
|
 
"CA" should probably be "CA."

Kind regards,

Niek Otten
Microsoft MVP - Excel

| =(X1/SUM(X:X,X,X:X)*INDIRECT("CA"&B4))
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||I cant' figure out what the exact words that I'm looking for and if I
|| did I would most likely find the answer.
||
|| I've got a spreadsheet that has a series of numbers in say column A
|| and it has the numbers 1,2,3,4,5
||
|| Further on in the spreadsheet I have named values like CA.1, CA.2, CA.
|| 3
||
|| What I want to do is in the formula go:
||
|| =(X1/SUM(X:X,X,X:X)*CA.2) (for the named range CA.2)
|| Is it possible to have something that goes....
||
|| =(X1/SUM(X:X,X,X:X)*"CA"&B4) where B4 = 2 Thus the outcome would be
|| CA.2 ?????
||
|
|
 
Back
Top