shortcut for same directory

[Tallmankg in Al]

I am trying to use a formula to copy info from three sheets into one sheet on
several different days. I will use this formula on different dates so I am
looking for a shortcut to work in the date I input. hers the formula I have
and I have to change it for each different day. Thanks
=MAXA('C:\EXCEL\Tusc Files\Fuel sheets\Feb
10\Blank\[tsa.xls]Sheet1'!D13:H13,'C:\EXCEL\Tusc Files\Fuel sheets\Feb
10\2-5-2010\[FFa.xls]Sheet1'!D13:H13,'C:\EXCEL\Tusc Files\Fuel sheets\Feb
10\Blank\[GSa.xls]Daily fuel'!D13:H13)

Ken Gray

 

[ker_01]

Ken- if you haven't used it before, the INDIRECT function might be what you
are looking for.

Let's assume that you are putting your formula in column A in your worksheet.

Put the filepath (at least as much as will be consistent) in column B, and
your date in Column C:
(untested, but should work)

=MAXA
(indirect(B1 & "\" & C1 & "\Blank\[tsa.xls]Sheet1'!D13:H13),

indirect(B1 & "\" & C1 & "\[FFa.xls]Sheet1'!D13:H13),

indirect(B1 & "\" & C1 & "\Blank\[GSa.xls]Daily fuel'!D13:H13))

Then you just have to change the values in B and C if your path or date
changes.

if your "Blank" refers to a directory called blank, leave that as is,
otherwise just delete "\Blank" from the first and third component of your
formula to reflect a shorter path.

HTH,
Keith