Dynamic lookup range - append row count number to column letter

**astolz** · 05-30-2014, 03:39 PM

I desperately need dynamic lookup ranges to make my workbooks run faster. Is it possible to reference a COUNTA cell after the column letter in my lookup range? I can't use named ranges because as I understand it they don't work in closed workbooks...

EXAMPLE

Cell A1 = COUNTA(20,000 Rows)

INDEX(B2:B&A1,1,2)

Thanks for any help!

**Tony Valko** · 05-30-2014, 07:13 PM

If A1 = 20000

This formula will refer to the range A2:A20000...

=A2:INDEX(A:A,A1)

**astolz** · 06-25-2014, 11:46 AM

Late response, but thank you none the less. Your solution worked beautifully!

**Tony Valko** · 06-25-2014, 01:50 PM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.