| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find first value in column with formulas
Columns D & H contain an IF formula.
Column D Formula =IF(C2="Diners",B2,"") Column H Formula =IF(C2="Westpac",B2,"") The first row containing a dollar value in either of columns D & H could be two or ten rows down. How do I program to locate the first cell that contains a dollar value skipping over "empty" cells containing the formula only. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP SP3 Office 2007 on Mini Mac using VMware. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find first value in column with formulas
Bob
Your formula appears to count the "empty" Cells down too, but not including the first dollar value. I'm after the first dollar value in the column. It should of returned a $169 value but showed the number 11 which is the same number of "empty" rows before the $169 value. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. "Bob Phillips" wrote: Try this array formula =MATCH(1,--(LEN(D2:D20)0),0) -- __________________________________ HTH Bob "Aussie Bob C" wrote in message ... Columns D & H contain an IF formula. Column D Formula =IF(C2="Diners",B2,"") Column H Formula =IF(C2="Westpac",B2,"") The first row containing a dollar value in either of columns D & H could be two or ten rows down. How do I program to locate the first cell that contains a dollar value skipping over "empty" cells containing the formula only. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP SP3 Office 2007 on Mini Mac using VMware. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find first value in column with formulas
Try this array-entered** formula then...
=INDEX(D1:D20,MATCH(1,--(LEN(D1:D20)0),0)) **Commit formula using Ctrl+Shift+Enter, not just Enter by itself -- Rick (MVP - Excel) "Aussie Bob C" wrote in message ... Bob Your formula appears to count the "empty" Cells down too, but not including the first dollar value. I'm after the first dollar value in the column. It should of returned a $169 value but showed the number 11 which is the same number of "empty" rows before the $169 value. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. "Bob Phillips" wrote: Try this array formula =MATCH(1,--(LEN(D2:D20)0),0) -- __________________________________ HTH Bob "Aussie Bob C" wrote in message ... Columns D & H contain an IF formula. Column D Formula =IF(C2="Diners",B2,"") Column H Formula =IF(C2="Westpac",B2,"") The first row containing a dollar value in either of columns D & H could be two or ten rows down. How do I program to locate the first cell that contains a dollar value skipping over "empty" cells containing the formula only. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP SP3 Office 2007 on Mini Mac using VMware. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find first value in column with formulas
That did the trick.
-- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. "Rick Rothstein" wrote: Try this array-entered** formula then... =INDEX(D1:D20,MATCH(1,--(LEN(D1:D20)0),0)) **Commit formula using Ctrl+Shift+Enter, not just Enter by itself -- Rick (MVP - Excel) "Aussie Bob C" wrote in message ... Bob Your formula appears to count the "empty" Cells down too, but not including the first dollar value. I'm after the first dollar value in the column. It should of returned a $169 value but showed the number 11 which is the same number of "empty" rows before the $169 value. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. "Bob Phillips" wrote: Try this array formula =MATCH(1,--(LEN(D2:D20)0),0) -- __________________________________ HTH Bob "Aussie Bob C" wrote in message ... Columns D & H contain an IF formula. Column D Formula =IF(C2="Diners",B2,"") Column H Formula =IF(C2="Westpac",B2,"") The first row containing a dollar value in either of columns D & H could be two or ten rows down. How do I program to locate the first cell that contains a dollar value skipping over "empty" cells containing the formula only. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP SP3 Office 2007 on Mini Mac using VMware. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
| Find first value in column with formulas | Excel Programming | |||
| Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
| Find last column, move formulas over, copy/paste data | Excel Worksheet Functions | |||
| Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Linear Mode
