Indirect function

[meglan]

Hi my friends!

I am in need of some help with the INDIRECT function (I think). I have a cell (B2)with a drop down that contains the names of 10 sheets I have in the workbook (i.e. Women, Men, Children). Each sheet contains the same data within the same cell on each page (i.e. revenue in cell E6, etc).

What I am trying to do is create a table that will change the data within in based upon the sheet selected in cell B2. I am using the formula below with my drop down in B2 and the value I am trying to capture on the corresponding sheet in cell E6. It seems that no matter what I try, I keep getting and #REF error. I used the formula below.

Does anyone have ANY idea what is wrong with this?? Thank you!!!!!

=INDIRECT("'"&B2&"'!"&E6)

 

[Scott T]

This should work

=INDIRECT(B2&"!e6")

 

[meglan]

This should work

=INDIRECT(B2&"!e6")

Thank you! Thank you! Thank you! I have no idea why I couldn't get that to work before, but copying your formula in to my cell worked!

 

[Eric W]

If any of the sheets have a space in the name:

=INDIRECT("'"&B2&"'!E6")

 

[meglan]

I think that was my original problem; several sheets had spaces. I finally figured that was why some were working and others were not. Thank you!

 

[Scott T]

The indirect function turns text into a reference. If you are having problems then just do what you want without the indirect. For example enter = then go to your other sheet and select the cell. You will then know the reference you want to build inside the indirect function for example

='this hasspace'!A7

What you put inside the indirect function needs to create that.