Excel Formula Quickie

[HiRich]

Hopefully, if Excel can do this.

My spreadsheet records the race history of race drivers. On a series of sheets, one for each year, I have a list of drivers for that year, and a table marking which events they appeared at. At the end I have a column that concatenates their name details (e.g. HuntIsaacIC). The original driver details are added manually on these pages.

I also have a summary page, listing every driver ever. Same concatenated name column, same manual entry (for several reasons I won't bore you with, this is the best way to do it).

The problem I have is cross checking that all the drivers are entered on the correct sheets. On the Drivers page, I have a sum check, that alerts me if someone is missing or incorrectly typed in a particular year, but it doesn't give me a clue as to which driver it might be.

What I want to do is put a flag beside the column of concatenated names on each of the Year pages. This shoud work along the lines of:
IF: concatenated name string in the cell immediately to the left appears anywhere in the column of concatenated names on the Driver page...
TRUE: nothing
FALSE: "X"
A bit of conditional formatting then makes the X appear in a red square - a nice big alert flag

What I can't find is the correct "match"-type formula, to look at all the concatenated names on the DRIVER page, and so create the correct IF rule. Any ideas?

[TonyHetherington]

could you not do it using LOOKUP ? Either HLOOKUP or VLOOKUP for vertical or horizontal?!


oooo, COUNTIF might be useful too?

Edited by TonyHetherington on Friday 10th August 14:35

[TonyHetherington]

Hang on, there is a formula called MATCH...

MATCH(lookup_value,lookup_array,match_type)

returns the relative position of an item in an array that matches a specified value

[HiRich]

Thanks Tony, COUNTIF works close enough - gives me 0, 1,and a few higher, rather than the ideal tick and cross. Conditional Formatting gives me the red square on the 0. That's the bit I really needed.

[limpsfield]

I thought I would drag an old thread up to ask for some help.

How do I sum up the remaining cells in a row if the first cell value is specific text?


So in this example, if D2 is ABC then add up cells E2 to G2?

Not an excel whizz by any means and really struggling with this one.

[Ultuous]

=if(D2="ABC",sum(E2:G2),"")

[paulrockliffe]

The SUM function ignores text, so = SUM(A1:D1) will add up any numbers in those columns. = A1 + B1 + C1 + D1 will give you an error.

[limpsfield]

Ultuous said:

=if(D2="ABC",sum(E2:G2),"")

Perfect Ultuous - thank you very much!

[paulrockliffe]

limpsfield said:

Ultuous said:

=if(D2="ABC",sum(E2:G2),"")

Perfect Ultuous - thank you very much!

That won't work as soon as your valuer isn't ABC.

If you wanted to do it this way you need to use a function that tests the value, eg = IF( ISTEXT(A1)=TRUE, SUM(B1:D1), "")

Edit - Just realised I misread your example. The above will test the cell for you and work for any text string.