LinkBack URL
About LinkBacks
I have a filtered column that sorts through "open, closed, blanks" How can I write an if-then statement so when an item is labeled closed it'll automatically move to a new sheet I created? that way I only show open items on sheet 1?
If Then statement to new tab
I have a filtered column that sorts through "open, closed, blanks" How can I write an if-then statement so when an item is labeled closed it'll automatically move to a new sheet I created? that way I only show open items on sheet 1?
Formulas cannot affect other cells, and they cannot delete, move, copy 1 cell to another place.
Why not do this the other way round and pull all the non-closed to another sheet?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
this will still leave all the data on the 1st sheet, just pull what you want to see
How do I "pull" as you are suggesting?
I would use a helper column on the master sheet which identifies the type of record and allocates a unique ID to each matched record. Then on the Closed sheet your would have formulae that copy the data across (or "pull" it) for each matched record. If you attach a sample workbook I can show you how to achieve that.
To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
Note that the Paperclip icon does not work.
Hope this helps.
Pete
Try this. I creates a small sample - on the same sheet - but it shows the method...
Header1 Header2 Header1 Header2 aa Open aa Open bb Closed cc cc dd Open dd Open ff ee Closed gg Open ff ii gg Open hh Closed ii jj Closed
E2=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$11<>"closed",ROW($B$2:$B$11)),ROWS($A$1:A1))),"")
this is an ARRAY formula ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Then copy down and across as needed
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks