Pull row from Mastersheet into another worksheet based on a cell value

[Katelee]

Hello, I have two worksheets.

Mastersheet
Subsheet

What I want to do, is update the Mastersheet and use a formula (not Macro) to extract the whole row into a subsheet, if in that row it contains a particular Cell value.

e.g. In the example below, If column C contains '2' I want to pull that whole row in the same order into the subsheet.

Mastersheet
A B C D
Cats|Dogs|Fish|Crab
3 2 2 3
5 5 3 3
7 3 2 3

Subsheet
A B C D
Cats|Dogs|Fish|Crab
3 2 2 3
7 3 2 3

Is there a formula I can use at all?

Thank you for your help.

[JohnTopley]

Try

in Master Sheet

in A2

=IFERROR(INDEX(Sheet1!$A$2:$D$4,SMALL(IF(Sheet1!$C$2:$C$4=2,ROW(Sheet1!$A$2:$A$4)-ROW($A$2)+1,""),ROWS($A$2:A2)),COLUMN()),"")

Sheet1 is your sub-sheet

Enter with Ctrl+Shift+Enter

Copy acroos and down

[Katelee]

Hello,

Thats awesome thank you, it does exactly what I want there's only one thing, when I copy the formula down and across it doesn't work to pick up the next row that will have a '2' in the fish column. Am I doing something wrong or not changing something I should?

Thank you for your help

[JohnTopley]

Was it entered with Ctrl+Shift+Enter? You will see curly brackets { ....} appear round the formula if this is done correctly.

And are the ranges correct?

[Katelee]

Hello, thank you so much, that was the issue, one last question, when I apply this formula from my excel 2010 to an older excel 2003 it doesn't work. How can I use this formula in Excel 2003?

[JohnTopley]

You need to replace IFERROR with ISERROR construct

Try

=IF(ISERROR(INDEX(Sheet1!$A$2:$D$4,SMALL(IF(Sheet1!$C$2:$C$4=2,ROW(Sheet1!$A$2:$A$4)-ROW($A$2)+1,""),ROWS($A$2:A2)),COLUMN())),"",INDEX(Sheet1!$A$2:$D$4,SMALL(IF(Sheet1!$C$2:$C$4=2,ROW(Sheet1!$A$2:$A$4)-ROW($A$2)+1,""),ROWS($A$2:A2)),COLUMN()))

[Katelee]

Thank you so much, if I wanted to replace the 2 with the word Fish (instead of a number) would that make a difference

[JohnTopley]

It should work with "Fish"

[Katelee]

Thank you, it works with the "" marks around the word which is great, the only thing now is that when I put the ctrl, shift and enter it turns the cell to #NA the formula also is not copying down. Im not sure what I did wrong

[JohnTopley]

Please post a sample file.

To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Katelee]

Hi, my comp with 2003 doesn't have internet, its to do with the A cells, they don't change, I have to change each cell manually

[JohnTopley]

Without a file it isn't possible to help resolve the problem.