Help [Conditional sum]

Hi there. Registered a minute ago. Have been searching the web for long. Here is my question: In Excel I have a calculation sheet, where result values are calculated basis entries in the same worksheet. I am dealing with consumption in a week basis. What I do so far manually is, to clear the contents of ,say, 2 days of the week, and observe the new balance of the remaining quantities. After I just undo the deletion and return to normal. Is there a way to automate this ? The only I want is to insert a date in a cell and automatically to erase the contents of a certain range, then on a keyboard click, space bar or other, to undo the previous action.

Thank you very much. Any help is welcome.

Captain Anthony

Hi Anthony and welcome to the forum.

What I do so far manually is, to clear the contents of ,say, 2 days of the week, and observe the new balance of the remaining quantities. After I just undo the deletion and return to normal.

Okay, this is definitely not the correct way to get your numbers.

I think that we can improve your formulas (or use pivot tables etc) to conditionally calculate for the relevant dates. This would be better than manually (or automatically via VBA) deleting / restoring rows. It's hard to be more specific without an example, so you are welcome to zip and attach a sample directly to this thread.

Hope that helps...

Dear all good day and thanks for the replies. Please let me give you the idea of what I am doing (manually so far). When I insert the figures in the table, the balance is reflected on the balance table. In case I need to know the available balance for a given day, I select the entries after a certain day and automatically I have the new balance. Then I undo the deletion and revert to normal. Although this procedure looks very simple, if I forget to undo, it creates a mess. Therefore the idea is, to create a macro to delete the contents of a range, then pause to allow me viewing the new availability, then undo the deletion.

Thank you very much for your kind efforts. If you happen to know a solution, please advise.

Rem: I have started with Lotus 1-2-3 on a floppy, then switched to Ability plus with the revolutionary ability to WYSIWYG. Occasionally I think that the tasks, although less rich than today, they were achieved easily.
Sometimes I recall my 3rd computer in a raw of xxx, the QL by that extremely smart and brilliant guy, Sir Clive !!! It was accomplishing the tasks so fast and at a fraction of the resources available in the new machines.

Anyway. Thanks again and await for help.

Anthony

Hello Anthony,

You posted your reply on someone else's thread so I have moved it onto here.



Therefore the idea is, to create a macro to delete the contents of a range, then pause to allow me viewing the new availability, then undo the deletion.

As I already explained, this is not a good idea.
For example, what if the macro errors half way and your data is lost? The point is you should not be deleting data - manually or automatically - to derive your calculations when you require that data to be restored afterwards. It's overly complicated and totally unnecessary.

You can simply use formulas (or perhaps pivot tables) to conditionally sum the relevant dates. Are you familar with worksheet functions such as SUMIF()?


Rem: I have started with Lotus 1-2-3 on a floppy, then switched to Ability plus with the revolutionary ability to WYSIWYG. Occasionally I think that the tasks, although less rich than today, they were achieved easily.
Sometimes I recall my 3rd computer in a raw of xxx, the QL by that extremely smart and brilliant guy, Sir Clive !!! It was accomplishing the tasks so fast and at a fraction of the resources available in the new machines.

How is any of this relevant?


If you are struggling with the formulas then zip and attach a workbook showing what you have tried, what you are trying to get and what difficulties you are having.

Hope that helps...

Hello dear Colin and thanks for your suggestion for the SUMIF. I'll give a try.

Anthony