spreadsheet calc

  • Thread starter Thread starter joe
  • Start date Start date
J

joe

I am trying to write an equation that will search the
contents of 4 cells and return the value of the only cell
with data. Any ideas on how to do that?
 
Here are two suggestions. The first only works if the data is numeric.
The second works in all cases. I am assuming that the four cells with
data have the following range names: "Cell1", "Cell2", "Cell3", and
"Cell4".

First case:
=Cell1*(Cell1<>"")+Cell2*(Cell2<>"")+Cell3*(Cell3<>"")+Cell4*(Cell4<>"")

This uses arithmetic logic as a trick. For example, if Cell1 has a
numeric value then the part (Cell1<>"") has a value of 1. Multiply this
by Cell1 and you get the value of Cell1. The problem with this formula
is that you must make sure that three cells are empty. Otherwise, the
different values get added together. This is an elegant formula, if it
matches you have no problems with the limitations.

Second case:
=IF(Cell1<>"",Cell1,IF(Cell2<>"",Cell2,IF(Cell3<>"",Cell3,IF(Cell4<>"",Cell4))))

This is the brute force method, as I see it. It works for numeric and
string data. If more than one cell has data, the first one to be looked
at in the formula will be chosen.
 
sStr = Trim(Range("A1") & Range("A2") & Range("A3") & Range("A4"))
msgbox sStr

or

for each cell in Range("A1:A4")
if not isempty(cell) then
sStr = cell.Value
exit for
end if
Next
msgb sStr
 
Back
Top