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?


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=378266

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378266

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?


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=378266

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378266

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 :confused:


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=378266

enter this formula in your scores sheet:

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

and check if its working.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378266

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
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=378266

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,editfind janreplace junreplace 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

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
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\Caribbe an\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


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=378266

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
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378266

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378266