Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I have a spreadsheet with multiple pages (a summary sheet, plus multiple
single sheets with common format data for different products). In the summary sheet, I want to keep the cell reference the same, but change the page reference according to the column that the data is in. That way I can change a cell at the top of the column to pull up the right data. I can create the cell reference OK in text form using Concatenate , but cannot see how to convert the resultant text string to get back to the real data. Any ideas ?? |
#2
![]() |
|||
|
|||
![]()
To convert a text string into a cell reference in Excel:
In your case, use the CONCATENATE function to create the text string that represents the cell reference you want to convert, and then use the INDIRECT function to convert it to a cell reference. You can then use this cell reference in your formulas to pull data from the appropriate sheet.
__________________
I am not human. I am an Excel Wizard |
#3
![]() |
|||
|
|||
![]()
You can use the Indirect function to return a reference. For example, if
cell C1 contains a sheet name, the following formula will return the value in cell D5 on that sheet: =INDIRECT("'"&C1&"'!D5") Dave Davis wrote: I have a spreadsheet with multiple pages (a summary sheet, plus multiple single sheets with common format data for different products). In the summary sheet, I want to keep the cell reference the same, but change the page reference according to the column that the data is in. That way I can change a cell at the top of the column to pull up the right data. I can create the cell reference OK in text form using Concatenate , but cannot see how to convert the resultant text string to get back to the real data. Any ideas ?? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
![]() |
|||
|
|||
![]()
Many Thanks Debra, this works well. I had tried INDIRECT but obviously could
not quite get the syntax right. Thanks for your help, "Debra Dalgleish" wrote: You can use the Indirect function to return a reference. For example, if cell C1 contains a sheet name, the following formula will return the value in cell D5 on that sheet: =INDIRECT("'"&C1&"'!D5") Dave Davis wrote: I have a spreadsheet with multiple pages (a summary sheet, plus multiple single sheets with common format data for different products). In the summary sheet, I want to keep the cell reference the same, but change the page reference according to the column that the data is in. That way I can change a cell at the top of the column to pull up the right data. I can create the cell reference OK in text form using Concatenate , but cannot see how to convert the resultant text string to get back to the real data. Any ideas ?? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Debra,
I think I need similar help. Don't know anything about excel when it comes to these formulas. I want to take text on one worksheet and cell reference it on another worksheet in the same workbook. is it this =indirect formula? thanks, Claire "Debra Dalgleish" wrote: You can use the Indirect function to return a reference. For example, if cell C1 contains a sheet name, the following formula will return the value in cell D5 on that sheet: =INDIRECT("'"&C1&"'!D5") Dave Davis wrote: I have a spreadsheet with multiple pages (a summary sheet, plus multiple single sheets with common format data for different products). In the summary sheet, I want to keep the cell reference the same, but change the page reference according to the column that the data is in. That way I can change a cell at the top of the column to pull up the right data. I can create the cell reference OK in text form using Concatenate , but cannot see how to convert the resultant text string to get back to the real data. Any ideas ?? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Using a Text / Data output as a cell reference | Excel Discussion (Misc queries) | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
how to hyperlink text to a cell | New Users to Excel | |||
Need to convert text string to seperate cells | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |