Transferring a line of data from one tab to another using formulas

[MSpring]

I am trying to have an entire line of data copied into another tab depending on the status of a claim. For instance, if the claim on Tab A has a column for its status and I enter approved, then the entire line is copied and added to the tab for all approved claims (Tab B). If I enter denied, I want it to go to a different tab (Tab C), and and if I enter pending, yet another tab (Tab D). Does anyone know how to do this?

Basically i want to be able to look at Tab A and see a complete list of all claims but then be able to click to Tab B and see an list of all approved claims, and so on.

[ben_hensel]

Probably the easiest way would for each of B, C, and D to be Pivot Tables fed by the data in A, with different criteria for a Filter.

[CWatsonJr]

How about something like this?

The main formula statements used are index, match and vlookup. I did add a variable name so that when the database grows, it will keep picking up the data automatically without having to adjust the formulas on the other sheets. The array formula will pick up the claim number if it is set to approved and then the vlookup will add the matching data for that row.

[MSpring]

Ok I think I got it to work, thank you very much. What if I want to change the names of the statuses? We have decided to break down "approved" into two types of approvals, "continuous" and intermittent". When I change the word "approved" in the formula it doesn't work anymore.
Also, I'd like to expand the data to include two more columns of info (dates) before the status. I have not been able to successfully insert columns and maintain the working formulas.
I want it to read Claim #, Name, Start Date, End Date, Status....
Any help you could give me would be much appreciated.

[CWatsonJr]

You can change the name of status or add extra status names as you wish. Just change the formula in A2 ...(Claims,0,4)="Approved"... so that the ="Name of Status" matches. Make sure you press Ctrl-Shift-Enter when you are done editing the formula - that is an array formula and if you just hit Enter, it won't work.

The 4 ((Claims,0,4) is the column number (from the left of where your indexing starts - in this case column A) that you have your status in on your Claims tab. If you move the Status column over, change the 4 to match the column number the status is in. Also, if you add extra columns, make sure you go to your name manager and edit the variable name "Claims" to adjust for the extra columns.

It would be easier to add tabs for "Continuous" and "Intermittent" than to rewrite the statement to look for either status. It may be possible but I will have to think on it and play with it to see if I can get it to work that way - I have only used this to match one item, not one or another.

Hope this helps some.

[CWatsonJr]

Another way to handle the Intermittent and Continuous problem is to add a column for a second status and then pull that in with another VLOOKUP statement.

[CWatsonJr]

Here is an example file with the start and end date columns added. I added another column called status 2 for Intermittent and Continuous. I haven't given up on looking for Intermittent or Continuous and then placing that on the Approved tab... Just haven't figured out how to do that... yet.

[CWatsonJr]

Here is another way to do it. Instead of showing the Status column on the Approved tab, show the Status 2 column.