Formula is working on Text but not on Numbers

**Albertmb** · 10-08-2011, 04:06 PM

Hi All,

My workbook is working on 3 Sheets

In Sheet named '1' I enter information

In Sheet named 'Statement info' I gather information from Sheet '1' by using the following formula:
IF($A$6<>'1'!C7,"",IF('1'!L7="P","",IF('1'!C7="","",IF('Statement Infoold'!$A$6='1'!C7,'1'!D7))))

In Sheet named 'Statement' I collect info from Sheet 'Statement Info' and by using the following formaula in Column A:

IF(OR('Statement Info'!A8="",'Statement Info'!A8),"",ROW())

and this formula in Column B:

IF(ROWS($7:7)>COUNT($A:$A),"",INDEX('Statement Infoold'!A:A,SMALL($A:$A,ROWS($7:7))))

I ommit empty cells from Sheet 'Statement Info'

My problem is that this formula is working when cells in sheet 'Statement Info' contain Text and fails when cells contain numbers.

Can anyone help please.

Thanks
Albert

**daddylonglegs** · 10-08-2011, 04:12 PM

COUNT only counts numbers, so that last formula won't work as you want - change COUNT to COUNTA, that function counts any populated cell (numeric or text)

**Albertmb** · 10-08-2011, 04:22 PM

[QUOTE=daddylonglegs;2615735]COUNT only counts numbers, so that last formula won't work as you want - change COUNT to COUNTA, that function counts any populated cell (numeric or text)[/Q

Thank you ever so much for your kind reply, but unfortunately it still did not work as a result I got: #NUM!

**daddylonglegs** · 10-08-2011, 05:11 PM

OK, sorry, I was jumping to the wrong conclusion.....

I think the problem is with this formula

=IF(OR('Statement Info'!A8="",'Statement Info'!A8),"",ROW())

what are you trying to do with that? The second part of the OR effectively excludes numbers, if you want all populated cells try changing that formula to this

IF(('Statement Info'!A8<>"",ROW(),"")

**Albertmb** · 10-08-2011, 05:35 PM

Thanks Again

This formula ommits empty cells so my final report will skip empty cells and have cells that contain text or numbers stacked below each other.

Unfortunately when I enter the formula you gave me I get a notification that there is something wrong with the formula