Data from multiple sheets

[dwsg01]

Hello,

I am creating a Spreadsheet at the moment that has 3 sheets for 3 different people, each page is going to have information they will each work down and mark off yes or no in a drop down linking to the task they are doing, I want a 4th page that will take all the information from each from each of the 3 sheets row that has yes next to it. If it is relevant there will be 150 rows on each sheet Columns A to J showing Different information.

Any advice on this would be brilliant thanks.

[daffodil11]

There's a few ways. You could do the literal cell for cell pull, which is to click a cell, hit = and then nagivate to another sheet and click a cell.

Another would be to use an INDEX(SMALL(IF array.

I can't see your data, but let's pretend each sheet has some silliness in A2:A150, some more thingies in B2:B150, and then Yes and No's in C2:C150. In my imaginary workbook, you named your worksheets after your tabs after your people: Batman, Unicorn, and Frodo_Baggins.

A2 on Sheet4:

=IFERROR(INDEX(Batman!A$2:A$150,SMALL(IF(Batman!$C$2:$C$150="Y",ROW($A$2:$A$150)-1),ROW(A70))),"")

This is confirmed with Ctrl+Shift+Enter to create an Array Formula. Drag this down 150 rows. Then start over, but with references to the next sheet.

Or, you could use VBA code to swiftly run through each sheet and pull the relevant details.

[dwsg01]

I think =Iferror will work I have sheets Danny, Lauren, Will, Each sheet has 150 "tasks" in each task it has info about each task in columns A-J so ill work it around that.

thanks :D

[dwsg01]

Right, this is how it turned out: =ArrayFormula(IFERROR(INDEX(Danny!A$2:A$151,SMALL(IF(Danny!$L$2:$L$151="Post",ROW($A$2:$A$151)-1),ROW(A70))),""))

Yes is now post as basically next to each task I have put a drop down saying; email/post if post is selected then it goes onto the 4th page so our admin can post information now i'm getting no error but the information from a row that says post is not being copied. I was doing this in excel but have now moved to google sheets so it can be shared will this make a difference

Edit:

Doh I just realised Im doing columns only can this be used if I want the rows all to merge together from each sheet ?

[daffodil11]

Your first row should be ....!$L$2:$L$151="Post",ROW($A$2:$A$151)-1),ROW(A1))),""))

This value will increment each time it is copied down to A2, A3, effectively searching for the 1st smallest, 2nd smallest, 3rd smallest without having to manually adjust the formula.

Just make sure the first line for each name's formula starts with ...1)-1),ROW(A1)))

[dwsg01]

OK sorted that Thank you so much, I just realized though I want all the data from the 3 sheets merged together will that work. I'm guesing not using that type of formula as id need versions with Danny!-:- Lauren!-:- and Will!-:- obviously cant put that in the same cell.

[daffodil11]

I'd recommend attaching a sample of your workbook, or creating a mock-up if it's sensitive. Pretty much everything is possible; just depends how hard you want to work for it.

I have a spreadsheet that can look up office locations, pull related Access data via a SQL query, filter relevant details within 'X' miles using spherical trigonometry, and instantly SaveAs pdf to predefined file name and attached to Outlook email. The sky is the limit.

[dwsg01]

Ill get you a mock up haha