Take volatile cell value each day

Hi all,

Actually I am trying to do this in google spreadsheet file but with no luck, so I was thinking if I could understand it in excel, I would probably know how to do it in google spreadsheet. Here is my problem:

I have a cell that represents total numbers of some events which is being updated each day so each day it has different value. I would like to have value for each day displayed in one column, one after another. For example:
If total number of events on 09/05/2014 was 1200 and on 10/05/20104 1250 I would like to have it as 09/05/2014 1200 and below that 10/05/2014 1250 and so on. That way I will get two columns of data for my graph(date will be on x axis and number of events on y axis.

My final goal is to have graph made up date and number of events which will get updated automatically with each day.

I think I would know how to do it in VBA but I believe google spreadsheet still doesn't have that option. Tnx.

Much appreciated.

Hi,

Your excel sheet only contains date. what are the events? please attach all the information in sheet.I will try to help.
what is Total on date? Target Total on date?



Thanks

Hi,

Cell B5 yesterday had value of 1520 so I would like for cell F249 to have that value. Today value of B5 is 1575 so I would like 1575 to be in F250. Tomorrow value of B5 could be 1615 and I would like tomorrow 1615 to go to cell F251. So that is the reason why TOTAL ON DATE column is empty( only focus is on days from yesterday and up to end of this year). I tried with formula that would check if date in column E is same as today's date and if so it would take value from B5 and paste it on the correct place in column F . Problem was as soon as the today's day would pass cell in column F would say FALSE or would be empty(depending on which formula I used) because I used IF function.

TARGET TOTAL ON DATE is really not important that much. I will manually enter couple of values(which will be given to me) for couple of days and I will have my graph. And that graph is same through out entire year. Important one is DATE-TOTAL ON DATE graph. I would like to have that graph updated by each change of cell B5. That way I could compare DATE-TOTAL ON DATE graph to DATE-TARGET TOTAL ON DATE graph as soon as B5 changes and see how am I performing against my targets. I hope this helped. Tnx.

Regards

Hi,

I noticed that in my previous post i typed F249 but I meant to write F130, instead of F250 should be F131,sorry about that. My excel sheet is currently not doing anything. Basically it's empty. Imagine that cell B5 is changing each day. Today it's value is 1575. I am trying to get excel to check the value of B5 each day( it can be at particular time,lets say 10 PM). So excel checks value of B5 today at 10PM and writes it's value in cell F131 and I would have pair of data(09/05/14,1575). I want same thing for tomorrow at 10PM excel should check value of B5(let's say it is 1615), takes 1615 and places it in cell F132(got another pair of data 10/05/14,1615). Same thing for following day and so on. I would keep getting pair of data for each day, and with each pair of data my graph would change. It even doesn't have to be at particular time, it could be with each opening of the file.I am sure I could write macro for it but I need some formula as google spreadsheet still doesn't have VBA.

...I'll try. Tnx.

Let's try this way(file attached).

I have five employees. There job is to watch some sports events each day. When they are done with their job they have to write down events they have watched for each day and type in 1 next to each match(black ones in the file).Simple SUM formula will return NUMBER OF EVENTS for each employee(cell C3 in each employee sheet) for first day(10/05/14). Another SUM function(in sheet1, cell B2) sums number of events for each person and returns NUMBER OF EVENTS for first day(24 events on first day).

I would like for excel to check for value in Sheet1 cell B7 on date 10/05/14 which will be 24 and place that 24 in the Graph sheet in cell B2.(It is there now because I typed it manually but I would like that process to be automatic). So NUMBER OF EVENTS column in Graph sheet is empty( I just typed manually 24 for easier visualisation) and is getting filled by each day.

We are coming to day 2(11/05/14). That day my employees covered 15 matches(blue text) which makes it 39 in total(24 previous day). So excel should check on day 11/05/14 value of cell B7 in Sheet1 which is 39 and automatically place 39 in Graph sheet cell B3(I also did it manually for easier visualisation).

And if on day 12/05/14 my employees covered 10 matches, B7 in sheet 1 will have value 49. Excel should take that value and put it in Graph sheet in cell B4. This process should get repeated each day by excel automatically.

I hope this helped. Tnx.

Apologies for Bold letters, I am still getting familiar with this forum. Tnx.

I will try that. Tnx a lot, hopefully this will work.

Regards

Hi again,

I haven't been able to do what I want with pivot tables. I realize that I am getting a bite annoying with this but I can't sleep because of it.
I have attached another file and will try to explain what's taking away my sleep.

In the file you can see NUMBER OF BIP value in A2 cell which will get changed each day( I don't know what value will have on 18/05/2014 but It will certainly be >1500 ). In column F I would like to have value memorised for each day so I've created a function for each cell in column F.

Function goes like this(for F2): =IF($E2=TODAY(),$A$2,IF($E2<>TODAY(),$F2)). I want excel to check if cell E2 is same as today's date and if so to take value from A2 and copy it to F3. And it's working fine when condition is meet but as soon as today ends and date changes to following day formula breaks and value in F2 would get lost(that is what is killing me,if I could only somehow have it to stay permanently). Anyone have some suggestions?

Tnx.

Tnx a lot anyway. You've already helped a lot.