use cell value in a fomaula

  • Thread starter Thread starter kevcar40
  • Start date Start date
K

kevcar40

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
 
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)
 
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










- Show quoted text -
Click to expand...

thank you
it works a treat
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumif + indirect 3
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Dynamic Offset from an Application.Goto cell = Date or Time 10
Check data range and return false value if a cell is blank 2
When I paste>special>value, I get a ' or ^ at the target cell. Why 1
Excel Use COUNTIF across a range of cells with formulas 0
Selecting specific cells in a column using countif 1
Enter value zero into all cells in multiple ranges? 4

Back
Top