Excel 2003 populate cell on certain date

Sigh, Dr Google isn't helping. Have a spreadsheet with date at the top of the page which auto calculate, {=today()-1}. Each day I need to automatically grab data from say cell F5 and place it onto a cell worksheet #2 page.

So if date for example is 1-4-2015 operator inputs data on worksheet #1, cell F5, then that data from worksheet #1 cell F5 gets automatically copied into cell G5 on worksheet #2. Then the spreadsheet gets saved by operator. Then next day 2-4-2015 operator puts new data into F5 and that gets copied to G6 on worksheet #2, ( the next cell down in that column), Operator then saves.

geek_nice_lady, Apr 5, 4:04 pm

Sounds like it would need to be a lookup function

In theory as long as sheet 2 had a date column alongside column G then process would be,
enter date and value in sheet #1,
then using the date from sheet #1 do a vlookup for the date in the range (dates + G. in sheet#2)
when found dump result into that row using the vlookup reference

geek_king1, Apr 5, 4:40 pm

would possibly need to use the datevalue function on the date when doing the vlookup

geek_king1, Apr 5, 4:44 pm

Thanks. Still figuring this one out but I think it's possible :-)

geek_nice_lady, Apr 6, 5:31 am

ok spent a lot of time on this so far, am getting slowly forward but a bit more help if possible ?

got spreadsheet with this forumula in one of the sheets
=IF($'sheet 1'.H3="4","-
test")
this puts the word 'test' into the cell I have the formula linked to on a second sheet, (within the same workbook).

but what i really need is not the word 'test' as the output but a copy of the info in a cell on the first sheet as the output. and I cant get it to work.

thus =IF($sheet 1'.H3="4","-
H4sheet1")
I want the output in my cell on sheet 2 to show the info which has been calculated and shows in sheet 1 cell H4. Cant' seem to get it to play nice !

geek_nice_lady, Apr 6, 2:47 pm

sorry bout the screwy formatting on the formulas on the question TM is 'breaking' the lines.

geek_nice_lady, Apr 6, 2:47 pm

there should NOT be a 'dash' ( - ) in the following: "-
test")

or a dash in ,"-
H4sheet1")

grr

geek_nice_lady, Apr 6, 2:49 pm

This goes Wherever you want the value ie sheet 2
=IF(Sheet1!H3=4,Sheet1!H3,&quo-
t;Not 4")

&quo-
t;
Equals single double quote

geek_king1, Apr 6, 5:05 pm

instead of pushing it from sheet one, can it be pulled over from sheet two
something like
if sheet one f5 =? then g5=sheet one f5 else **

and you can workout the syntax, lol

geek_skin1235, Apr 6, 6:24 pm

Thanks guys will keep trying

geek_nice_lady, Apr 6, 6:49 pm

I think you're going to have to delve into a bit of VBA (i.e. macros) here. The problem with referencing F5 in sheet one is that whenever the operator enters a new value all the historical data is lost. You need to find a way to copy the value to Sheet2 every time it changes.
It'd be a fairly simple bit of a code but quite a learning curve if you have no VBA experience.

geek_floydbloke, Apr 7, 7:48 am

Yes you might be right. Hubby has looked at VBA a "little" but he says he dont' want to know lol.

Anyway we have gotten a little further with this and now have a working formula which picks up the info off sheet1 and places it into the cells on sheet2 "on date", thus:

=IF($'sheet 1'.H3="5",$'-
sheet 2'.H31,"0")

=IF($'sheet 1'.H3="6",$'-
sheet 2'.H31,"0")

etc

as you can see the last command in the formula reads "0". This puts a zero into the field if the date in "H3" on sheet 1 has changed, (a return on the formula of FALSE). Problem is that on date "5" all is good (assuming that IS the date), however on date "6" the formula puts a zero into the field on cell for date "5". What we need is for the info which is already on the form (say in date '5" to stay UNchanged on the following day.

So the formula writes info to the cell in sheet 2 on date 5 - all good
but on date 6 I want the info which was written to the previous cell the previous day to stay UNchanged. So instead of "0" I want NO change to happen to the PREVIOUS days info.

Phew ! Any ideas appreciated !

geek_nice_lady, Apr 7, 7:58 am

bloody TM formatting

the "-" after the "$" referencing sheet 2 should not be in there at all !

geek_nice_lady, Apr 7, 8:00 am

Excellent result, got it working. Thanks for your input gus.

geek_nice_lady, Apr 8, 5:16 am

thanks but I have figured it out and no macro needed.

heres how:

=IF($'sheet 1'.H3="6",$'-
sheet '.H31,A8)
with H3 date changed for each day and the final reference "A8" changed to suit the cell the formula is in Ie:

=IF($'sheet 1'.H3="6",$'-
sheet 2'.H31,A8)
=IF($'sheet 1'.H3="7",$'-
sheet 2'.H31,A9)
=IF($'sheet 1'.H3="8",$'-
sheet 2'.H31,A10)

etc

had to allow 'iterations' also so it could do 'circular references

geek_nice_lady, Apr 8, 8:01 am

sorry, as usual TM has stuffed up the formatting a bit. where it says {,$'sheet 2'} there should be NO "-"

geek_nice_lady, Mar 14, 5:22 pm

Share this thread