help with formulas and dates???

[n0 h4ck1ng]

on sheet1 i have a list of items in col a and in col b i enter the qt
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
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 shee
1 same for cod ect... than tommarow when i change those #'s on sheet1
need the 14th's # to stay and the new # to go to the 15th keeping
daily log of those #'s

is this posible please help???

thank yo

 

[Biff]

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

 

[Bryan Hessey]

The easy way is with a button which acts on selected data fields and
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 i
right-aline the words 'Update Day '
In the cell to the right of it create a button, and the code for th
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, bette
still, each time you enter figures so as not to miss any.

If you need help to create a button just reply so.

 

[n0 h4ck1ng]

that worked great but does anyone know of a way to make that value stay
after that day as soon as its entered ... the chart im showing is just
a crude one... it would be to hard to go throught and select 125 cels to
run the macro i was given

 

[n0 h4ck1ng]

is it posible to create a button to lock only the cells that have
returned a value without having to select those cells i want
constant...and without ruining the formulas in the blank cells????

 

[Bryan Hessey]

Was that in response to Biff or to myself? - if to myself then you would
need to specify each range or cell on another row in the macro, but I do
not understand why you would have formula in 'blank cells' if you wish
to overwrite them with figures as requested.

 

[n0 h4ck1ng]

mabey this will help explain my prob. on page one there are 6 cells
looking for data for that day b2....e2.....h2.....k2...etc than on
page 2 there are six more b46.......e46........h46 so on and so on for
23 pages that would be for one day ie the 14th.. on the 15th it would
be the cells right below those all other cell are doing calculations
for that day based on those numbers so ...with 23 pages and all those
single cell it would take a while to select them than run the macro
here is the formula i have in b2 right now

=IF($A1=TODAY(),Sheet1!B2,"")

does this help with what im looking for or am i confused beyond help???
lol

thanx

 

[Bryan Hessey]

If you are using my button you would specify the 'copy from cell' and
the 'copy to cell' once for each item

Then each day after just click the button and it would update the line
according to the day number.

I showed only 3 lines as that was as per your sample, they were

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

If you have more you would just copy a line and keep pasting and
amending to form a new line, ie, 1 copy and 3 pastes gives

Range("sheet2!c" & dy).Value = Range("sheet1!b5").Value
Range("sheet2!c" & dy).Value = Range("sheet1!b5").Value
Range("sheet2!c" & dy).Value = Range("sheet1!b5").Value

and with a small amount of typing you can amend to the required cells.

You do NOT have to select the cells before pressing the button.

You do need a range of cells that can be entered/calculated for each
Day

Does that help to un-confuse you?

 

[n0 h4ck1ng]

ahhhh ok i get it now, thank you now if you could tell me how to make
this button and where to enter the code i should have my problem solved
i have toyed around with VBA but have not actualy used it yet

thanks again this solution will take off at least two days worth of
work out of my month

 

[Bryan Hessey]

To create a button, View, Toolbars and ensure Control Toolbox it
ticked.

the first icon on thatt bar reflects 'Edit Mode', click that, then
click the button icon.

click (or double click) in the cell to make a button, and right-mouse
Properties to set a Caption Name "Update Day Figures" and a button back
colour
click View Code and enter the code previously shown.

Then click the 'Edit Mode' icon again (which now shows 'Exit Edit
Mode')

Let me know how you go