| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting values from names by reference
I am looking to get a value from a name created by concatination. For Example
I have a Column with January in B2 and a Row with Income in A4. I have a named reference of Income with a scope of January. I also have the other months as well. I want to create the reference [CONCATENATE(B$2,"!",$A4)] and get the value of that reference in the cell. If I use indirect, I get a reference error. Anyone have any ideas? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting values from names by reference
Maybe...
=indirect("'" & b$2 & "'!a4") But I'm confused about this Row in A4. I'm assuming that you meant an address or a range name???? If this doesn't help, you may want to share the values that you have in each of those cells. (January isn't just the result of a date that's formatted to only show the month name, right???) Charlie Smith wrote: I am looking to get a value from a name created by concatination. For Example I have a Column with January in B2 and a Row with Income in A4. I have a named reference of Income with a scope of January. I also have the other months as well. I want to create the reference [CONCATENATE(B$2,"!",$A4)] and get the value of that reference in the cell. If I use indirect, I get a reference error. Anyone have any ideas? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting values from names by reference
A4 is "Income" and B2 is "January"
On the January Sheet I have a named cell of Income. I want to create a formula that gets the value from the reference but I want to be able to use the formula across the spreadsheet. January!Income February!Income etc January!Expense February!Expense etc etc. With Column A being the name and Row 2 being the sheet names, I should be able to get the values withut having to enter the name directly into the cell. "Dave Peterson" wrote: Maybe... =indirect("'" & b$2 & "'!a4") But I'm confused about this Row in A4. I'm assuming that you meant an address or a range name???? If this doesn't help, you may want to share the values that you have in each of those cells. (January isn't just the result of a date that's formatted to only show the month name, right???) Charlie Smith wrote: I am looking to get a value from a name created by concatination. For Example I have a Column with January in B2 and a Row with Income in A4. I have a named reference of Income with a scope of January. I also have the other months as well. I want to create the reference [CONCATENATE(B$2,"!",$A4)] and get the value of that reference in the cell. If I use indirect, I get a reference error. Anyone have any ideas? -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting values from names by reference
Does this work ok?
=indirect("'" & b$2 & "'!" & a$4) Charlie Smith wrote: A4 is "Income" and B2 is "January" On the January Sheet I have a named cell of Income. I want to create a formula that gets the value from the reference but I want to be able to use the formula across the spreadsheet. January!Income February!Income etc January!Expense February!Expense etc etc. With Column A being the name and Row 2 being the sheet names, I should be able to get the values withut having to enter the name directly into the cell. "Dave Peterson" wrote: Maybe... =indirect("'" & b$2 & "'!a4") But I'm confused about this Row in A4. I'm assuming that you meant an address or a range name???? If this doesn't help, you may want to share the values that you have in each of those cells. (January isn't just the result of a date that's formatted to only show the month name, right???) Charlie Smith wrote: I am looking to get a value from a name created by concatination. For Example I have a Column with January in B2 and a Row with Income in A4. I have a named reference of Income with a scope of January. I also have the other months as well. I want to create the reference [CONCATENATE(B$2,"!",$A4)] and get the value of that reference in the cell. If I use indirect, I get a reference error. Anyone have any ideas? -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting values from names by reference
=INDIRECT("'" & B$2 & "'!" & A$4) gets a #REF error
Still trying to figure out why. "Dave Peterson" wrote: Does this work ok? =indirect("'" & b$2 & "'!" & a$4) Charlie Smith wrote: A4 is "Income" and B2 is "January" On the January Sheet I have a named cell of Income. I want to create a formula that gets the value from the reference but I want to be able to use the formula across the spreadsheet. January!Income February!Income etc January!Expense February!Expense etc etc. With Column A being the name and Row 2 being the sheet names, I should be able to get the values withut having to enter the name directly into the cell. "Dave Peterson" wrote: Maybe... =indirect("'" & b$2 & "'!a4") But I'm confused about this Row in A4. I'm assuming that you meant an address or a range name???? If this doesn't help, you may want to share the values that you have in each of those cells. (January isn't just the result of a date that's formatted to only show the month name, right???) Charlie Smith wrote: I am looking to get a value from a name created by concatination. For Example I have a Column with January in B2 and a Row with Income in A4. I have a named reference of Income with a scope of January. I also have the other months as well. I want to create the reference [CONCATENATE(B$2,"!",$A4)] and get the value of that reference in the cell. If I use indirect, I get a reference error. Anyone have any ideas? -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Getting values from names by reference
Found the issue - the indirect works just fune, It's the name I mis-spelled.
Thanks "Dave Peterson" wrote: Does this work ok? =indirect("'" & b$2 & "'!" & a$4) Charlie Smith wrote: A4 is "Income" and B2 is "January" On the January Sheet I have a named cell of Income. I want to create a formula that gets the value from the reference but I want to be able to use the formula across the spreadsheet. January!Income February!Income etc January!Expense February!Expense etc etc. With Column A being the name and Row 2 being the sheet names, I should be able to get the values withut having to enter the name directly into the cell. "Dave Peterson" wrote: Maybe... =indirect("'" & b$2 & "'!a4") But I'm confused about this Row in A4. I'm assuming that you meant an address or a range name???? If this doesn't help, you may want to share the values that you have in each of those cells. (January isn't just the result of a date that's formatted to only show the month name, right???) Charlie Smith wrote: I am looking to get a value from a name created by concatination. For Example I have a Column with January in B2 and a Row with Income in A4. I have a named reference of Income with a scope of January. I also have the other months as well. I want to create the reference [CONCATENATE(B$2,"!",$A4)] and get the value of that reference in the cell. If I use indirect, I get a reference error. Anyone have any ideas? -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| reference other worksheets via variable names | Excel Worksheet Functions | |||
| Fixing sheet names is a 3D reference | New Users to Excel | |||
| worksheet names in drop down list for reference | Excel Discussion (Misc queries) | |||
| reference to range names | Excel Discussion (Misc queries) | |||
| reference to sheets without using sheet names | Excel Worksheet Functions | |||
Linear Mode
