Cell Format Locked When Using IF THEN Formula

[essential]

Hello,

I'm using an IF/THEN formula, which is:

=IF(BK6=6836,$O6,"")&IF(BK6=7380,$P6,"")&IF(BK6=7421,$Q6,"")&IF(BK6=7999,$R6,"")&IF(BK6=8006,$S6,"")&IF(BK6=8017,$T6,"")&IF(BK6=8385,$U6,"")&IF(BK6=8391,$V6,"")&IF(BK6=8392,$W6,"")&IF(BK6=8394,$X6,"")&IF(BK6=8720,$Y6,"")&IF(BK6=8742,$Z6,"")&IF(BK6=8748,$AA6,"")&IF(BK6=8809,$AB6,"")&IF(BK6=8810,$AC6,"")&IF(BK6=8871,$AD6,"")&IF(BK6=9026,$AE6,"")&IF(BK6=9029,$AF6,"")&IF(BK6=9072,$AG6,"")

This formula is working correctly, however, the TRUE values (O5, P5, Q5, etc.) are dollar values, for example $1,110,704, $387,089, $78,416, etc.

Now, the formula is pulling the correct values, but it's loosing it's $ formatting. All the numbers show up as 1110704, 387089, 78416, etc.

Also, when I right click on the cell and select "Format Cells" none of the options make a difference. It is important that it retain the $ format because I am merging the info from this sheet into a word document, and these values need to show up properly formatted as currency.

One last strange thing, the numbers produced by the formula won't add up. For example if you do a row sum of the numbers this formula produces, the value is 0, almost like it's not seeing numerical values in the cell.

I'm posting an example of the situation with this so you can see what I mean.

Anyone have any ideas?

Thanks.

[darkyam]

Replace IF(BK6=6836,$O6,"") with =IF(BK6=6836,"$"&Text($O6,"###,###,###"),"") and so on.

[shg]

Why not put the codes (6386, 7380, ...) in A1:S1, and then in AA2 and copy down and across,

=IF(ISNA(MATCH(T2, $A$1:$S$1)), "", INDEX($A2:$S2, MATCH(T2, $A$1:$S$1)))

[essential]

Replace IF(BK6=6836,$O6,"") with =IF(BK6=6836,"$"&Text($O6,"###,###,###"),"") and so on.

This worked, just two questions:

1) How would you control how many decimal places the dollar value is show? For example, if I wanted $100.47 as opposed to $100?

2) When I value/sum it still equals $0. Is that because of the formula? Even showing as $values now, it still won't sum.

Why not put the codes (6386, 7380, ...) in A1:S1, and then in AA2 and copy down and across,

=IF(ISNA(MATCH(T2, $A$1:$S$1)), "", INDEX($A2:$S2, MATCH(T2, $A$1:$S$1)))

Honestly, because I'm not familiar with that. Does it make a difference if the input values (6386, 7380) are different on every row? Those are the only values that have to be manually inputted on each row, and then i'm trying to get the rest calculate itself. I think your method might require a fixed row with all input values?

Thanks.

[darkyam]

For mine, just modify Text($O6,"###,###,###") to be Text($O6,"###,###,###.##"). If you want the decimals to show regardless of whether the number is an even dollar amount, then use Text($O6,"###,###,###.00").

[shg]

You're catenating the results, which creates a text result.

[ChemistB]

shg's method seems to be the most efficient but if you want to stick with your IF statements, rather than change everything to TEXT..... as darkyam suggested, place parenthesis around your entire equation (not including the =) and then multiply by 1. This will convert your values to number values and you can format them as currency and sum them. e.g.

Please Login or Register  to view this content.

Does that work for you?

[essential]

shg's method seems to be the most efficient but if you want to stick with your IF statements, rather than change everything to TEXT..... as darkyam suggested, place parenthesis around your entire equation (not including the =) and then multiply by 1. This will convert your values to number values and you can format them as currency and sum them. e.g.

Please Login or Register  to view this content.

Does that work for you?

That does work, the only issue is, with the *1, when there is a blank value, it doesn't show up blank anymore, it shows up with a #VALUE! error. I'm still trying things, but you guys have been a great help. Looks like I can't have this one both ways, using IF THENS and being able to keep numbers.

[shg]

In case you change your mind, the formula should be changed to require an exact match:

=IF(ISNA(MATCH(T2, $A$1:$S$1, 0)), "", INDEX($A2:$S2, MATCH(T2, $A$1:$S$1, 0)))

[essential]

In case you change your mind, the formula should be changed to require an exact match:

=IF(ISNA(MATCH(T2, $A$1:$S$1, 0)), "", INDEX($A2:$S2, MATCH(T2, $A$1:$S$1, 0)))

Yes, I am using yours now shg and it works. I tend to like to use formulars i'm familiar with because incase I have a problem later I can work though them, but in this case, yours works and mine doesn't so i'm using yours, even though i'm not really sure what it means.

I guess it's only important that it does.

Thanks!

[shg]

It's pretty straightforward;

MATCH returns an index to where a match is found, or NA if none is found. The last argument (0) requires an exact match.

INDEX returns a specified value from a range given the row and column indices. When the range is, as here, a vector (a single row or a single column), only one index is needed.

I'd be very surprised if five minutes looking at Help for the functions doesn't ramp you completely up on the formula. INDEX/MATCH is one of the most useful and commonly-used formula structures in Excel. It's a good one to have in your bag.