Problem with formula depending on date.

[Temp]

Soory, if this problem is already resolved.
I am trying to make a list of consumed goods (fuel, water, etc...) in
one month extracting data from DAILY report. It work, but only for one
(this very) day. When make report for another day, the OLD data on the
from cells on "monthly" list go to "zero value". Need to KEEP this
data, or, beter say, I dont need any formula in cells from yesterday,
enough is ONLY value. If I am not clear, please ask me to clarifie.
Thank you.

Attachment filename: formula.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=397927

 

[gmangad]

Your question is not clear. you may attach a sample file (excel not
jpg) with few sample data to have a look.

regards
gt

 

[Temp]

Sorry, I should think about this before, here is one example in Excel.
Thank you for interest.

Attachment filename: testing 02 january 04.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=399187

 

[Temp]

More clear.... formula should be valid only for this day, and next day,
data (value) should remain as only number....
Thank

 

[Max]

Think you will need a macro to transfer the values
from sheet: Daily report to sheet: Monthly

A formula can only return values to the cell it is in.
It cannot by itself freeze values returned by it,
after calculations are completed.

Try the sub TransferValues() below
in your sample workbook

(Delete all the formulas you have in sheet: Monthly first)

Steps
------
1. Press Alt + F11 to go to VBE
2. Click Insert > Module
3. Copy > Paste the sub below (everything between the dotted lines)
into the white empty space in the right-side window

-------begin vba------
Sub TransferValues()
' This sub will transfer the values in F21, F17 & F19 in sheet: Daily report
' to the row in sheet: Monthly with the date in col A which matches the
' the date entered in D6 of sheet: Daily report
' (Max .misc 14 Jan 2004)

n = Application.WorksheetFunction.Match(Sheets("Daily report").Range("D6"),
_
Sheets("Monthly").Columns("A:A"), 0)

With Sheets("Monthly").Range("A1")
.Offset(n - 1, 2) = Sheets("Daily report").Range("F21")
.Offset(n - 1, 3) = Sheets("Daily report").Range("F17")
.Offset(n - 1, 4) = Sheets("Daily report").Range("F19")
End With

End Sub
-------end vba------

4. Press Alt + Q to return to Excel

Running the macro
----------------------
(Assuming the daily inputs in the sheet: Daily report
have been done)

5. Press Alt + F8 (or click Tools > Macro > Macros)

In the dialog box:

Click on "TransferValues" > Run
(or just double-click on "TransferValues")

---------------------

You could also assign the sub to a button drawn
from the Forms* toolbar in sheet: Daily report
(*Activate toolbar via View > Toolbars > Forms)

Or, assign to an autoshape drawn in the sheet
(via right-click on autoshape > Assign Macro)

The above would make it easier for the sub to be run
after daily input