Hello friends
I need to sum up the sales figure. I have a column for daily sales till end of day and a column showing monthly figure till today. Is there any way I can have a formula or way to have the monthly figure to update by itself once I enter daily sales figure?
Eg: column B had a value 10. I will enter in column A total sale for today as 4 and when press enter button, the value in column B should turn to 14.
Pls help
Thanks
sajith
Please attach a workbook, showing your existing structure, and your desired outcome.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
HiOriginally Posted by OllyXLS
Unfortunately I am using the mobile to post the querry and have no access to internet on the computer I am working on.
My will try to explain my Q more detail.
in a spreadsheet
column A has name of staff, X.
Column B has daily no. of units sold
column C has month till date Sales made.
Today 21.01 staff X sells 2uts.
When I open the file, column C is having 10uts sold till 20.01.14
I will enter 2 in column B.
I am looking for the value in C to change to 12 once I enter 2 and press the 'eenter' key.
I hope my Q is more clear now.
Sajith
Not much clearer, I'm afraid. Do you have a row per staff member per day? What happens with today's sales value (2 units) when you open the file tomorrow?
It sounds like you want the value in column C to update without there being a formula and a list of daily sales anywhere. Is that what you want? If so, can you change your data structure, and / or can you use VBA?
It's really hard to help you without seeing what we're working with, and what your desired outcome looks like.
The figure 2 will appear same tomorrow as well. If X sells another 3 uts tomorrow I will enter 3 in B and value has to change to 12+3=15. Once I do this, I will circulate the file among the staff.
I meant the value in C has to change to 15.
No progress from those answers. Tomorrow's entry of 3 - does this replace the value of 2, in the same cell, or is it entered in another row?
And you haven't answered my questions on whether you can change the workbook structure, and / or use VBA.
Unless you can provide a sample workbook, showing your layout, and how you would like it to work for daily updates, then I cannot help any further.
Yes the figure 3 will replace 2 already available when I open the work book tomorrow. I have not used VBA so far. But tried to enter macros in a workbook.
I get your point, will try to upload the file a bit later
Here is a sample workbook. hope it gives the idea
I can see 2 separate tables for 2 days. If each day have each separate table like this, day after day goes down, try this:
E15=LOOKUP(2,1/($A$1:A14=A15),$E$1:E14)+C15
Drag down.
If you have 1 table for current day only, I afraid that VBA solution must be applied.
Quang PT
Hi Unfortunately
No.. The table will be only one and everyday the value on daily sales will keep changing
That's really poor spreadsheet design. How can you track daily entries?
If you MUST use such a structure, then the following code would do what you desire:
Whenever a cell value in the range C5:C8 changes, the new value is added onto the existing value in the corresponding cell in Column E.
Please Login or Register to view this content.
Let me repeat, I strongly recommend against this approach. it is poor design. You lose visibility (and potentially integrity) of your data.
It would be better deign to keep a source table of sales by executive by date in one master table, then compile a summary report from that table. Every day add new records to your data table. This would allow checking and changes to be made, and would allow greater flexibility in reporting.
Last edited by Olly; 01-21-2014 at 12:58 PM.
Thank you very much. Will try ur advise as soon as I can. Infact the table send to u has only few fields to give u the idea of what I am looking for. The data has many xolumns and rows thought will confuse u more.That's really poor spreadsheet design. How can you track daily entries?
If you MUST use such a structure, then the following code would do what you desire:
Whenever a cell value in the range C5:C8 changes, the new value is added onto the existing value in the corresponding cell in Column E.Please Login or Register to view this content.
Let me repeat, I strongly recommend against this approach. it is poor design. You lose visibility (and potentially integrity) of your data.
It would be better deign to keep a source table of sales by executive by date in one master table, then compile a summary report from that table. Every day add new records to your data table. This would allow checking and changes to be made, and would allow greater flexibility in reporting.
Thanks a ton once again will keep ur advise in mind.
Will keep u posted on the result
It worked.. Thanks a ton... :-)
Hi... Today I have another thought on the formatThat's really poor spreadsheet design. How can you track daily entries?
If you MUST use such a structure, then the following code would do what you desire:
Whenever a cell value in the range C5:C8 changes, the new value is added onto the existing value in the corresponding cell in Column E.Please Login or Register to view this content.
Let me repeat, I strongly recommend against this approach. it is poor design. You lose visibility (and potentially integrity) of your data.
It would be better deign to keep a source table of sales by executive by date in one master table, then compile a summary report from that table. Every day add new records to your data table. This would allow checking and changes to be made, and would allow greater flexibility in reporting.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks