Finding last value Column - formula

[SeliM]

Good morning
I am helping a small local government with a report compilation project for tenderers.
A Procurement system exports tender details and tenderers to excel for reporting in a template in word,
There can be significant differences in number of tenders submitted making a table or list challenging.
I'm looking for a formula (there are no staff competent in Excel VBA so trying to keep it to formula driven) that is able to calculate the number of tenders from a known start position ($A$11) when the list may vary from 6 -60. Most formulas assume there are no details following a list which is not the case with material I am using.
The =COUNTA('Final_Report-43859_Q23_24_30_De'!$A$11:$A$16) works because I know the last cell $A$16 its when the number of tenders is greater I'm challenged.

The Procurement system export workbook will have an empty cell after the list (in this case at $A$17) and has text at $A$18 - these addresses of course change when more or less tenders are received.

Any advice and guidance most welcomed on how to find the last tender is the list.

I hope this summary makes sense.

Thanks in advance.

Mel

 

[Trebor76]

Hi SeliM,

Maybe this:

=COUNTA(INDIRECT("'Final_Report-43859_Q23_24_30_De'!$A$11:$A$" & LOOKUP(2,1/(NOT(ISBLANK('Final_Report-43859_Q23_24_30_De'!A:A))),ROW('Final_Report-43859_Q23_24_30_De'!A:A))))

The LOOKUP part of the formula will find the last row in Col. A of the Final_Report-43859_Q23_24_30_De tab whether there's blanks in-between or not.

HTH

Robert

 

[Fluff]

A non volatile option

=COUNTA('Final_Report-43859_Q23_24_30_De'!A11:INDEX('Final_Report-43859_Q23_24_30_De'!A11:A50,XMATCH(" ",'Final_Report-43859_Q23_24_30_De'!A11:A50&" ")))

 

[SeliM]

Thanks very much for your assistance.

Mel

 

[Fluff]

Glad we could help & thanks for the feedback.