Change sheet name on linked cell by dropdown box

[Mikeice]

Hi There

What I need is the following:
This is the Formula

=IF('[Quality Scorecard Test.xls]Jan'!B2="","",'[Quality Scorecard
Test.xls]Jan'!B2)

Now Jan is the worksheet name that I need to be chnaged depending on
what mth is slected in cell A1.

Eg.

It cell A1 is Mar then I want the above formula to say Mar

Is that possible?

 

[mangesh_yadav]

Hi Mike,

some things are unclear here.
=IF('Jan'!B2="","",'Jan'!B2)

What does the cell B2 contain in this sheet. Where does cell A1
figure.In which cell you want the "Mar" to appear.

Mangesh

 

[Mikeice]

Hi Mangesh

I am having trouble again.

In this workbook I have a sheet called Scorecard.

The formula above is listed in multiple cells referencing the sheet
that you have.

So in A2 I want the person to type in a mth and this will change all
formuals in this worksheet to reflect the month that has been entered
in A2.

eg.

About 40 formulas are as below but with different cell references

=IF('[Quality Scorecard Test.xls]Jan'!B2="","",'[Quality Scorecard
Test.xls]Jan'!B2)

so I typein A2 Jun

This then changes all formulas with Jun

=IF('[Quality Scorecard Test.xls]Jun'!B2="","",'[Quality Scorecard
Test.xls]Jun'!B2)

Do you think perhaps I should do a Command button instead changing the
above?

 

[mangesh_yadav]

Hi Mike,

do the following:

=INDIRECT(A1&"!B2")

Here I enter Jan in cell A1 in the scores sheet. So the function will
look at cell A1 and read the month from this cell and pick up the value
from cell B2 in the concerned sheet.

Your case would be:

=IF(INDIRECT(A1&"!B2")="","",INDIRECT(A1&"!B2"))


Mangesh

 

[Mikeice]

Hi Again

Jan is the name of the worksheet I want to link too.
How can I get that worksheet name to change in every formula?

I don't understand the use of the indirect

 

[mangesh_yadav]

enter this formula in your scores sheet:

=IF(INDIRECT(A1&"!B2")="","",INDIRECT(A1&"!B2"))

and check if its working.

Mangesh

 

[Mikeice]

Hi

Just don't get the solution:

I have done what you suggetsed but how will that change a 100 odd
formulas ?

just so that we are on the same page.

=IF('[Quality Scorecard Test.xls]Jan'!B2="","",'[Quality Scorecard
Test.xls]Jan'!B2)
=IF('[Quality Scorecard Test.xls]Jan'!B3="","",'[Quality Scorecard
Test.xls]Jan'!B3)
=IF('[Quality Scorecard Test.xls]Jan'!B4="","",'[Quality Scorecard
Test.xls]Jan'!B4)
=IF('[Quality Scorecard Test.xls]Jan'!B5="","",'[Quality Scorecard
Test.xls]Jan'!B5)
=IF('[Quality Scorecard Test.xls]Jan'!B6="","",'[Quality Scorecard
Test.xls]Jan'!B6)
=IF('[Quality Scorecard Test.xls]Jan'!B7="","",'[Quality Scorecard
Test.xls]Jan'!B7)

This is just a small snapshot of over 100 formulas in varios cells.

I need the name of the link worksheet to chnage from what it currently
is to whatever is selected in b2 (That is B2 on the current worksheet
not the one above. The formalua above contains the information needed
for this worksheet.

Am I just not getting it or are we on a different page?

 

[Mikeice]

Thx Mangesh

I just can't get it to work

Thx for trying

Mike

 

[Guest]

the best i can do is this,edit>find jan>replace jun>replace all

 

[mangesh_yadav]

Hi Mike,

sorry had gone out. You need to put that formula in all your instances
Its a one time job you have to do. For instance, just replace on
formula and see if you are getting your expected result.

Lets say this is one of your formulae.
=IF('[Quality Scorecard Test.xls]Jan'!B2="","",'[Quality Scorecar
Test.xls]Jan'!B2)
If you are working in one workbook, you need not use the file name,
=IF('Jan'!B2="","",'Jan'!B2)
This will work as fine.

Now you want to change the "Jan" in the above formula to be whateve
month you select in A1. Note that A1 shold be text "Jan" or "Feb"
While entering a month in A1, enter "Jan" (without quotes) and not
date.

Substitute the above formula by the one I gave, and with Jan in A1 i
the score sheet, it should give the same result as your formula i
currently giving.

=IF(INDIRECT(A1&"!B2")="","",INDIRECT(A1&"!B2"))


Manges

 

[Mikeice]

Thx

Got that to work but some of the formulae are in a different workbook
on a network drive and I need to access those cells.

WOrkbook location:

C:\fsmel01\fireworks\Business\Tech\Quality\Caribbean\Mike\Scorecard
Summary

Worksheet Jan Cell b6

SO same as above but this link is in a differnet workbook.

Indirect fails in this instance.

How would I approach this one

 

[mangesh_yadav]

Hi Mike,

use something like:

=IF(INDIRECT("[Book1.xls]"&A1&"!B2")="","",INDIRECT("[Book1.xls]"&A1&"!B2"))


But I think that both files need to be open. Also put the path in the
filename

Mangesh

 

[mangesh_yadav]

Hi Mike,

This worked for me:

=IF(INDIRECT("'C:\[Book1.xls]"&A1&"'!B2")="","",INDIRECT("'C:\[Book1.xls]"&A1&"'!B2"))


But it needs both the workbooks open

Manges