Using Cell Data s Sheet Reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StompS - 17 Jan 2006 17:16 GMT

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.

Signature

StompS
Portland, OR
http://www.geocities.com/pdxinvestr/Stomps.html

Reply to this Message

Dave Peterson - 17 Jan 2006 18:33 GMT

=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.

> I would like to have sheets named after a certain text string in a cell and
> refernce them in formulas
[quoted text clipped - 15 lines]
> Portland, OR
> http://www.geocities.com/pdxinvestr/Stomps.html

Signature

Dave Peterson

Reply to this Message

StompS - 17 Jan 2006 19:01 GMT

Thank you very, very much!!!!!

> =INDIRECT("'"&main!B5&"'!A1")
>
[quoted text clipped - 24 lines]
>> Portland, OR
>> http://www.geocities.com/pdxinvestr/Stomps.html

Reply to this Message

StompS - 17 Jan 2006 19:11 GMT

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)

> =INDIRECT("'"&main!B5&"'!A1")
>
[quoted text clipped - 24 lines]
>> Portland, OR
>> http://www.geocities.com/pdxinvestr/Stomps.html

Reply to this Message

Dave Peterson - 17 Jan 2006 19:16 GMT

I think...

=INDIRECT("'"&B26&"'!B8)
should be:
=INDIRECT("'"&B26&"'!B8")

> Something's not working.......here's what I have and I'm getting an error
>
[quoted text clipped - 35 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

StompS - 17 Jan 2006 19:21 GMT

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.

> Something's not working.......here's what I have and I'm getting an error
>
[quoted text clipped - 31 lines]
>>> Portland, OR
>>> http://www.geocities.com/pdxinvestr/Stomps.html

Reply to this Message

Dave Peterson - 17 Jan 2006 19:24 GMT

Now I really think it was the final double quote.

> Just to clarify....
>
[quoted text clipped - 43 lines]
> >>
> >> Dave Peterson

Signature

Dave Peterson

Reply to this Message