Index and References within a workbook

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

Pete_UK

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
 
P

Pete_UK

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

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

Curtis

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

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

.
 
P

Pete_UK

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.

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 -
.- Hide quoted text -

- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top