Dynamic Vlookup or Index Match?

Iluvhc

New Member
Joined
Aug 28, 2019
Messages
3
Hello, I have a large data set in tab 1 and in column T there are statuses (Active or Retired). what I would like to do is create a new tab (tab 2) to only show those individuals with an Active status. So every time I change the status to Active in my first tab it would populate into my new tab. Is there an excel formula that would help me so that?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi, welcome to the board.

It's possible to do this, but could be complicated, depending on how you want to do it.

I would recommend NOT doing this, and instead consider options that let you keep everything on a single tab, such as Excel's Data / Filter functions.
 
Upvote 0
Welcome to the Board!

consider options that let you keep everything on a single tab, such as Excel's Data / Filter functions.
I agree with Gerald's suggestion. This is how I would do it myself. You can apply filters, which would allow you to easily view just Active, Inactive, or All with a few clicks.
 
Last edited:
Upvote 0
Thanks for the welcome and quick response!
Part of the reason I’m pulling Active names into a new tab is because I need o use the data to calculate the month over month savings for the name associated with that status in the new tab. Is there a simple way to maybe add a new column in the large data that would associate a number (let’s say 1-20) each time the status changes to Active. Then on the new tab to somehow label 1 - 20 and if match then bring in that name associated with the number?


Hi, welcome to the board.

It's possible to do this, but could be complicated, depending on how you want to do it.

I would recommend NOT doing this, and instead consider options that let you keep everything on a single tab, such as Excel's Data / Filter functions.
 
Upvote 0
I am not 100% clear on what you are asking. Are you asking for a running count of the Active people?
So, the first "Active" person would have a "1", the next would have a "2", etc?

If so, you can do that in a new column with a formula like shown below.
Let's say that the column indicating "Active" or "Inactive" is column B.
Then place the following formula on row 2 and copy down for all rows:
Code:
=IF(B2="Active",COUNTIF(B$2:B2,"Active"),"")
 
Upvote 0
Thank you. When I used this formula it gave me a 1 again each time Active showed up.

I used =IF(T4=“Active”,COUNTIF($T$4,”Active”),” “). My active status is in T and my statuses are in T4-T200.

I am not 100% clear on what you are asking. Are you asking for a running count of the Active people?
So, the first "Active" person would have a "1", the next would have a "2", etc?

If so, you can do that in a new column with a formula like shown below.
Let's say that the column indicating "Active" or "Inactive" is column B.
Then place the following formula on row 2 and copy down for all rows:
Code:
=IF(B2="Active",COUNTIF(B$2:B2,"Active"),"")
 
Upvote 0
You messed up the range in the COUNTIF part of the function.
It should be:
Code:
[COLOR=#333333]=IF(T4="Active",COUNTIF([/COLOR][COLOR=#ff0000]T$4:T4[/COLOR][COLOR=#333333],"Active")," ")[/COLOR]
Note that the location of the dollar sign is absolutely critical to the success of the formula.
That locks the starting row at row 4 as you copy the formula down the page.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top