Storing Information By Date

[CrazyFileMaker]

Hello,
I've created a workbook that I use daily. The workbook has two sheets. One for entering information. The other for storing information.

On the first sheet, I enter health information about myself. I also enter the date.

On the second sheet, I have rows dated from January 1, 2009 to December 31, 2009. If the date entered on the first sheet matches January 1, 2009, then the information is copied to that respective row. If the date entered on the first sheet matches January 2, 2009, then the information is copied to the corresponding January 2, 2009 row, etc, etc.

So far so good. I enter the information, the date, and instantly the information is copied over. I save the workbook and go to sleep happy.

The next day I enter in my new information (replacing yesterday's information) and enter in the new date (replacing yesterday's date). As soon as I enter this new date, the new information is instantly copied to the new corresponding row on sheet 2. Perfect, right? ---- But not exactly... the information I stored the previous day disappears!

I want to store information everyday for the next year. Right now I'm only storing the current day's information and nothing else! When the date on sheet 1 changes, my "IF-THEN" formula for the day before no longer holds true and those values are lost.

I've read about making a form using VBA script. But I can't use VBA, because I'm on the mac version 2008. I also can't use apple script because I'm sharing this workbook with a windows user.

Is there anyway to make a form without script? Is there anyway to accomplish my goal without using a form?

Thanks for your help in advance!! I am desperate for a solution!

[JBeaucaire]

The whole point of using FORMULAS for this kind of work is that it IS dynamic, it changes/updates in realtime, as you've discovered.

The only way to keep this historical date that is occuring via formulae is to copy the data and do an EDIT > PASTE SPECIAL > Values somewhere, but I'm not sure what that would get you.

This stuff is made for VBA, which would not suffer the issues you're experiencing most likely.

[DonkeyOte]

As you say given you're running 2008 on the Mac you're pretty restricted given no VBA functionality... there used to be an old template wizard with data tracking (XL2002) but I don't think that's available to you either...

You could possibly do this with formulae using Iteration but it's not generally advised... see attached, might help ?

Failing that then as JB states it's a case of manual updates I'm afraid.

[CrazyFileMaker]

Problem solved... I got a VBA capable version of excel!

You two had a great influence on the decision. Thanks for that. Fortunately, since I've done some OOP in the past, VBA has been relatively simple to pick up. Makes SO many more things possible. And I'm really enjoying the language too. Thanks again for the sound advice!