how do I convert text string into a cell reference

D

Dave Davis

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 ??
 
D

Debra Dalgleish

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")
 
D

Dave Davis

Many Thanks Debra, this works well. I had tried INDIRECT but obviously could
not quite get the syntax right. Thanks for your help,
 
C

cm

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
 
Top