Number Values Converted to text, multiple ranges.

**Whistler** · 03-09-2009, 12:02 PM

I appologize if this is simple, and I appreciate any assitance with this.

I have attached an example sheet to help visually explain what I am going to try and articulate.

Cell A2 and B2's values result in cell E2, Cell C2 and D2's values result in cell F2.

The same is true for row 3.

So what I would like to do is, based on the predetermined ranges of the Height+Chest Columns, auto complete column E, and Columns C+D, auto complete column F.

My problem is that I cannot simply cell A + B = E, because the maximum range of one layers the minumum range of another.
An example would be Maximum of A+B is 101 to result in SXS in Columm C, but the minmum A+B to get a SS in column C would be 97.

So what I need is, for another specific example:

If cell A2 is between 57 and 65, and cell B2 is between 31 and 36, then Cell C2 must be SXS.
If Cell A2 is between 66 and 68, and Cell B2 is between 31 and 36, then Cell C2 must be SS.

I hope that isnt to confusing of an explanation although I see how it could be.

Again, I appreciate any assistance with this!

**NBVC** · 03-09-2009, 12:18 PM

You would need to set up some side table to refer to.. see attached for an example...

list the size ranges showing only the lower bounds for each size and then insert the appropriate codes at the intersect points.

Then see C2 for an example formula that would be applied to get the right code.

**Whistler** · 03-09-2009, 04:18 PM

That's very helpful! Thank you. Now is there a way I can put the side chart on a different sheet and get the same result? I.E. direct the formula to the second sheet?

**NBVC** · 03-09-2009, 04:33 PM

Yes just add the sheet name within single quotes followed by an exclamation point (!) before any reference to that other sheet.

e.g. ='Sheet 2'!A1:A100

See attached for the previous sample with table moved to another sheet... see formula in C2 for changes...

**Whistler** · 03-09-2009, 04:39 PM

If only I could buy you a drink good sir.

**NBVC** · 03-09-2009, 04:43 PM

No problem...glad you're happy with the solution.

BTW, clicking on the blue scales and adding to my rep is ExcelForum's way of "buying someone a drink"

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save