Excel - Multiple conditional formatting

Stheno

Hey folks,

So I've an app that tracks my spending, which I'd like to like to my personal budget which is in a spreadsheet.

The app allows me to export my spending on a daily /weekly/monthly basis in .csv format which is good.

Where I'm struggling is that while I can import the csv as another worksheet on my budget spreadsheet, I'd like to automate what's on the csv to update my overall budget sheet.

So for both I have categories, so e.g. I smoke, so I've a one liner on my budget spreadsheet that is Ciggs

I also have a category in my spending tracker that is Ciggs

So I can import a weeks worth of spending and have say seven rows where ciggs is the category.

What I'm trying to figure out is how can I link the category, so if in my csv file the category is Ciggs, that it enters the figure for that row, but then adds other lines with the same category if that makes sense?

TIA for any help

Retrovertigo

It's been a while since I've used excel but from memory i'd guess the easiest way would be make a new sheet that links the data (SUM) of the columns from the previous into a new cell on the new sheet to make it cleaner.

I'm open to correction on this of course, as I said it's been a while, an example .csv might be useful.

Retrovertigo

Stheno wrote: »

What I'm trying to figure out is how can I link the category, so if in my csv file the category is Ciggs, that it enters the figure for that row, but then adds other lines with the same category if that makes sense?

Multiple cig categories? Are you trying to do differential calculations on the cig category data?

Stheno

Retrovertigo wrote: »

Multiple cig categories? Are you trying to do differential calculations on the cig category data?

No what I'm doing is tracking my spending day to day using an app that exports to a .csv

So that leaves me with different categories of spending, so I have a groceries category, and as I go through the month, I end up with multiple entries in the .csv for groceries e.g. 4th July Groceries 5.00, 6th July Groceries 7.00, so what I am trying to do is to total all of those individual figures up into one overall column for the month.

mugsymugsy

Quickly scanning this but as others said have it importing to another sheet. Then on your summary have your calls. Using sumif function that will sum all the data if certain criteria is met.

U_Fig

Without seeing the files it's difficult to know what you are looking for or need to do or the best option,

I would suggest having a sheet that you drop a new csv file into each week, then have formula in a columns or rows whichever way the spreadsheet is laid out, (i'd use sumifs) to pull into the main sheet then each week copy the formula to the next column or row and paste values over the previous line, you could also look at implementing VBA to do this automatically

Are Am Eye

I guess the key is predicting or knowing the cell names on the worksheet that will in the future be created by the app. Then your existing worksheets and formula can incorporate those cell names and therefore the values they contain and reference them. Once the app is consistent in its worksheet naming and cell location and names this should be possible. If the name of the worksheet incorporates a date then you can have a statement that concatenates a sting - the part of the name that is always the same - and a variable generated from the current date.

Retrovertigo

Stheno wrote: »

No what I'm doing is tracking my spending day to day using an app that exports to a .csv

So that leaves me with different categories of spending, so I have a groceries category, and as I go through the month, I end up with multiple entries in the .csv for groceries e.g. 4th July Groceries 5.00, 6th July Groceries 7.00, so what I am trying to do is to total all of those individual figures up into one overall column for the month.

This should be pretty easy to do, just post up a simple example of the type of csv you're getting off the app.