Toggle between sum/date in same cell

[sanzet]

Hi guys.

I'm working with a project where I am trying to produce a worksheet containing
A) The sum of the order that is to be paid
B) The date where they have to be paid

I have created a very simple table with the names of the delivering companies in the columns, and project names in the rows. In each cell I then add the sum of the order that we are going to place. Example:

	Mike's Iron	Toys'R'Us
Project 1	5000	50
Project 2		20000

And so on..

Now, what I need is to be able to click a toggle button (or any other method that works) that switches the sums to dates! Like so:

	Mike's Iron	Toys'R'Us
Project 1	2014-01-15	2014-01-25
Project 2		2014-02-01

Preferably I would like to be able to press the button and "clear" all the cells where the sums are already added, and then manually add the dates to the same cells, and then when I press the toggle button again all the sums should return. Then I can switch between sum/date and edit any of the two on the fly, cell by cell. I imagine this would require the worksheet to be duplicated when the button is pressed, cleared of all information except the captions, and then switched between worksheet A and B depending on if the button is pressed or not. But perhaps there is another way?

I think it would be easier if I would just add the sums and dates separately in other cells and have the togglebutton "gather" the information from the cells and display them in my target cell. But I would really like to edit the cell directly and not have to add values somewhere else. Otherwise I feel that it would take a lot of time getting the toggle button to display the right values (seeing as rows are deleted when the project is fully paid for etc.). The same problem could occur if the worksheet is duplicated somehow; when I delete or add rows when projects are finished/new it feels like it could mess up the code.

It's a real doozy (is that how you say it?)! I hope anyone can help me with this.

[Kaper]

If you want to stick with your (IMHO - suboptimal :-P) idea of having a button to restore formulas, my advice is as folows:
prepare sheet with formulas.
Start registering macro.
Position cursor in a first cell with formula. Edit (F2) and of course do not change it, just press enter. Then repeat it for all cells with formulas.
Stop registering.
Now you have a ready to use macro for filling all cells with formulas. just assign it to a button or other shape and use it.
so when you need just write the days and when you need to restore formulas - just press a button.

[millz]

Not sure how your data looks like, see if this helps?

[sanzet]

Kaper: Thanks for your input, not really what I was thinking but I'll try it out just for fun.

Not sure how your data looks like, see if this helps?

It's really basic, something like your example (it's used to get a quick overview of coming payments).

I suspected your way might be the easiest (or only) way to do this. The problem, however, is that I would need to keep two different tables and update them accordingly when new projects arrive and old ones are removed.

I was hoping there is a way to combine these two tables into one - somehow. Meaning I could press the button, and the cells with the dollar value would get wiped (but with the sums remaining "in memory") and allow me to add the dates. And then when I press the button, it will remember both the sums and the dates and switch between the two in the cells when I press the button.

I completely understand if this is not possible, I just wanted to ask


Otherwise, I think your example might be the best option. I would just need to find a good way to replicate the table to make sure the dates are inserted in the right cell when I "Switch Data". If there is time, I might try to create some sort of form when adding/removing projects (where I can add the sums and dates at the same time and they are added to two different tables automatically) and then use your example to quickly switch between the two. It's probably too time consuming for this simple thing, that's why I was hoping for a "quick fix"!

[millz]

If you want something fun or quirky, do invest some time into making it happen

Meaning I could press the button, and the cells with the dollar value would get wiped (but with the sums remaining "in memory") and allow me to add the dates. And then when I press the button, it will remember both the sums and the dates and switch between the two in the cells when I press the button.

The "memory" you are talking about, can be like another table, just like in the example I have shown.
You can place it in a different worksheet, and then very hide it. That way, it remembers your data, and yet still seem invisible.