Index and References within a workbook

  • Thread starter Thread starter Curtis
  • Start date Start date
C

Curtis

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

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

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.
Click to expand...
Note: in B1: b12 say I would list all the tabs in the workbook.. Is this
possible
thanks
Click to expand...

=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 -
Click to expand...

- Show quoted text -
Click to expand...
 
Trying to avoid indirect as opening the source files is not feasible.

Thanks

Pete_UK said:
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

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

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.
Click to expand...
Note: in B1: b12 say I would list all the tabs in the workbook.. Is this
possible
thanks
Click to expand...

=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 -
Click to expand...

- Show quoted text -
Click to expand...

.
Click to expand...
 
Well, if the files are closed then your formula will need to show the
full path in addition to the filenames.

Pete

Trying to avoid indirect as opening the source files is not feasible.

Thanks



Pete_UK said:
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
Click to expand...

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
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.. Isthis
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 -
Click to expand...
Click to expand...
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top