Storing Formulas to Use

[NEWB]

Is there a way to store formulas in a worksheet, for instance----
=IF(ISNA(VLOOKUP(A4,'2005 information'!$A$4:$L$97,12,FALSE)),"No Client Info
For This Year",VLOOKUP(A4,'2005 information'!$A$4:$L$97,12,FALSE)) ----- So
that it will automaticall pull if a new worksheet is created. Basically I
want to make a Comparison worksheet, and when a new year comes like 2006, I
want ot be able to pull in my 2006 worksheet, and have the formulas update
automatically to 2006, so that I don't have to change them to 2006. Is this
possible to do????

 

[vezerid]

You need the INDIRECT() function, which creates dynamic addresses.
Assuming that you have your year number in a separate cell (Say K1),
and that all your worksheets have the same format, i.e. "YEARNO(space)
information", you will need the following for your VLOOKUP():
VLOOKUP(A4, INDIRECT("'" & K1 & " information'!$A$4:$L$97"), 12, FALSE)

HTH
Kostis Vezerides

 

[NEWB]

what do you mean by having the year number in a seperate cell, because I did
that, and my vlookup does not update if i put 2004 in it stays as the 2005
numbers.

 

[vezerid]

Newb,
first of all the full formula, in case you forgot something:

=IF(ISNA(VLOOKUP(A4, INDIRECT("'" & K1 & " information'!$A$4:$L$97"),
12, FALSE)),"No Client Info For This Year",VLOOKUP(A4, INDIRECT("'" &
K1 & " information'!$A$4:$L$97"), 12, FALSE))

Second, I just tested a variant, just in case and it works. Please
check Tools|Options the Calculation tab, to see if somehow calculation
was set to Manual. If so, you would get the new values by pressing the
function key F9.

Finally, replace K1 in BOTH places in the formula with whatever cell
you chose in your Comparison sheet.

Write back if neither of these works.

Kostis Vezerides