Lookup Question

[JimS]

I have the following formula in Sheet2

=IF(C157="","",HLOOKUP(C157,'Sheet1'!$X$2:$BA$3,2,0))

This grabs a number from a table in Sheet1.

It works great and does exactly what I want except for one thing.
Tomorrow, when I put more data into the table it changes all of the
old numbers in Sheet2. I want the old numbers to stay the way they
were, (so I have separate data for each day) and only have new data
affected.

Is this possible?

 

[L. Howard Kittle]

It would seem to me that is not possible. If you change the data in sheet 1
and the formula on sheet 2 still refers to that table, the formula will just
do its job and return the new data.

I may not completely understand the problem.

HTH
Regards,
Howard

 

[JimS]

No, you've got it right. My workaround solution is that once the data
is inputed to sheet2 I just overwrite it by hand and eliminate the
forumulas for the old data. New data will still be updated from the
table and I'll just overwrite that tomorrow.

Thanks

 

[ebloch]

free ASAP Utilities has a function to replace formulas with their values.

a simpler solution would be to use Paste-Special Values Only from the
formula cells into other cells

you could write a script/macro to do this job and add a button to run it.

 

[JimS]

Thanks for the suggestions. I'll use the ASAP idea for now.