Change sheet name on linked cell by dropdown box

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?

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

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?

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

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

enter this formula in your scores sheet:

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

and check if its working.

Mangesh

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?

Thx Mangesh

I just can't get it to work

Thx for trying

Mike

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 one 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 Scorecard 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 whatever 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 a date.

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

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


Mangesh

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

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

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

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

Mangesh

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>

the best i can do is this,edit>find jan>replace jun>replace all
--
paul
remove nospam for email addy!



"Mikeice" wrote:

>
> Thx Mangesh
>
> I just can't get it to work
>
> Thx for trying
>
> Mike
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=378266
>
>