MS Excel help.

[Trader]

I'll try to explain what I'm trying to do....more for fun than any real need.

I'm doing a book keeping spreadsheet.

 

On a summary page, I have a formula that adds the numbers in a column based on the "code" found in the next cell. This helps me to track different sources of income, each one with it's own code

 

=SUMIF(A4:A85,U93,E4:E85)

 

so this formula says to compare the letter code found the cells A4 to A85 and IF they are the same as the comparison cell (U93) then return the SUM of the value from cells E4 to E85.

This formual is repeated IN DIFFERENT CELLS with different comparison cell codes. (U94, or U95)

 

I want create a similar formula to read a name found anywhere in a range of cells and if it is there, to then print that name in another cell. (on the summary page) yet, not have other cells repeat that name ....AND.... if that name is not in that range of cells, to allow a different name to be returned in that same cell

 

 

I can't use the same formula because I am asking for text...not numbers.

 

in much the same manner, I can look at the range of cells, and return a number associated with that company name

=SUMIF(B11:B89,"Hydro One",J11:J89)

 

...asking it to give me the SUM of payouts found in cells J11 to J89, as long as "Hydro One" is contained somewhere in the range of cells from B11 to B89

 

 

This formula works fine when I ask it to look at a single cell.

=IF(B11=R101,"company name","")

so in the cell on the summary page... the formula looks to see if cell B:11 contains the same name as the comparison cell (R101), if it does, it displays "Company name", if that exact name is not found , it displays nothing ("")

 

When I ask it to look at a range of cells it returns the "false" value ("") because that specific text is not in every cell of the range.

I THOUGHT it would check the entire range of cells to see if that name is in ANY of the cells....but it is actually checking ot ensure the name is in ALL of the cells

=IF(B11:B85=R101,"company name","")

 

What formula can I use to get it to check a range of cells, and return a name from a multiple choice of 5 companies. Of course if any name is already used in that cell then it has to just forget about the whole thing and move on.

The next cell below would do the same until all the vendors are listed along with their totals.

Any suggestions...perhaps a macro?

 

I could just type in in...but what's the fun in that?

Edited December 31, 2010 by Trader
I confused myself with that one!

[dingy]

Look at using the 'Match' & 'Index' function.

 

Formula below is an example.

 

It first indexes all cells in a range from Q62 to Q118.

 

Then it looks for a match to cell B8 anywhere in the Q62 to Q118 range.

 

If this formula is in cell A1, then a match found would display in A1 cell.

 

=INDEX(Q62:Q118,MATCH(B8,Q62:Q118))

 

If you need it, I could send you an example of it in use.

 

PM me with an email address.

 

Gary

Edited December 31, 2010 by dingy
edited formula

[SilvrT]

If your range spans multiple columns and/or rows, then use an ARRAY formula.

 

I will come back with an example in a few minutes...

 

OK, just enter the formula =SUMIF(B11:B89,"Hydro One",J11:J89)

 

but instead of hitting Enter, use Ctrl+Shift+Enter

 

you will end up with an ARRAY formula that looks like this -> {=SUMIF(B11:B89,"Hydro One",J11:J89)}

 

and it should work.

Edited December 31, 2010 by SilvrT

[MikeWa]

You might be able to use an (if;than) formula. The than can be a name or a cell location where the name exhists. Or functions "if logical test (value-cel if true) (value-cel if false).

Edited December 31, 2010 by MikeWa

[SilvrT]

If you want a "macro", I could write anything you need in VBA code for you.