Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
hi
i am trying to search over a range only as far as the first empty cell eg row 274 = COUNTIF(BH2:BH134,AT2) what i would like to do is use the value of the first empty cell so the search would be BH2: "First empty cell",AT2 the formula below returns the row number of the last row with data in it =(MATCH("EMPTY",INDIRECT("BA1:BA"&COUNTIF(BA:BA,"< ")+1)&"EMPTY", 0)-1)) ie 250 ( this could change from worksheet to workheet) so what i am lookig to do is = COUNTIF(BH2:BH250,AT2) and BH250 to remain contant when i fill the column with a the fomula |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You forgot to mention the MATCH is an array formula
With the MATCH formula (giving 250) in say AT1 = COUNTIF(INDIRECT("BH2:BH"&$AT$1,AT2) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kevcar40" wrote in message ps.com... hi i am trying to search over a range only as far as the first empty cell eg row 274 = COUNTIF(BH2:BH134,AT2) what i would like to do is use the value of the first empty cell so the search would be BH2: "First empty cell",AT2 the formula below returns the row number of the last row with data in it =(MATCH("EMPTY",INDIRECT("BA1:BA"&COUNTIF(BA:BA,"< ")+1)&"EMPTY", 0)-1)) ie 250 ( this could change from worksheet to workheet) so what i am lookig to do is = COUNTIF(BH2:BH250,AT2) and BH250 to remain contant when i fill the column with a the fomula |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On 12 Mar, 13:22, "Bernard Liengme"
wrote: You forgot to mention the MATCH is an array formula With the MATCH formula (giving 250) in say AT1 = COUNTIF(INDIRECT("BH2:BH"&$AT$1,AT2) -- Bernard V Liengmewww.stfx.ca/people/bliengme remove caps from email "kevcar40" wrote in message ps.com... hi i am trying to search over a range only as far as the first empty cell eg row 274 = COUNTIF(BH2:BH134,AT2) what i would like to do is use the value of the first empty cell so the search would be BH2: "First empty cell",AT2 the formula below returns the row number of the last row with data in it =(MATCH("EMPTY",INDIRECT("BA1:BA"&COUNTIF(BA:BA,"< ")+1)&"EMPTY", 0)-1)) ie 250 ( this could change from worksheet to workheet) so what i am lookig to do is = COUNTIF(BH2:BH250,AT2) and BH250 to remain contant when i fill the column with a the fomula- Hide quoted text - - Show quoted text - thank you it works a treat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |