Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill cell based on todays date
My problem is this, I have a sales graph for my sales people that needs to
collect daily sales per day. I enter the sales as they come in, in "sheet 1" of the workbook (the graph is in "sheet 2"). These sales are then collated into a total for the month - as the month goes by, this number grows. The graph I have is a line graph that crawls along like a worm increasing as it goes based on the number of sales for the month. How do I create a formula that will look from one sheet to another, can remain blank leading up to that particular day, pick up the value of that cell on that day and then keep the value static the next day, then next days cell then picks up the new sales total and so on. I would imagine sosmething along the lines of "if less than 22/08/08 then "" otherwise a1, if greater than 22/08/08 then remain static"? What i need to happen is that the sales value on that particular day stays at that value as the month goes by so that i can create a creeping graph of sales for the month. Hopefully this makes sense - happy to clairify further if you like. -- Struggling with technology |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill cell based on todays date
Tomahawk wrote:
My problem is this, I have a sales graph for my sales people that needs to collect daily sales per day. I enter the sales as they come in, in "sheet 1" of the workbook (the graph is in "sheet 2"). These sales are then collated into a total for the month - as the month goes by, this number grows. The graph I have is a line graph that crawls along like a worm increasing as it goes based on the number of sales for the month. How do I create a formula that will look from one sheet to another, can remain blank leading up to that particular day, pick up the value of that cell on that day and then keep the value static the next day, then next days cell then picks up the new sales total and so on. I would imagine sosmething along the lines of "if less than 22/08/08 then "" otherwise a1, if greater than 22/08/08 then remain static"? What i need to happen is that the sales value on that particular day stays at that value as the month goes by so that i can create a creeping graph of sales for the month. Hopefully this makes sense - happy to clairify further if you like. I'm not sure I understand, let's see: You would like a chart with sales date on x-axis and cumulative sales totals on y-axis? This is the part I'm really not sure about: You enter incremental information on each calendar day that might include results from previous sales days. E.g., you might enter sales for 22/08/08 on that day, and then enter more sales results for 22/08/08 on the next day, 23/08/08. Situation 1: /But/ you do not want to count these "late" results? Situation 2: (Much easier) You need to know the cumulative sales totals by sales date, regardless of the calendar date in which you enter the results? Am I close? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill cell based on todays date
Hi, Thanks for trying to get your head around this for me. Lets see if i can
explain a little better for you. Yes you are correct in the graph, the vertical axis (y) has increasing $$ $0 - $100 for example. Then along the horizontal axis (x) I have the days of the week 1st - 31st for example. I have set up a table (hidden) that I manually input the specific sales total at the end of each day (the graph is linked to this table obviously). With a large sales team this is time consuming and it is this process I want to automate. In a seperate spreadsheet I collate the daily sales for the team. This is totalled in one cell to give a monthly sales total. What I am trying to do is to have the table look at the sales total and add that particular total on the day that it represents. So the cell allocated to the 5th of the month will display the sales total for that day only. If at the end of the day that value is $10 then that is the number that stays in the cell for the rest of the month. The cell on the 6th will then display the sales result at the end of that day only. So at the end of the sales period I have a line graph that slowly crawls along going from $0 - $100 for example. I have experimented with things like =if(today=22/08/08,(a1),"",) but either doesnt work or will not retain the sales value on that particular day. If you have a solution to this I would love to hear it. I have looked conditional formatting but am not sure that that is the answer either. Thanks - talk to you soon -- Struggling with technology "smartin" wrote: Tomahawk wrote: My problem is this, I have a sales graph for my sales people that needs to collect daily sales per day. I enter the sales as they come in, in "sheet 1" of the workbook (the graph is in "sheet 2"). These sales are then collated into a total for the month - as the month goes by, this number grows. The graph I have is a line graph that crawls along like a worm increasing as it goes based on the number of sales for the month. How do I create a formula that will look from one sheet to another, can remain blank leading up to that particular day, pick up the value of that cell on that day and then keep the value static the next day, then next days cell then picks up the new sales total and so on. I would imagine sosmething along the lines of "if less than 22/08/08 then "" otherwise a1, if greater than 22/08/08 then remain static"? What i need to happen is that the sales value on that particular day stays at that value as the month goes by so that i can create a creeping graph of sales for the month. Hopefully this makes sense - happy to clairify further if you like. I'm not sure I understand, let's see: You would like a chart with sales date on x-axis and cumulative sales totals on y-axis? This is the part I'm really not sure about: You enter incremental information on each calendar day that might include results from previous sales days. E.g., you might enter sales for 22/08/08 on that day, and then enter more sales results for 22/08/08 on the next day, 23/08/08. Situation 1: /But/ you do not want to count these "late" results? Situation 2: (Much easier) You need to know the cumulative sales totals by sales date, regardless of the calendar date in which you enter the results? Am I close? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofill cell based on todays date
Tomahawk wrote:
Hi, Thanks for trying to get your head around this for me. Lets see if i can explain a little better for you. Yes you are correct in the graph, the vertical axis (y) has increasing $$ $0 - $100 for example. Then along the horizontal axis (x) I have the days of the week 1st - 31st for example. I have set up a table (hidden) that I manually input the specific sales total at the end of each day (the graph is linked to this table obviously). With a large sales team this is time consuming and it is this process I want to automate. In a seperate spreadsheet I collate the daily sales for the team. This is totalled in one cell to give a monthly sales total. What I am trying to do is to have the table look at the sales total and add that particular total on the day that it represents. So the cell allocated to the 5th of the month will display the sales total for that day only. If at the end of the day that value is $10 then that is the number that stays in the cell for the rest of the month. The cell on the 6th will then display the sales result at the end of that day only. So at the end of the sales period I have a line graph that slowly crawls along going from $0 - $100 for example. I have experimented with things like =if(today=22/08/08,(a1),"",) but either doesnt work or will not retain the sales value on that particular day. If you have a solution to this I would love to hear it. I have looked conditional formatting but am not sure that that is the answer either. Thanks - talk to you soon OK here's what I think you need to do. It's just a little organization really. You are able to "collate the daily sales". This value needs to be captured for every day you have data. Since I don't know what your data entry looks like, I can't say exactly how you would go about it. One way might be to have a list of dates in a column somewhere separate from the data entry, and use sumif to obtain the daily total. Another way is to use a pivot table, which can automatically get daily totals with building a list of dates. Another way is to use subtotals, but this will not make it easy to do the next step. Now you have a total for every date. The next thing to do is get a running sum. That is, start with day one and add each day's result to the sum of the days prior. Columns C and D below offer two suggestions for doing this. The formulae in C2 and D2 can be copied down. Note: a pivot table can be configured to figure the running sum automatically. A B C D 1 22/08/08 day1 total =A1 =sum($A$1:A1) 2 23/08/08 day2 total =B1+A2 =sum($A$1:A2) Regardless of how you get the running sum, /that/ is the column you want to base your chart values on. Hope this helps! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
Put todays date in a cell by checking a box!! | Excel Discussion (Misc queries) | |||
In Excel, Get todays date in a cell | Excel Discussion (Misc queries) | |||
Setting traffic lights based on todays date against target dates | Excel Discussion (Misc queries) | |||
Autofill date based on date in another cell | Excel Worksheet Functions |