Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
spreadsheet calc
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
spreadsheet calc
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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
spreadsheet calc
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 -- Regards, Tom Ogilvy "joe" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does my spreadsheet re-calc so slowly ? | Excel Discussion (Misc queries) | |||
Calc = Manual & Do Not Calc b/4 SAVE | Excel Discussion (Misc queries) | |||
How come the spreadsheet says calc when it is set to autocalc | Excel Discussion (Misc queries) | |||
Why does my spreadsheet calc slower when many other windows are op | Excel Discussion (Misc queries) | |||
auto calc on, but have to edit (f2) cells to force re-calc..help! | Excel Worksheet Functions |