Variable in VLOOKUP and referencing a cell another file in excel

[Guest]

I have a small account file running over a couple of years. I want to use a
variable in the reference to the previous years figures:

ie. ='C:\Treasurer\Data\SCLROC - 2005-06 - Accounts - 1.xls'!D70

I want to use a variable for the year (2005-06) so I can input it once and
all refernces will change each year.

Can anyone help me please.

I also have my sport members on file and I need to refernce teh file using
VLOOKUP.

I need to change the month and year value, as in,
VLOOKUP(A3,'Availability 11.05'!$A$1:$U$120,8,FALSE)

Where 'Availability 11.05' is a workbook in the same file.

I would like to be able to put 11.05 (or the full workbook name) in a cell
and all the formulas use this in their reference. I have over 80 members and
chaning each one (even with copy and past) is time consuming.

Thanks for all your help.

DaveT

 

[Franz Verga]

Dave T at home wrote:
[cut]

Hi Dave,

I also have my sport members on file and I need to refernce teh file
using VLOOKUP.

I need to change the month and year value, as in,
VLOOKUP(A3,'Availability 11.05'!$A$1:$U$120,8,FALSE)

Where 'Availability 11.05' is a workbook in the same file.

I would like to be able to put 11.05 (or the full workbook name) in a
cell and all the formulas use this in their reference. I have over
80 members and chaning each one (even with copy and past) is time
consuming.

If (if) 'Availability 11.05' it's a *worksheet* in the same *workbook* or
file in which you have the VLOOKUP, you can use the INDIRECT function:

=VLOOKUP(A3,INDIRECT("'Availability "&Z1&"'!$A$1:$U$120"),8,FALSE)

Where Z1 is the cell in which you have 11.05



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy