The easy way is with a button which acts on selected data fields and a
cell containing =Day(Now()) (on the example, cell C10 in Sheet1)
Select a cell to hold the Day number, and in the cell to the left of it
right-aline the words 'Update Day '
In the cell to the right of it create a button, and the code for the
button needs to include lines such as:
Dim dy As Integer
dy = Range("sheet1!c10").Value
Range("sheet2!a" & dy).Value = Range("sheet1!b3").Value
Range("sheet2!b" & dy).Value = Range("sheet1!b4").Value
Range("sheet2!c" & dy).Value = Range("sheet1!b5").Value
which, for day 16, makes Sheet2 cell a15 a copy of Sheet1 cell B3 etc.
You will need to click this button at the end of each day, or, better
still, each time you enter figures so as not to miss any.
If you need help to create a button just reply so.
Biff Wrote:
Hi!
Here's half a solution.....
The "problem" with what you want is that if you use formulas the
formulas
don't or can't automatically convert to constants unless you use some
VBA
code.
The formula part is easy.....
In Sheet2 B2 enter this formula:
=IF($A2=TODAY(),VLOOKUP(B$1,Sheet1!$A$1:$B$10,2,0) ,"")
Copy across then down as needed.
Now comes the tricky part!
At the end of the day (before midnight when the date changes) you will
have
to convert the formulas in the row that corresponds with that days date
to
constants. My VBA skills are truly lacking but I know enough (I think)
to
come up with a very simple macro to do that.
Sub MakeValues()
Selection.Value = Selection.Value
End Sub
You could put a button on Sheet2 and assign the macro to that button.
At the
end of that day, say the date is 8/14/2005, you would select all the
cells
starting in B2 and to the right then click the button to convert the
formulas to constants.
Maybe someone will chime in with a more robust way to convert the
formulas
to constants.
Biff
"n0 h4ck1ng"
wrote
in message
...
on sheet1 i have a list of items in col a and in col b i enter the
qty
of those items on a daily basis ex.
.......a..............................b........... ..............
beef................................3............. ......
cod..................................1
chicken............................24
col b is different every day
on sheet 2 i have a list of days in col a col b would be beef col
c
would be cod and col d would be chicken ex....
.........a..........................b............. ............c...................d................. .
..................................beef............ ........cod.................chicken
8/14/05
8/15/05
8/16/05
if today is the 14th i need col b to enter the number for beef on
sheet
1 same for cod ect... than tommarow when i change those #'s on
sheet1 i
need the 14th's # to stay and the new # to go to the 15th keeping a
daily log of those #'s
is this posible please help???
thank you
--
n0 h4ck1ng
------------------------------------------------------------------------
n0 h4ck1ng's Profile:
http://www.excelforum.com/member.php...o&userid=18410
View this thread:
http://www.excelforum.com/showthread...hreadid=395696
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=395696