# Find Table header in a dynamic range and start counting 1 cell below up to the last used range

#### mabelO

##### New Member
I have a function that returns a location of a header in a column (B)
It prints this location (B19) into cell A2
Below this header that is in B19 is a list of employees that i want to count up to the last used row

So i am looking for a way for excel to take a look at cell A2, grab the value in A2 which is another cell location (B19) and start counting from 1 cell below (B20) all the way to the last used row in column B.

The reason for the cell location been Printed in A2 is because the table is dynamic, i don't always know where the header will start from based on other information on the table so i have to first find the header and start counting 1 cell below it all the way to the last used row.

Attached is an example of what i'm looking for

#### Attachments

• Excel Example.PNG
75.9 KB · Views: 5

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=LOOKUP(2,1/(B1:B1000<>""),ROW(B1:B1000))-MATCH("List of Employees",B1:B1000,0)``

#### mabelO

##### New Member
Omg, thanks a bunch!! that worked like a charm.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

#### mabelO

##### New Member

Hey, i was wondering if there is a way to determine the range of table 1 and select the range...

See table 2 starts one cell after table one ends. both Tables are dynamic so i cant always tell how big it will get. I know table 1 will always start at A5 but i dont know where it will end. I want to be able to select the whole range of table 1. I have attached a small visual to explain what i need.

#### Attachments

• mr excel.PNG
56.4 KB · Views: 2

#### Fluff

##### MrExcel MVP, Moderator
As that is a totally different question it needs a new thread. Thanks

Ahh understood

Replies
12
Views
390
Replies
2
Views
66
Replies
3
Views
65
Replies
5
Views
136
Replies
2
Views
96

1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back