Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List the one nonempty cell
All of column B is empty except one cell. The non-empty cell is in a
different place each week. I want a formula that will give the text in the nonempty cell, regardless of which cell is nonempty at that point in time. For example. Week 4 Cell should say "Text 4" A B 1 2 3 4 Text 4 Week 2 Cell should say "Text 2" A B 1 2 Text 2 3 4 Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List the one nonempty cell
Hi Adam
Maybe: =LOOKUP(REPT("z",255),B:B) Hope this helps! Richard Adam Graham wrote: All of column B is empty except one cell. The non-empty cell is in a different place each week. I want a formula that will give the text in the nonempty cell, regardless of which cell is nonempty at that point in time. For example. Week 4 Cell should say "Text 4" A B 1 2 3 4 Text 4 Week 2 Cell should say "Text 2" A B 1 2 Text 2 3 4 Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
List the one nonempty cell
Another one:
=INDEX(B:B,MATCH("*",B:B,0)) Biff "Adam Graham" <Adam wrote in message ... All of column B is empty except one cell. The non-empty cell is in a different place each week. I want a formula that will give the text in the nonempty cell, regardless of which cell is nonempty at that point in time. For example. Week 4 Cell should say "Text 4" A B 1 2 3 4 Text 4 Week 2 Cell should say "Text 2" A B 1 2 Text 2 3 4 Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
List the one nonempty cell
works!
thanks "RichardSchollar" wrote: Hi Adam Maybe: =LOOKUP(REPT("z",255),B:B) Hope this helps! Richard Adam Graham wrote: All of column B is empty except one cell. The non-empty cell is in a different place each week. I want a formula that will give the text in the nonempty cell, regardless of which cell is nonempty at that point in time. For example. Week 4 Cell should say "Text 4" A B 1 2 3 4 Text 4 Week 2 Cell should say "Text 2" A B 1 2 Text 2 3 4 Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
List the one nonempty cell
I was wrong, these don't work. They do, if the cells in column B are imputed
manually, but in my case they are controlled by an 'IF' formula, where truth is indicated by text and falseness is indicated by "", to create a blank cell. Both these formulas interperate "" as text. I may need to find a new way to do this altogether, unless someone knows of a way to manipulate either of these formulas to exclude the blank cells, in other words, list the only cell that is not "". A formula that would work would also do the following: case 1: cell should say "Text 3" A false false Text 3 false case 2: cell should say "Text 2" A false Text 2 false false Thanks for your help, though, in truth you did answer my question! "T. Valko" wrote: Another one: =INDEX(B:B,MATCH("*",B:B,0)) Biff "Adam Graham" <Adam wrote in message ... All of column B is empty except one cell. The non-empty cell is in a different place each week. I want a formula that will give the text in the nonempty cell, regardless of which cell is nonempty at that point in time. For example. Week 4 Cell should say "Text 4" A B 1 2 3 4 Text 4 Week 2 Cell should say "Text 2" A B 1 2 Text 2 3 4 Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
List the one nonempty cell
Adam
Try this variant: =LOOKUP(2,1/(A1:A65535<""),A1:A65535) Note that this formula doesn't use whole column refs (it is 1 row less). Best regards Richard Adam Graham wrote: I was wrong, these don't work. They do, if the cells in column B are imputed manually, but in my case they are controlled by an 'IF' formula, where truth is indicated by text and falseness is indicated by "", to create a blank cell. Both these formulas interperate "" as text. I may need to find a new way to do this altogether, unless someone knows of a way to manipulate either of these formulas to exclude the blank cells, in other words, list the only cell that is not "". A formula that would work would also do the following: case 1: cell should say "Text 3" A false false Text 3 false case 2: cell should say "Text 2" A false Text 2 false false Thanks for your help, though, in truth you did answer my question! "T. Valko" wrote: Another one: =INDEX(B:B,MATCH("*",B:B,0)) Biff "Adam Graham" <Adam wrote in message ... All of column B is empty except one cell. The non-empty cell is in a different place each week. I want a formula that will give the text in the nonempty cell, regardless of which cell is nonempty at that point in time. For example. Week 4 Cell should say "Text 4" A B 1 2 3 4 Text 4 Week 2 Cell should say "Text 2" A B 1 2 Text 2 3 4 Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
List the one nonempty cell
works like a charm!
thanks, Richard "RichardSchollar" wrote: Adam Try this variant: =LOOKUP(2,1/(A1:A65535<""),A1:A65535) Note that this formula doesn't use whole column refs (it is 1 row less). Best regards Richard Adam Graham wrote: I was wrong, these don't work. They do, if the cells in column B are imputed manually, but in my case they are controlled by an 'IF' formula, where truth is indicated by text and falseness is indicated by "", to create a blank cell. Both these formulas interperate "" as text. I may need to find a new way to do this altogether, unless someone knows of a way to manipulate either of these formulas to exclude the blank cells, in other words, list the only cell that is not "". A formula that would work would also do the following: case 1: cell should say "Text 3" A false false Text 3 false case 2: cell should say "Text 2" A false Text 2 false false Thanks for your help, though, in truth you did answer my question! "T. Valko" wrote: Another one: =INDEX(B:B,MATCH("*",B:B,0)) Biff "Adam Graham" <Adam wrote in message ... All of column B is empty except one cell. The non-empty cell is in a different place each week. I want a formula that will give the text in the nonempty cell, regardless of which cell is nonempty at that point in time. For example. Week 4 Cell should say "Text 4" A B 1 2 3 4 Text 4 Week 2 Cell should say "Text 2" A B 1 2 Text 2 3 4 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop-Down List Choice Affects Multiple Cells? | Excel Worksheet Functions | |||
auto updating list | Excel Worksheet Functions | |||
change size of in cell drop down list | Excel Discussion (Misc queries) | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions | |||
Can a cell have a drop down list and can also be auto populated | Excel Worksheet Functions |