indirect function not giving intended results

[kubo3]

Hey guys,

I'm stuck on this formula I'm trying to do.

I'm using something like this:

INDIRECT("'"&A1&"'!"&B1)

I put name of the sheet from where I want to pull information in A1 in sheet where is the formula(lets say sheet1), lets say A1=sheet2. All good so far. I want to put cell reference in B1 in the sheet1 (lets say B1=D3).

My problem is that this formula takes value from Sheet2 and B1 in sheet2. I want this formula to take values from sheet2 ( as per value in A1) and whatever is in sheet1B1 (D3 in this case).


So right now I end up with Sheet2B1 but i would like to end up with Sheet2D3.


I hope this is not too confusing but I cant figure this out and I would really appreciate any help.

Thank you in advance.

Jakub

[6StringJazzer]

Welcome to the Forum kubo3!

The formula you showed should do what you said you want it to do. If it is not doing that, I am guessing that what you did in your actual Excel file is not what you think you did.

Can you attach your file here? The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[kubo3]

Thank you very much for quick reply. Actual file is at my work. I will double check what I did and post the file later if Im still stuck
.

Thanks again

Welcome to the Forum kubo3!

The formula you showed should do what you said you want it to do. If it is not doing that, I am guessing that what you did in your actual Excel file is not what you think you did.

Can you attach your file here? The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[Pete_UK]

In cell B1 you don't need an equals sign - just type the text D3, then you should get the correct result.

Hope this helps.

Pete

[kubo3]

Thank you very much for the replies, I'm still stuck on the same, I have uploaded the file if you could have a look.

Thanks again.

[Pete_UK]

Change the formula in B1 to this:

=INDIRECT("'"&A9&"'!"&B9)

i.e. remove the quotes from around the B9. The way you had it, the formula was taking B9 as text, so it was that cell that it was trying to get the data from.

Hope this helps.

Pete

[kubo3]

That has fixed it, thank you very much!

[Pete_UK]

Glad to hear it.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete