indirect addressing to a sheet

[costadina]

Hi all

what I want to do is copy data to my working sheet (say sheet-1) from other worksheetx (say sheet-2, sheet-3). That's easy enough, but I want to be able to indirectly address "sheet-2" or "sheet-3" from a cell in sheet-1.

Look at the attachment. The data under cost A, cost B, cost C is from other sheets in the same workbook. I want to able to type in "sheet-2" in the first column and Excel to automatically copy over the data in columns 2,3,4.


I do not want a VBA solution. I know this can be done with built-in Excel functions because I did it before. Unfortunately, I lost that spreadsheet and I can't recall how it was done. I tried using Indirect function, but it returns a ref# error. I'm really stuck.

[shg]

Welcome to the forum.

If you put the formula

=INDIRECT($A2 & "!" & CELL("address", B2))

in B2, it will copy data from the corresponding cell in the sheet specified in A2 -- but the sheet name has to be exactly correct (Sheet2, not Sheet-2)

[costadina]

I can't believe this was solved so quickly!

Thank you soooo much. You made my day

[shg]

Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

If more than two days have elapsed since your first post, ask a moderator to mark it.

[IronDogg]

Welcome to the forum.

If you put the formula

=INDIRECT($A2 & "!" & CELL("address", B2))

in B2, it will copy data from the corresponding cell in the sheet specified in A2 -- but the sheet name has to be exactly correct (Sheet2, not Sheet-2)

Excellent solution! I was using this solution for a number of similar items and came across a sticking point. How do you adjust this formula to account for a sheet name that may have an apostrophe in it.

For example if the sheet name in A2 was "Cost's". Typically, I think you would surround the 's in an extra apostrophe's like this: ='Cost''s'!N2 (for example), but how do I get around this using the formula you have provided above?

[shg]

The easy solution is don't use apostrophes in sheet names. But to accommodate spaces, the formula should have been,

=INDIRECT("'" & $A2 & "'!" & CELL("address", B2))

Please start your own thread if you have further questions.