text reference in formula

Topic

New Reply

I am trying to find a way to allow text entered into a cell to be used as part of a formula. For example, I have a workbook containing identical worksheets with a different dataset on each worksheet and one worksheet which contains a chart with a range of data I want to graph. I want to enter the name of the worksheet containing the data to be graphed into a cell on the graph worksheet and have that name picked up by formulas in the range being referenced by the chart. Any suggestions?

Reply | Quote

Replies

It’s a little unclear what you want. Do you want a text result such as, where d1 contains the spreadsheet name and d2 contains the date:
=D1&” “&TEXT(D2,”mm/dd/yyy”)

or do you want a formula which refers to a cell in that other spreadsheet, depending on which spreadsheet is named, such as, where d1 contains the spreadsheet name and d3 in that other spreadsheet contains the value you want to reference:
=INDIRECT(“‘”&D1&”‘!”&”d3”)

Reply | Quote

Thanks for your response. It put me on the right track. I was trying to achieve the second of your examples. This is what worked for me- =INDIRECT(“”&C23&”!”&”h3″) , where C23 is the cell containing the name of the worksheet which contains the data, and H3 contains the cell reference in that worksheet which contains the value I want to bring in.

More generally, =INDIRECT(“”&C23&”!”&A30&””) does the same thing, but cell A30 contains the cell reference in the data sheet (the H3 in this case). This allows the formula to be copied and have the H3 become H4, H5, etc. and it’s easy enough to create that sequence of text using Excel’s drag and copy feature.

Reply | Quote

Glad to help. Note that if your indirectly referenced spreadsheet has a space in the name, the indirect reference will need to be built as
‘my name’!rc
as opposed to
myname!rc
where rc are the row and columns refs

Reply | Quote

Another limitation of the Indirect function…
If the reference is in an external workbook, the workbook must be open or Indirect will return #REF.

Reply | Quote