find text and color cell and sum cell number

[broholm]

Hi All

I am making my own budget and have trouble doing the following:

From my online bank I export all my account activities one month at a time to their own sheet: January, Febuary, March etc.

I have one sheet i call "calculation" which have the months as columns and expenses posts as rows. As an example I have a post called "car insurance". In the cell where this row hits the January column I have the following function:

=SUMIF(January!F:F;"*123456789*";January!D:D)

This gives me the sum of column D when a text in column F matches an account activity with a withdrawel ID of "123456789"

So far so good. What I would like the function to do is:

=SUMIF(January!F:F;"*123456789*";January!D:D) AND In case you find a text matching "123456789" Color that cell on sheet January green.

This way I can check the sheet January and see what rows Excel automatically has summed to my "calculation" sheet.

I hope this makes sense. Hope someone can help. Thanks in advance

Broholm.

 

[broholm]

Hi everyone. Me again.

I have tried posting this question on two other expert boards and noone has answered. Is this because the question is too complex? Am I trying to make Excel do something it was not intended to do? Hope someone can help?

 

[starl]

It is a little complex to understand, but I think what you are saying is, on the individual months sheets, you want to color a cell green if it's been included on your Calculation sheet. My first thought would be conditional formatting - but then I wondered why you want to color the cells? And why do you have * around the withdrawal IDs? Are you wanting to verify there are no IDs missed? If so - then this Conditional Format formula would work (assume that the first ID I"m checking is A1):
=A1 = VLOOKUP(A1,mylist,1,FALSE)
mylist is the list of IDs I'm comparing to. Since this list is on your Calculation sheet, you'll have to setup a range name.
Basically, I'm saying if the cell I"m in matches the cell I"m looking for, then color the cell