Hopefully this question is in the right forum. I have a spreadsheet that I am using filtering to access the data I need. Once I have filtered the data, I want specific fields to be moved to another sheet. It works fine before I filter, then when I turn the filter on, no change. Is there a way to do this without a macro. I just want the data that I can see after the filter.
Thanks,
_Will
Need more details.
Where is this data?
Where do you want to put it?
You can do this with formulas. However, if you have 1000's of rows and dozens of columns then formulas are not the way to do this.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Ok, it is a camp registration. I need Row B, C, and D from the MASTER sheet copied to row B, C, and D on the Sign in Sheet. Column AN through AV on the master are the weeks they are attending. I need to filter from the MASTER to Sign in based on what week they are attending in order to create a sign-in sheet for each week.
How do you identify for which week the data is required. "Sign in" has a date June 20-24 but the "Master" only has week numbers. I would suggest you change the week numbers to dates (week beginning dates?)
Originally Posted by moonshinewillie
There are no rows B, C or D. Did you mean columns B, C and D?
It looks like you want to filter on column AN in the Master sheet and "copy" the data from columns B and C (first name, last name) to the Sign In sheet. Is that correct?
Sorry, long day. I want to filter them by the week they are attending. There are nine potential weeks they can attend. The weeks are Column AN through AV, then I need their first name, last name, and birthday to populate the sign-in sheet.
Thanks,
_Will
You're only filtering by 1 week at a time, correct?
Assuming that's the case...
Enter the week number that you want to filter on in a cell, say A4.
Sign In sheet cell A4 = Week 1
You filter the Master sheet on Week 1 in cell AN1.
On the Sign In sheet enter this array formula in B5:
=IFERROR(INDEX(MASTER!B$2:B$10,MATCH(ROWS(B$5:B5),SUBTOTAL(3,OFFSET(INDEX(MASTER!$AN$2:$AV$10,1,MATCH($A$4,MASTER!$AN$1:$AV$1,0)),,,ROW(MASTER!$AN$2:$AV$10)-ROW(MASTER!$AN$2)+1)),0)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy across to D5 then down to B40:D40
Tony,
I am only filtering one week at a time. Thank you for this. I have follwed your instructions, but am not getting anything. Any suggestions?
_Will
Anyone have any insights on this? We import data to the MASTER sheet from an online form. I want to be able to populate the form on sheet "Sign in" based on the week they are signed up: Master AN - AU. I can run a filter, but I cannot get the filtered data to populate the the other sheet. I cannot get the formula above to do anything.
Thanks,
_Will
Here's your sample file with the formulas implemented.
Tony,
Brilliant, thank you so much!!!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks