Indirect worksheet function

[Howard Kaikow]

The following is a hardcoded formula in a worksheet, call it the
CurrentSheet..

='210208345'!FSPTX/'210208345'!CurrentValue

Where '210208345' is the name of a worksheet, and FSPTX and CurrentValue
are names defined in thgat worksheet.

I can get the same reult using

=INDIRECT("'210208345'!FSPTX")/INDIRECT("'210208345'!CurrentValue")

But I'd rather not have to hardcode the 210208345, and FSPTX, as they are
values in cells in the CurrentSheet.
Is the following "best'?

=INDIRECT("'"&D$2&"'!"&B3)/INDIRECT("'"&D$2&"'!CurrentValue")

So B3 would change to B4 in the cell below.

 

[Bob Phillips]

Well it is dynamic, and it does what you want, so I guess it is 'best'. You
could use names to hold the values, but is that better, probably not.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Domenic]

Try...

=INDIRECT("'"&D$2&"'!"&CELL("address",B3))/INDIRECT("'"&D$2&"'!CurrentVal
ue")

Hope this helps!

 

[Howard Kaikow]

Try...

=INDIRECT("'"&D$2&"'!"&CELL("address",B3))/INDIRECT("'"&D$2&"'!CurrentVal
ue")

How does using CELL("address",B3) differ from using just B3?

 

[JE McGimpsey]

How does using CELL("address",B3) differ from using just B3?

For one thing, it could change the cell reference if your inserted or
deleted a row or column in the sheet which contains the formula rather
than the source sheet.

That would be either a good or a bad idea, depending on what you're
trying to achieve.

 

[Bob Phillips]

Let's say that D2 holds sheet_name, and cell B3 contains A1.

The formula =INDIRECT("'"&D$2&"'!"&CELL("address",B3)) returns A1, that is
the contents of B3.

The formula =INDIRECT("'"&D$2&"'!"&B3) retuns whatever is in A1, that is the
cell pointed to by B3.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Domenic]

How does using CELL("address",B3) differ from using just B3?

Sorry, I misunderstood. Disregard my post. CELL("Address",B3) returns
the address for the B3 in the form of a text value --> $B$3. INDIRECT
then uses it, together with the text string in D2, to return a reference.

 

[Howard Kaikow]

Sorry, I misunderstood. Disregard my post. CELL("Address",B3) returns
the address for the B3 in the form of a text value --> $B$3. INDIRECT
then uses it, together with the text string in D2, to return a reference.

Lemme clarify.

THe values in cells C2, D2, etc. are the hard coded names of worksheets,
each representing a different investment portfolio.

The values in B3, B4, etc. are the hard coded stock symbols.
The formula calculates the % in each portfolio for each stock.

So =INDIRECT("'"&C$2&"'!"&B5)/INDIRECT("'"&C$2&"'!CurrentValue")

returns the percent of the portfolio on sheet with name in C2 for the stock
with name in B5.