use info in 2 cells to get a value from another sheet

[Rob69mfj]

I have a 2 cells where the value may be changed by the user but for the sake of this question I will say that cell 1 value is "JAN" and cell 2 is "14".

In another cell, say cell 3, I want to get a value from (H35) on sheet "JAN14". I know the name can be created with a concatenate statement but I cant use that as the sheet name.

ex.
='JAN14'!H35

I need the JAN14 to be able to change when the cells 1 and 2 are changed.

any ideas?

[daffodil11]

Just some more fancy concatenation. Instead of using the function CONCATENATE, you can instead use & which means the same thing.

=A1&B1&"!H35"

Now we nest it within INDIRECT, which evaluates a string as a reference.

=INDIRECT(A1&B1&"!H35")



In much the same way INDIRECT("A" & 100 - 99) is the same as =A1

[ranman256]

You CAN name a sheet JAN14, but normally you would not change the name of your sheet, it may disturb all formulas prointing to the previous name.

[Rob69mfj]

Thanks daffodil11, that was exactly what I was needing. Ranman256, I was not looking to change the tab name, I was wanting the cell value to report information from a cell on the sheet by that tab name. Thank you anyway for your input as it is accurate information, just not what I was looking for