| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Funciton to Lookup First Post in a Column
I have a worksheet in which I want to write a formula that will look in a
column of 5 rows and return to me the first date that is posted in that column. My column of cells consist of Cells C12, C13, C14, C15, and C16. This column is a date column. I want to write a formula in Cell A3 that tells Excel to look in C12 and return the date in C12 to A3, if one exists there. If it does not, then I want it to look on down to C13 and return that date, but if there is no date there, then look in C14 and return that one, etc. all the way down to C16 (which will ALWAYS contain a date). Basically, I want Excel to look in my range of C12:C16 and return to me the very first cell that is not blank Is there anyway to do this? I've tried several different formulas (even using VLOOKUP) and I'm not having any luck. Thanks, -- Sherry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Funciton to Lookup First Post in a Column
Hi,
You can use the following *array* formula (commit with Shift+Ctrl+Enter) =IF(SUM(C12:C16),INDEX(C12:C16,MIN(IF(C12:C16<"", ROW(C12:C16)-ROW(C12)+1))),"") HTH Kostis Vezerides Ladypep wrote: I have a worksheet in which I want to write a formula that will look in a column of 5 rows and return to me the first date that is posted in that column. My column of cells consist of Cells C12, C13, C14, C15, and C16. This column is a date column. I want to write a formula in Cell A3 that tells Excel to look in C12 and return the date in C12 to A3, if one exists there. If it does not, then I want it to look on down to C13 and return that date, but if there is no date there, then look in C14 and return that one, etc. all the way down to C16 (which will ALWAYS contain a date). Basically, I want Excel to look in my range of C12:C16 and return to me the very first cell that is not blank Is there anyway to do this? I've tried several different formulas (even using VLOOKUP) and I'm not having any luck. Thanks, -- Sherry |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Funciton to Lookup First Post in a Column
Thanks for your help, although this did not work. It returns #VALUE! error
though I cut and pasted your formula into my cell. Hmmm...I'm at a loss. Thanks again. -- Sherry "vezerid" wrote: Hi, You can use the following *array* formula (commit with Shift+Ctrl+Enter) =IF(SUM(C12:C16),INDEX(C12:C16,MIN(IF(C12:C16<"", ROW(C12:C16)-ROW(C12)+1))),"") HTH Kostis Vezerides Ladypep wrote: I have a worksheet in which I want to write a formula that will look in a column of 5 rows and return to me the first date that is posted in that column. My column of cells consist of Cells C12, C13, C14, C15, and C16. This column is a date column. I want to write a formula in Cell A3 that tells Excel to look in C12 and return the date in C12 to A3, if one exists there. If it does not, then I want it to look on down to C13 and return that date, but if there is no date there, then look in C14 and return that one, etc. all the way down to C16 (which will ALWAYS contain a date). Basically, I want Excel to look in my range of C12:C16 and return to me the very first cell that is not blank Is there anyway to do this? I've tried several different formulas (even using VLOOKUP) and I'm not having any luck. Thanks, -- Sherry |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Funciton to Lookup First Post in a Column
It worked fine for me, so long as my dates were entered as date values, not
as text that looked like a date. "Ladypep" wrote: Thanks for your help, although this did not work. It returns #VALUE! error though I cut and pasted your formula into my cell. Hmmm...I'm at a loss. Thanks again. -- Sherry "vezerid" wrote: Hi, You can use the following *array* formula (commit with Shift+Ctrl+Enter) =IF(SUM(C12:C16),INDEX(C12:C16,MIN(IF(C12:C16<"", ROW(C12:C16)-ROW(C12)+1))),"") HTH Kostis Vezerides Ladypep wrote: I have a worksheet in which I want to write a formula that will look in a column of 5 rows and return to me the first date that is posted in that column. My column of cells consist of Cells C12, C13, C14, C15, and C16. This column is a date column. I want to write a formula in Cell A3 that tells Excel to look in C12 and return the date in C12 to A3, if one exists there. If it does not, then I want it to look on down to C13 and return that date, but if there is no date there, then look in C14 and return that one, etc. all the way down to C16 (which will ALWAYS contain a date). Basically, I want Excel to look in my range of C12:C16 and return to me the very first cell that is not blank Is there anyway to do this? I've tried several different formulas (even using VLOOKUP) and I'm not having any luck. Thanks, -- Sherry |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Funciton to Lookup First Post in a Column
As Duke says, the test for emptiness might be the problem. Replace the
formula with: =IF(COUNTA(C12:C16),INDEX(C12:C16,MIN(IF(C12:C16< "",ROW(C12:C16)-ROW(C12)+1))),"") HTH Kostis Vezerides Ladypep wrote: Thanks for your help, although this did not work. It returns #VALUE! error though I cut and pasted your formula into my cell. Hmmm...I'm at a loss. Thanks again. -- Sherry "vezerid" wrote: Hi, You can use the following *array* formula (commit with Shift+Ctrl+Enter) =IF(SUM(C12:C16),INDEX(C12:C16,MIN(IF(C12:C16<"", ROW(C12:C16)-ROW(C12)+1))),"") HTH Kostis Vezerides Ladypep wrote: I have a worksheet in which I want to write a formula that will look in a column of 5 rows and return to me the first date that is posted in that column. My column of cells consist of Cells C12, C13, C14, C15, and C16. This column is a date column. I want to write a formula in Cell A3 that tells Excel to look in C12 and return the date in C12 to A3, if one exists there. If it does not, then I want it to look on down to C13 and return that date, but if there is no date there, then look in C14 and return that one, etc. all the way down to C16 (which will ALWAYS contain a date). Basically, I want Excel to look in my range of C12:C16 and return to me the very first cell that is not blank Is there anyway to do this? I've tried several different formulas (even using VLOOKUP) and I'm not having any luck. Thanks, -- Sherry |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Funciton to Lookup First Post in a Column
Thanks to both of you. I got it to work this time.
-- Sherry "Ladypep" wrote: I have a worksheet in which I want to write a formula that will look in a column of 5 rows and return to me the first date that is posted in that column. My column of cells consist of Cells C12, C13, C14, C15, and C16. This column is a date column. I want to write a formula in Cell A3 that tells Excel to look in C12 and return the date in C12 to A3, if one exists there. If it does not, then I want it to look on down to C13 and return that date, but if there is no date there, then look in C14 and return that one, etc. all the way down to C16 (which will ALWAYS contain a date). Basically, I want Excel to look in my range of C12:C16 and return to me the very first cell that is not blank Is there anyway to do this? I've tried several different formulas (even using VLOOKUP) and I'm not having any luck. Thanks, -- Sherry |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro | Excel Discussion (Misc queries) | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Lookup Table Dilemma | Excel Worksheet Functions | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Linear Mode
