I am trying to create an expense report for my account on Amazon. Sometimes the purchases are all for one expense category. Other times the purchases are for a mix of expense categories.
I created a column with Data Validation menu that is just a Yes or No. If Yes then there is only one category. If No then I need to create to create a formula that opens a new spreadsheet.
The new spreadsheet needs to be blank of values but includes formatting and formulas to list the other purchases, their individual expense categories, and a sanity check that shows that the sum of the individual expenses is the same as the original transaction amount.
Any help would be appreciated. I can make the drop down menus. I know what the IF-THEN formula is, but I don't know how to get it to open a preformatted spread sheet.
Thank you,
david
I think the only way is for you to add a sheet that is formatted just as you require but with no data, to act as a template for new sheets when required.
You will need a Worksheet_Change Macro which will fire when you select "No" from the Data Validation List. The macro will copy the Template sheet as a new sheet and can also name the new sheet as required and add the necessary data.
Hello,
Just want to point out that you can set up a data table in a tabular format, so regardless if there is only one or several categories, you just put in one row of data per category. For this to work you need of course an id of some sort that is the same for all those rows, to connect them.
Br,
Anders
