Cell Address of first occurrence of a formula

[Marklarbear]

Hi Brains Trust

Is there a way to return the cell address of the first occurrence of a formula in a column range?

ie in range B5:B500 there are values from cell B5 to B78 - then there are formulas from B79 to B500. Is there a formula I can have in cell K1 that tells me the first occurrence of a formula in column B is in cell B79?

(weekly a team member will copy, paste special values the most recent data - so this week the first cell with a formula is B79, next week it could be B84, the week after B107 and so on)

cheers

 

[Eric W]

Try:

Book2
	A	B	C	D
78		74
79		75		80
80		76 formula
81		77 formula
82		78 formula
Sheet5

Cell Formulas
Range	Formula
D79	D79	=MIN(IF(ISNA(FORMULATEXT(B5:B500)),"",ROW(B5:B500)))
B80:B82	B80	=ROW()-4&" formula"

Presumably, you already know the column.

 

[Marklarbear]

Try:

Book2
	A	B	C	D
78		74
79		75		80
80		76 formula
81		77 formula
82		78 formula
Sheet5

Cell Formulas
Range	Formula
D79	D79	=MIN(IF(ISNA(FORMULATEXT(B5:B500)),"",ROW(B5:B500)))
B80:B82	B80	=ROW()-4&" formula"

Presumably, you already know the column.

Thankyou - works perfectly!

 

[Eric W]

Happy to help! Thanks for the follow up.