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


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