Change Sheet Name in a formula

[ravibhatu]

Hello,

If I have the following formula, how would I be able to change the sheet name to something that is in cell M2. M2 will have either Week 1, Week2, Week 3, Week 4 or Week5

=VLOOKUP(A4,'Week 1'!B:L,5,0)

Any help would be highly appreciated.

Thank you in advance

Ravi

 

[T. Valko]

Hello,

If I have the following formula, how would I be able to change the sheet name to something that is in cell M2. M2 will have either Week 1, Week2, Week 3, Week 4 or Week5

=VLOOKUP(A4,'Week 1'!B:L,5,0)

Any help would be highly appreciated.

Thank you in advance

Ravi

Try it like this...

=VLOOKUP(A4,INDIRECT("'"&M2&"'!B:L"),5,0)

 

[Peter_SSs]

One further suggestion I would make is to not have 11 columns (B:L) in your lookup range when you are only using 5 as this can lead to the sheet re-calculating more often than is necessary. So..

=VLOOKUP(A4,INDIRECT("'"&M2&"'!B:F"),5,0)

 

[ravibhatu]

Thanks for your help Biff & Peter. The formula works properly.

Now I am getting greedy

if i have this formula, how to do i get INDIRECT for work for me. I want to change the sheet name (Week 1) to whatever is in M2

='Week 1'!$E$13

I tired using the formula you gave before, meaning replacing 'Week 1'! with INDIRECT("'"&$M$2&"'!$E$13") but it didnt work.

Thanks in advance once again.

Regards

Ravi

 

[T. Valko]

Thanks for your help Biff & Peter. The formula works properly.

Now I am getting greedy

if i have this formula, how to do i get INDIRECT for work for me. I want to change the sheet name (Week 1) to whatever is in M2

='Week 1'!$E$13

I tired using the formula you gave before, meaning replacing 'Week 1'! with INDIRECT("'"&$M$2&"'!$E$13") but it didnt work.

Thanks in advance once again.

Regards

Ravi

That should work. It works for me.

If M2 = Week 1 then the formula evaluates to:

='Week 1'!$E$13

If M2 = Week 2 then the formula evaluates to:

='Week 2'!$E$13

Just a minor tweak:

=INDIRECT("'"&$M$2&"'!E13")

When you quote the cell reference then there's no need to use the $ dollar signs for absolute referencing. The quoted string will automatically be evaluated as an absolute reference.

 

[Peter_SSs]

Now I am getting greedy

I don't know how many INDIRECT formulas you intend to use but just a word of warning before you get too excited about INDIRECT and start using it everywhere. INDIRECT is a volatile function so it can slow your sheet performance if used a lot in a sheet.

 

[ravibhatu]

Thanks Biff, it works.

Peter, I will keep your suggestion in mind.

Thanks both

 

[T. Valko]

Thanks Biff, it works.

Peter, I will keep your suggestion in mind.

Thanks both

You're welcome. Thanks for the feedback!