Dynamic Vlookup or Index Match?

[Iluvhc]

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?

 

[Gerald Higgins]

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.

 

[Joe4]

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.

 

[Iluvhc]

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.

 

[Joe4]

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:

=IF(B2="Active",COUNTIF(B$2:B2,"Active"),"")

 

[Iluvhc]

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:

=IF(B2="Active",COUNTIF(B$2:B2,"Active"),"")

 

[Joe4]

You messed up the range in the COUNTIF part of the function.
It should be:

[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.