Problems comparing data from multiple blank cells

[monkeyhop]

Problem:

I have 4 worksheets in the same workbook: sheet1 sheet2 sheet3 sheet4
I want to get cell B10 of sheet4 to get info from cells B5 of sheets 1,2, & 3
BUT: Only one of those 3 cells will have something in it to print to B10,
Else if none of those cells has anything then leave it blank in B10
The cells are formatted in Time: 5:00 pm and such

I've been trying to get a formula to work trying different methods and I get these results: #value, 12:00 am, etc
I can get it to print any cell that has something but if none of those cells has anything it prints #value, 12:00 am

Thanks in advance,
monkeyhop

[Clivey_UK]

Monkeyhop,
It sounds like you need an IF formula. In B10 of Sheet4, use something like this:
=IF(Sheet1!B5="","",Sheet1!B5)
which says if B5 is blank, then return blank, Else show the result of B5.
However you will need to change it to take account of B5 in the other sheets. But I'm not sure what as you haven't said what you mean by 'get info', e.g. Sum, Count, etc.
Hope this helps.
Clive

[monkeyhop]

Clivey_UK:

I tried that already. That only helps if Im trying to get data from one cell.
So then I tried Nesting that formula like:

=IF(Sheet1!B5="","",Sheet1!B5, IF(Sheet2!B5="","",Sheet2!B5, IF(Sheet3!B5="","",Sheet3!B5)))

I get too many arguments error

Also tried:

=SUM(Sheet1!B5 & Sheet2!B5 & Sheet1!B5)

That also works if there is a value in any of those 3 cells but if there isn't it returns value of #VALUE!

I want to make a formula to do this:

IF Sheet1!B5 has data then enter that data to Sheet4!B10
elseif Sheet2!B5 has data then enter that data to Sheet4!B10
elseif Sheet3!B5 has data then enter that data to Sheet4!B10
else output CHAR(160) to Sheet4!B10


Thanks again in advance
monkeyhop

[Clivey_UK]

I think this should do it. Note that if more than one of the B5 has data, Char(160) will be returned. But if all B5's are blank, then you'll get Char(160), and if any one of them has data, you'll get that data.

=IF(ISERROR(SUM(Sheet1!B5 & Sheet2!B5 & Sheet3!B5)),CHAR(160),SUM(Sheet1!B5 & Sheet2!B5 & Sheet3!B5))

Clivey_UK:

I want to make a formula to do this:

IF Sheet1!B5 has data then enter that data to Sheet4!B10
elseif Sheet2!B5 has data then enter that data to Sheet4!B10
elseif Sheet3!B5 has data then enter that data to Sheet4!B10
else output CHAR(160) to Sheet4!B10


Thanks again in advance
monkeyhop

[monkeyhop]

Clive,

Thanks buddy that worked like a charm, sorry it took me so long to respond but I had to get some sleep

monkeyhop