If text mathces column 1 range, display value in column 2 cell

**shg** · 05-03-2008, 12:15 PM

B1: =IF(AND(A3:A11 = ""), 0, IF(AND(A3:A11 = "DME"), 1, IF(AND(A3:A11 = "O&M"), 0, 0.5) ) )

C1: =IF(AND(A3:A11 = ""), 0, IF(AND(A3:A11 = "O&M"), 1, IF(AND(A3:A11 = "DME"), 0, 0.5) ) )

Both of these are array formulas; they MUST be confirmed with Ctrl+Shift+Enter, not Enter. You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

**brillison** · 05-03-2008, 12:36 PM

Using the formula format provided and ensuring I press Ctrl-Shift-Enter, I am getting an error.

My information was an example scenario - would the fact that my real data is accross spreadsheets cause the error?

Here is what I have:

=IF(AND('Worksheet1'!I39:I98="",0,IF(AND('Worksheet1'!I39:I98="DME"),1,IF(AND('Worksheet1'!I39:I98="O&M",0,0.5)))

So comparing to my example:
'Worksheet1'!I39:I98 = A1
'Worksheet2'!I2 = B1
'Worksheet3'! = C1

Another concern: for the range I39:I98, every other line will never have a value because they are used for styling purposes. Does this ruin the function?

**shg** · 05-03-2008, 02:13 PM

would the fact that my real data is accross spreadsheets cause the error?

No. Can you post a workbook?

... every other line will never have a value because they are used for styling purposes. Does this ruin the function?

Yes, it does.

**brillison** · 05-03-2008, 03:59 PM

Please find the worksheet attached. Areas of interest are highlighted in BRIGHT ORANGE.

'GeneralInformation'!I39:I98 is the range with the option of DME or O&M

Part 1: If range 'GeneralInformation'!I39:I98 is all "DME" and some blank, then I want 'DME'!H28 to contain the full value of cell 'GeneralInformation'!I24

Part2: If the range 'GeneralInformation'!I39:I98 contains "DME" AND "O&M" and blank, then I want 'DME'!H28 to contain .5(half) the value of cell 'GeneralInformation'!I24'. The other .5(half) will then display in 'O&M'!H28.

Likewise, If range 'GeneralInformation'!I39:I98 is all "O&M" and some blank, then I want 'O&M'!H28 to contain the full value of cell 'GeneralInformation'!I24. Then follows the logic of Part 2 above.