Using Cell Data s Sheet Reference

S

StompS

I would like to have sheets named after a certain text string in a cell and
refernce them in formulas

For example:

I have a sheet named '123Main' and a sheet named 'Main'
On the 'Main Sheet' column 'B' is labelled 'Address'
Cell B5 has text data '123Main'
I want to be able to reference from Main!C5 a formula as such:

="whatever text is in cell Main!B5 is the sheetname to reference a cell on
that sheet"

I hope it is clear what I'm trying to do.......thanks.
 
D

Dave Peterson

=INDIRECT("'"&main!B5&"'!A1")

Or since the formula is on that Main worksheet
=INDIRECT("'"&B5&"'!A1")



will point at A1 of the sheet name in B5 on the Main worksheet.
 
S

StompS

Thank you very, very much!!!!!


Dave Peterson said:
=INDIRECT("'"&main!B5&"'!A1")

Or since the formula is on that Main worksheet
=INDIRECT("'"&B5&"'!A1")



will point at A1 of the sheet name in B5 on the Main worksheet.
Click to expand...
 
S

StompS

Something's not working.......here's what I have and I'm getting an error

=INDIRECT("'"&B26&"'!B8) (cell C26 on 'GCP' worksheet)
'4339 NE Alberta' (cell B26 on 'GCP' worksheet)
'4339 NE Alberta' (name of worksheet that is referenced
in the INDIRECT function, cell B8 is the referenced cell)
 
S

StompS

Just to clarify....

I am trying to just take the data from cell B8 on the '4339 NE Alberta'
worksheet and put it into cell C26 on the 'GCP' worksheet. Cell B26 on the
'GCP' worksheet contains "4339 NE Alberta" (which is the subject property
address). I am referencing that cell to tell Excel which worksheet to find
the data.

StompS said:
Something's not working.......here's what I have and I'm getting an error

=INDIRECT("'"&B26&"'!B8) (cell C26 on 'GCP' worksheet)
'4339 NE Alberta' (cell B26 on 'GCP' worksheet)
'4339 NE Alberta' (name of worksheet that is referenced
in the INDIRECT function, cell B8 is the referenced cell)
Click to expand...
 
D

Dave Peterson

Now I really think it was the final double quote.
Just to clarify....

I am trying to just take the data from cell B8 on the '4339 NE Alberta'
worksheet and put it into cell C26 on the 'GCP' worksheet. Cell B26 on the
'GCP' worksheet contains "4339 NE Alberta" (which is the subject property
address). I am referencing that cell to tell Excel which worksheet to find
the data.
Click to expand...
 
Top