Using Cell Data s Sheet Reference

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

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

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

StompS wrote:
>
> 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.
> --
>
> StompS
> Portland, OR
> http://www.geocities.com/pdxinvestr/Stomps.html

--

Dave Peterson

[StompS]

Thank you very, very much!!!!!


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> =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.
>
> StompS wrote:
>>
>> 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.
>> --
>>
>> StompS
>> Portland, OR
>> http://www.geocities.com/pdxinvestr/Stomps.html
>
> --
>
> Dave Peterson

[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)

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> =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.
>
> StompS wrote:
>>
>> 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.
>> --
>>
>> StompS
>> Portland, OR
>> http://www.geocities.com/pdxinvestr/Stomps.html
>
> --
>
> Dave Peterson

[Dave Peterson]

I think...

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



StompS wrote:
>
> 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)
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > =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.
> >
> > StompS wrote:
> >>
> >> 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.
> >> --
> >>
> >> StompS
> >> Portland, OR
> >> http://www.geocities.com/pdxinvestr/Stomps.html
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[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" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> =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.
>>
>> StompS wrote:
>>>
>>> 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.
>>> --
>>>
>>> StompS
>>> Portland, OR
>>> http://www.geocities.com/pdxinvestr/Stomps.html
>>
>> --
>>
>> Dave Peterson
>
>

[Dave Peterson]

Now I really think it was the final double quote.

StompS wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> >> =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.
> >>
> >> StompS wrote:
> >>>
> >>> 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.
> >>> --
> >>>
> >>> StompS
> >>> Portland, OR
> >>> http://www.geocities.com/pdxinvestr/Stomps.html
> >>
> >> --
> >>
> >> Dave Peterson
> >
> >

--

Dave Peterson