Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell references change when entering new data | New Users to Excel | |||
I Need to change reference sheet for all cells on a form | Excel Discussion (Misc queries) | |||
Copy text from same cell on every sheet to title sheet? | Excel Discussion (Misc queries) | |||
how do i copy formula and change worksheet instead of cell | Excel Worksheet Functions | |||
Link - deactivate being sent to source when click on linked cell? | Excel Worksheet Functions |