MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I've got a load of tasks in a spreadsheet laid out in rows, with associated data like 'number', 'date', 'action', 'owner' etc.
Some of these tasks require further work before they can be done, while others can just be done without further input. I have a column that expresses this with a Y for further work and N for no further work.
I want to set up another sheet that takes the rows where further work is required (Y in the cell) and duplicates the information, so I can see what needs further work in one sheet.
I don't need all the data columns copying over though, just a few of them like 'number', 'date', 'action' and then I want to add a column to the end of this detailing what the further work is.
I could probably just filter for the Y's and N's (on the original sheet) but want to do it with a new sheet if possible, and if it's not too tricky?
I did think about just doing =if(cell(A5)=Y,"A1","") and doing that for all cells in the row, but then I would end up with blank rows where there is an N. I'd prefer it if they ended up stacked beneath each other......
I really am crap at Excel!
Or a pivot table with the " further work is required = Y" in the report filter, and the fields you want in the rows of the table.
That way you can refresh the pivot table as Y changes to N and you'll get the most up to date list
Oooh, that sounds good. Will investigate pivot tables....! Ta.
This sounds like something where "advanced filter" is the right tool for the job.
This effiectively does a filter and a copy paste all in one go.
Yeah, a pivot table would work. Or an access query that pulls everything from the main worksheet through a query, only returns rows with Y and then spits that back into excel.
That might be overcomplicating things though...