Substituting cells formula???

**daddylonglegs** · 04-25-2010, 10:55 AM

What do you mean "....does not work with the second cell"? If N8 has a number and L8 doesn't you want to show the number in N8, are you saying that doesn't work with your data?

It works for me. I put ratcat's formula in O8, left L8 blank and then put 10 in N8, O8 shows 10, isn't that what you require?

**olisim** · 04-25-2010, 11:22 AM

I thought I had it. I tried reversing the formula:

=IF(ISNUMBER(N8),N8,IF(ISNUMBER(L8),L8,0))

In this scenario it shows N8, but if the cell is empty it does not show L8

=IF(ISNUMBER(L8),L8,IF(ISNUMBER(N8),N8,0))

In this scenario it shows L8, but if the cell is empty it does not show N8

Maybe I'm missing something here.

Thanks again for the help... Oh, I'm using Excel 2007, just in case.

OS

**olisim** · 04-25-2010, 11:29 AM

Both cells L8 and N8 are simplistic formulas.

Would that be causing the issue?

Thanks,

OS

**daddylonglegs** · 04-25-2010, 11:36 AM

What formulas do you have in those cells? Functions like MID, LEFT, RIGHT and others always return a text result (even if it looks like a number) so the ISNUMBER part of Ratcat's formula might not recognise a number as you might expect. You could probably alter L8 and N8 to return genuine numbers.......or try changing to

=IF(ISNUMBER(L8+0),L8+0,IF(ISNUMBER(N8+0),N8+0,0))

**olisim** · 04-25-2010, 11:46 AM

Tried that as well, and still won't show N8

Here is what makes those cells populate:

L8 is calculated with =SUM(H8)*K8

N8 is calculated with =SUM(J8)*M8

Both L8 and N8 are currency fields, if that makes a difference.

I think I'm just missing the logic here, or I haven't had enough coffee.

Thanks again...

OS

**daddylonglegs** · 04-25-2010, 11:55 AM

Can you give a concrete example where the formula doesn't work as you want - what's in L8, what's in N8 and what do you want the formula to return.....and what does it return?

**olisim** · 04-25-2010, 12:01 PM

L8 and N8 are totals for a construction process. Let's say L8 is option 1 and N8 is option 2.

If L8 has a number, that number needs to be shown in P8 (total for that option).

If L8 does not have a number, I need the formula to check N8. If that has a number, it needs to be shown in P8.

Right now, with the formula given, if L8 has a number, it is shown in P8

If L8 does not have a number, but there is a number in N8, P8 still show 0

Does this make sense?

OS