Variable 'sheet1' ?

[lostmule]

Hello all,

{=IF(ISERROR(INDEX(Apr!$H$11:Apr!$AM$51,SMALL(IF(Apr!$H$11:$H51=AM$41,ROW(Apr!$H$11:$H51)),ROW(6:6))-10,Apr!$I$8)),"",INDEX(Apr!$H$11:$AM$51,SMALL(IF(Apr!$H$11:$H51=AM$41,ROW(Apr!$H$11:$H51)),ROW(6:6))-10,Apr!$I$8))}

This line goes through a long table and builds a new table which works perfectly. My question:

I am trying to replace all the 'Apr' or if you will, use a variable taken in cell. I have tried many combination but cannot see a solution. Would someone know if there is a way to replace a 'sheet' name with a variable?

Thanks a bunch!

Eric

 

[tusharm]

Check out the INDIRECT function.

 

[lostmule]

I have looked at that function very closely and I am able to bring data from all over the sheets (so much easier than 'hardwired' a cell to another sheet. How would you suggest that I insert 'indirect' in this statement - where I want to replace 'Apr' ?

"INDEX(Apr!$H$11:Apr!$AM$51,SMALL(IF(Apr!$H$11:$H51=AM$41,
ROW(Apr!$H$11:$H51)),ROW(6:6))-10,Apr!$I$8)"

I have use the indirect function in other places with great ease... but replacing the name of a sheet in this statement keeps giving me errors..

Thanks again!

E

 

[Misca]

Try putting the whole range including the sheet name in the INDIRECT function:

Say you have typed your sheet name in A1 so "Apr!$H$11" becomes INDIRECT("'"&A1&"'!$H$11"). I've used the single quotes in the function as well just in case some of your sheet names are not single words. If they are, you can skip the single quotes: INDIRECT(A1&"!$H$11")

 

[tusharm]

What have you tried? And, how did it not work?

I have looked at that function very closely and I am able to bring data from all over the sheets (so much easier than 'hardwired' a cell to another sheet. How would you suggest that I insert 'indirect' in this statement - where I want to replace 'Apr' ?

"INDEX(Apr!$H$11:Apr!$AM$51,SMALL(IF(Apr!$H$11:$H51=AM$41,
ROW(Apr!$H$11:$H51)),ROW(6:6))-10,Apr!$I$8)"

I have use the indirect function in other places with great ease... but replacing the name of a sheet in this statement keeps giving me errors..

Thanks again!

E

 

[lostmule]

Try putting the whole range including the sheet name in the INDIRECT function:

Say you have typed your sheet name in A1 so "Apr!$H$11" becomes INDIRECT("'"&A1&"'!$H$11"). I've used the single quotes in the function as well just in case some of your sheet names are not single words. If they are, you can skip the single quotes: INDIRECT(A1&"!$H$11")

That was the first thing I tried... unfortunately.. nope...

this is what I tried, which seems to be logical:

Original:

=IF(ISERROR(INDEX(Apr!$H$11:Apr!$AM$51,SMALL.......

Change:

=IF(ISERROR(INDEX((INDIRECT(AR43&"!$H$11:"&AR43&"!$AM$51")),
SMALL(IF(Apr!$H$11:$H51=AM$41,ROW(Apr!$H$11:$H51)),ROW(6:6))-10,
Apr!$I$8)),"",INDEX(Apr!$H$11:$AM$51,SMALL(IF(Apr!$H$11:$H51=AM$41,
ROW(Apr!$H$11:$H51)),ROW(6:6))-10,Apr!$I$8))

AR43 = "Apr"

As seen in my line above, I have only tried the first instance where a 'Apr' was called.

E

 

[Misca]

Instead of

INDIRECT(AR43&"!$H$11:"&AR43&"!$AM$51")

try

INDIRECT(AR43&"!$H$11:$AM$51").

That's about the only error I can think of and I'm surprised if your original formula works with the double sheet reference.

 

[tusharm]

How does it not work? What happens when you use the 'Formula | Evaluate Formula' capability of Excel?

That was the first thing I tried... unfortunately.. nope...

this is what I tried, which seems to be logical:

Original:

=IF(ISERROR(INDEX(Apr!$H$11:Apr!$AM$51,SMALL.......

Change:

=IF(ISERROR(INDEX((INDIRECT(AR43&"!$H$11:"&AR43&"!$AM$51")),
SMALL(IF(Apr!$H$11:$H51=AM$41,ROW(Apr!$H$11:$H51)),ROW(6:6))-10,
Apr!$I$8)),"",INDEX(Apr!$H$11:$AM$51,SMALL(IF(Apr!$H$11:$H51=AM$41,
ROW(Apr!$H$11:$H51)),ROW(6:6))-10,Apr!$I$8))

AR43 = "Apr"

As seen in my line above, I have only tried the first instance where a 'Apr' was called.

E

 

[lostmule]

How does it not work? What happens when you use the 'Formula | Evaluate Formula' capability of Excel?

Misca and tusharm: Indeed... it does work. I can do complex task with Excel and I was not able to have this very simple function to run. Why? Meh: I had one extra parenthesis (After a night sleep, I was able to re-read this thread and see the typos I had in my code.. phew..). Lastly, because if my 'if(iserror...' and the fact that I was only replacing the first part with 'indirect', the cell was becoming 'blank' which was suppose too when there was a prob... removing that condition enable me to 'see the light'.

Nope
=IF(ISERROR(INDEX((INDIRECT(AR43&"!$H$11:"&AR43&!$AM$51")), and so on...

Yup
=IF(ISERROR(INDEX(INDIRECT(AR43&"!$H$11:"&AR43&!$AM$51"),and so on...

Thanks for your time guys, it did help a lot!

Eric