200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I need help with this formula:
I would like to have, From column "K", the whole row show up on the "ON sheet" if it has "on" in the column and then if it has 'OFF" in the column to show up on the "off" sheet in the same workbook but have the data show up in two different sheets?
(I deleted and changed my data because it is personal information)
I've been trying to use the different lookup formulas and have had no luck.
Thank you.
Heather
You have some non-VBA choices but not using a formula:
As Variatus, points out in the discussion above, using Excel's built-in filtering is the easiest solution to viewing (and printing) subsets of data. It also avoids the problem of your ON OFF sheets becoming out of sync with the Master sheet.
In Excel 2013 I think you'd need to click somewhere in the data then select menu Data > Filter. Autofiltering wil be applied (down arrows on each column heading). Then click the down arrow on column K, uncheck (Select All) to clear all of the check boxes then check the ON box (or OFF to see only those rows). That will leave you with a filtered view which can be printed. You can even hide column K and the filtering will remain.
If, for some reason, that approach cannot work for you and you really need a snapshot of ON and OFF rows, the simplest way is to make a copy of your sheet Master (with your cursor over the Tab, right-click, pick Move or Copy... then check Create a copy and where you want it). On that new sheet, apply Auto Filter so you get the down arrows on all the column headings.
If you want column K for ON, click on the filter arrow and uncheck ON. Then select all the visible rows and (on a row number), right click and pick Delete. Remove the filters and you're left with only the ON rows.
Do the same but unchecking OFF etc.
A neat way is covered in Don's tutorial here: Filter While Leaving Original Data Intact in Excel but the limitation is that the filtered data can't be created in another sheet- you have to create it in the same sheet. You could then copy/paste into another sheet or in your ON sheet say set each cell formula to = a cell from that range in your Master sheet.
Achieving your goal with using formula would be trickier, lead to a large file and need filtering anyway I think. I'm guessing that you're not familiar with VBA so haven't suggested a solution using macros.
Hope this helps.
