|
Index and References within a workbook
I am using the following formula below that works fine however I would like
to modify it such that rather than tell it to go to worksheet 1401 (INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135) I would like it to say look for the any sheet in the workbook using a reference say B1. Note: in B1: b12 say I would list all the tabs in the workbook.. Is this possible thanks =IF(OR(F$1=1,INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-1400-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))=0,0),INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-1400-WB-Actual.xlsx]1401'!$D$9:$Q$9,0)),INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-1400-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))-INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1-1,'[0910-1400-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))) |
Index and References within a workbook
Wherever you have this in your formula:
'[0910-1400-WB-Actual.xlsx]1401'! you would need to change it to: INDIRECT("'[0910-1400-WB-Actual.xlsx]"&B1&"'! and at the end of each address range you will need to add: ") So, for example, the following: INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH...etc will become: INDEX(INDIRECT("'[0910-1400-WB-Actual.xlsx]"&B1&"'!$D$11:$Q $135"),MATCH...etc and then you can just put a sheet name in B1 and the formula will look at that sheet. If the formula is on row 1 then you can copy it down to row 12 to pick up each sheet name on each row. Hope this helps. Pete On Nov 17, 7:40*pm, Curtis wrote: I am using the following formula below that works fine however I would like to modify it such that rather than tell it to go to worksheet 1401 (INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135) I would like it to say look for the any sheet in the workbook using a reference say B1. Note: in B1: b12 say I would list all the tabs in the workbook.. Is this possible thanks =IF(OR(F$1=1,INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH("TOT*AL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140*0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))=0,0),INDEX('[0910-1400-WB-Actual.xlsx]*1401'!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140*0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0)),INDEX('[0910-1400-WB-Actual.xlsx]1401'*!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140*0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))-INDEX('[0910-1400-WB-Actual.xlsx]1401'*!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1-1,'[0910-1*400-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))) |
Index and References within a workbook
I should have pointed out that INDIRECT will only work with open
workbooks (which your formula implies that you have, as you do not show the full path), but if the file 0910-1400-WB-Actual.xlsx is closed then you will get errors. Pete On Nov 17, 8:17*pm, Pete_UK wrote: Wherever you have this in your formula: '[0910-1400-WB-Actual.xlsx]1401'! you would need to change it to: INDIRECT("'[0910-1400-WB-Actual.xlsx]"&B1&"'! and at the end of each address range you will need to add: ") So, for example, the following: INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH...etc will become: INDEX(INDIRECT("'[0910-1400-WB-Actual.xlsx]"&B1&"'!$D$11:$Q $135"),MATCH...etc and then you can just put a sheet name in B1 and the formula will look at that sheet. If the formula is on row 1 then you can copy it down to row 12 to pick up each sheet name on each row. Hope this helps. Pete On Nov 17, 7:40*pm, Curtis wrote: I am using the following formula below that works fine however I would like to modify it such that rather than tell it to go to worksheet 1401 (INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135) I would like it to say look for the any sheet in the workbook using a reference say B1. Note: in B1: b12 say I would list all the tabs in the workbook.. Is this possible thanks =IF(OR(F$1=1,INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH("TOT**AL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140**0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))=0,0),INDEX('[0910-1400-WB-Actual.xlsx*]*1401'!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140**0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0)),INDEX('[0910-1400-WB-Actual.xlsx]1401*'*!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140**0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))-INDEX('[0910-1400-WB-Actual.xlsx]1401*'*!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1-1,'[0910-1**400-WB-Actual.xlsx]1401'!$D$9:$Q$9,0)))- Hide quoted text - - Show quoted text - |
Index and References within a workbook
Trying to avoid indirect as opening the source files is not feasible.
Thanks "Pete_UK" wrote: I should have pointed out that INDIRECT will only work with open workbooks (which your formula implies that you have, as you do not show the full path), but if the file 0910-1400-WB-Actual.xlsx is closed then you will get errors. Pete On Nov 17, 8:17 pm, Pete_UK wrote: Wherever you have this in your formula: '[0910-1400-WB-Actual.xlsx]1401'! you would need to change it to: INDIRECT("'[0910-1400-WB-Actual.xlsx]"&B1&"'! and at the end of each address range you will need to add: ") So, for example, the following: INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH...etc will become: INDEX(INDIRECT("'[0910-1400-WB-Actual.xlsx]"&B1&"'!$D$11:$Q $135"),MATCH...etc and then you can just put a sheet name in B1 and the formula will look at that sheet. If the formula is on row 1 then you can copy it down to row 12 to pick up each sheet name on each row. Hope this helps. Pete On Nov 17, 7:40 pm, Curtis wrote: I am using the following formula below that works fine however I would like to modify it such that rather than tell it to go to worksheet 1401 (INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135) I would like it to say look for the any sheet in the workbook using a reference say B1. Note: in B1: b12 say I would list all the tabs in the workbook.. Is this possible thanks =IF(OR(F$1=1,INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH("TOTÂ*Â*AL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140Â*Â*0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))=0,0),INDEX('[0910-1400-WB-Actual.xlsxÂ*]Â*1401'!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140Â*Â*0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0)),INDEX('[0910-1400-WB-Actual.xlsx]1401Â*'Â*!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140Â*Â*0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))-INDEX('[0910-1400-WB-Actual.xlsx]1401Â*'Â*!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1-1,'[0910-1Â*Â*400-WB-Actual.xlsx]1401'!$D$9:$Q$9,0)))- Hide quoted text - - Show quoted text - . |
Index and References within a workbook
Well, if the files are closed then your formula will need to show the
full path in addition to the filenames. Pete On Nov 17, 9:05*pm, Curtis wrote: Trying to avoid indirect as opening the source files is not feasible. Thanks "Pete_UK" wrote: I should have pointed out that INDIRECT will only work with open workbooks (which your formula implies that you have, as you do not show the full path), but if the file 0910-1400-WB-Actual.xlsx is closed then you will get errors. Pete On Nov 17, 8:17 pm, Pete_UK wrote: Wherever you have this in your formula: '[0910-1400-WB-Actual.xlsx]1401'! you would need to change it to: INDIRECT("'[0910-1400-WB-Actual.xlsx]"&B1&"'! and at the end of each address range you will need to add: ") So, for example, the following: INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH...etc will become: INDEX(INDIRECT("'[0910-1400-WB-Actual.xlsx]"&B1&"'!$D$11:$Q $135"),MATCH...etc and then you can just put a sheet name in B1 and the formula will look at that sheet. If the formula is on row 1 then you can copy it down to row 12 to pick up each sheet name on each row. Hope this helps. Pete On Nov 17, 7:40 pm, Curtis wrote: I am using the following formula below that works fine however I would like to modify it such that rather than tell it to go to worksheet 1401 (INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135) I would like it to say look for the any sheet in the workbook using a reference say B1. Note: in B1: b12 say I would list all the tabs in the workbook.. Is this possible thanks =IF(OR(F$1=1,INDEX('[0910-1400-WB-Actual.xlsx]1401'!$D$11:$Q$135,MATCH("TOT***AL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140***0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))=0,0),INDEX('[0910-1400-WB-Actual.xls*x*]*1401'!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140***0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0)),INDEX('[0910-1400-WB-Actual.xlsx]140*1*'*!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1,'[0910-140***0-WB-Actual.xlsx]1401'!$D$9:$Q$9,0))-INDEX('[0910-1400-WB-Actual.xlsx]140*1*'*!$D$11:$Q$135,MATCH("TOTAL FUEL",'[0910-1400-WB-Actual.xlsx]1401'!$C$11:$C$135,0),MATCH(F$1-1,'[0910-1***400-WB-Actual.xlsx]1401'!$D$9:$Q$9,0)))- Hide quoted text - - Show quoted text - .- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 11:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com