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

mabelO

New Member
Joined
Feb 25, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
    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
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=LOOKUP(2,1/(B1:B1000<>""),ROW(B1:B1000))-MATCH("List of Employees",B1:B1000,0)
 
Solution

mabelO

New Member
Joined
Feb 25, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Omg, thanks a bunch!! that worked like a charm.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

mabelO

New Member
Joined
Feb 25, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
    mr excel.PNG
    56.4 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
As that is a totally different question it needs a new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top