Too many nested ifs, another way?

[cokewithvanilla]

Hi, I am trying to get a simple sheet to work on .xls format. No problems with xlsx, only it doesn't work on the computers at work.

I have 4 cells that need to be checked for a certain letter (1 of 13). If the cell contains any of these letters, I need to have the amounts added for cells that correspond to these letters. The cells that correspond are different each day, and are linked to another sheet.

To do this, I've used a huge If statement. I am sure there is a better way, only I do not know it.

This is my statement:
=IF(E7="A",batch1A,IF(E7="B",batch1B,IF(E7="C",batch1C,IF(E7="D",batch1D,IF(E7="E",batch1E,
IF(E7="G",batch1G,IF(E7="H",batch1H,IF(E7="J",batch1J,IF(E7="K",batch1K,IF(E7="L",batch1L,
IF(E7="M",batch1M,IF(E7="S",batch1S,IF(E7="T",batch1T,0)))))))))))))+IF(F7="A",batch1A,
IF(F7="B",batch1B,IF(F7="C",batch1C,IF(F7="D",batch1D,IF(F7="E",batch1E,IF(F7="G",batch1G,
IF(F7="H",batch1H,IF(F7="J",batch1J,IF(F7="K",batch1K,IF(F7="L",batch1L,IF(F7="M",batch1M,
IF(F7="S",batch1S,IF(F7="T",batch1T,0)))))))))))))+IF(G7="A",batch1A,IF(G7="B",batch1B,
IF(G7="C",batch1C,IF(G7="D",batch1D,IF(G7="E",batch1E,IF(G7="G",batch1G,IF(G7="H",batch1H,
IF(G7="J",batch1J,IF(G7="K",batch1K,IF(G7="L",batch1L,IF(G7="M",batch1M,IF(G7="S",batch1S,
IF(G7="T",batch1T,0)))))))))))))+IF(H7="A",batch1A,IF(H7="B",batch1B,IF(H7="C",batch1C,
IF(H7="D",batch1D,IF(H7="E",batch1E,IF(H7="G",batch1G,IF(H7="H",batch1H,IF(H7="J",batch1J,
IF(H7="K",batch1K,IF(H7="L",batch1L,IF(H7="M",batch1M,IF(H7="S",batch1S,IF(H7="T",batch1T,0)))))))))))))

Anyone know how to make this work in excel 2002?

 

[vaskov17]

=INDIRECT("batch1" & E7)+INDIRECT("batch1" & F7)+INDIRECT("batch1" & G7)+INDIRECT("batch1" & H7)

 

[pgc01]

Hi

Try:

=SUM(IF(ISNUMBER(SEARCH(E7:H7,"ABCDEGHJKLMST")),N(INDIRECT("batch1"&E7:H7)),0))

This in an array formula, you have to confirm it with CTRL-SHIFT-E
NTER

 

[cokewithvanilla]

=INDIRECT("batch1" & E7)+INDIRECT("batch1" & F7)+INDIRECT("batch1" & G7)+INDIRECT("batch1" & H7)

Doing :=INDIRECT("B" & E5)+... works, only in the column that I enter the letters, I have to enter the number of the corresponding cell. For example, if i would want to enter A, I would have to enter 5 because it's in cell B5.

 

[cokewithvanilla]

Hi

Try:

=SUM(IF(ISNUMBER(SEARCH(E7:H7,"ABCDEGHJKLMST")),N(INDIRECT("batch1"&E7:H7)),0))

This in an array formula, you have to confirm it with CTRL-SHIFT-E
NTER

This one is not working at all. Simply Registers 0. I have numbers in B, Letters that correspond to the numbers in no given fashion in EFGH. For example... B8: 38, E5 = D, F5,G5,H5 are blank, so I5 should return 38. I have to have a way to tell excel that Cell B8 needs to come up with the letter D

 

[pgc01]

Hi

That is an array formula. Are you sure you confirmed it with CTRL-SHIFT-ENTER as I wrote?

 

[cokewithvanilla]

Hi

That is an array formula. Are you sure you confirmed it with CTRL-SHIFT-ENTER as I wrote?

I've never had to use it like that, I just pasted the text in and hit CTRL-SHIFT-ENTER, is this correct?

 

[wigi]

I've never had to use it like that, I just pasted the text in and hit CTRL-SHIFT-ENTER, is this correct?

You should select the target cell, then hit F2 (or click in the formula bar), paste the code, then hit Ctrl-Shift-Enter.

 

[cokewithvanilla]

You should select the target cell, then hit F2 (or click in the formula bar), paste the code, then hit Ctrl-Shift-Enter.

yep, that's what I did. I'm using Excel 2010 for making this, if that matters

 

[wigi]

Then pgc01 will revert and look at his/her suggestion, I'm sure.