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

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

Similar Threads

Alternative to multiple IF limits 5
Index Match Help 6
Calculation Speed (Index/Match vs Vlookup)? 6
IF INDEX and MATCH function together 7
Index Function Problem 7
INDEX MATCH SMALL 12
Is it possible to pick up formatting from range using index match? 4
Index and Match - Errors 6

Top