1

I have a sheet with certain data arranged like this:

X8 - something - something2
X12 - somethingelse - asdfg
X8 - asft - sastaryraavt
X8 - random - random2

I need to extract only and all of the rows labeled X8 to another sheet inside the Excel spreadsheet. So for the above example, the second sheet would look exactly the same but would be missing the 2nd row labeled X12.

How would one go about doing this?

EDIT: I would like it to be more like a function and not require copy-and-paste or such. For example, if one value is changed in the parent spreadsheet, say the "something" in the first X8 row, the change should also automatically mirror over to the child spreadsheet (the one containing only X8 rows).

EDIT 2: I just figured out how to mirror my parent sheet to the new sheet using =IF(OFFSET(Form!$A$1,ROW(C14)-1,COLUMN(C14)-1)=0,"",OFFSET(Form!$A$1,ROW(C14)-1‌​,COLUMN(C14)-1)). I'm still confused on how to delete rows (like not just make empty, completely delete) that contain a certain word, say, X12. Additionally, I'm using Google Spreadsheets so I don't think I can input code like Sub etc and am relying only on native formulas.

  • You could sort everything, and then all the X8 would be together and then all you need is a single cut and paste. – cybernard Mar 2 '16 at 1:44
  • Oops, it turns out I wasn't specific :) edited – crownusa Mar 2 '16 at 2:12
  • Update: I am using =IF(OFFSET(Form!$A$1,ROW(C14)-1,COLUMN(C14)-1)=0,"",OFFSET(Form!$A$1,ROW(C14)-1,COLUMN(C14)-1)) to mirror my new sheet as the old one. How can I delete the rows with anything but "X8"? – crownusa Mar 2 '16 at 2:31
  • This will give you useful tips, I tried it myself already.chandoo.org/wp/2014/12/09/… For a more serious version: thinketg.com/… – Michthan Mar 2 '16 at 9:05
  • Thanks! I figured out how to copy/mirror the spreadsheet, but I still need to know how to delete rows that have "X12" or whatever in them. – crownusa Mar 2 '16 at 23:58
1

If you must use Google Sheets, then the following won't work, but I'll offer as much (since this question was tagged for Excel), just in case it helps you or others that need to mirror content conditionally to another sheet like this.

Using Excel, once you have your source data on your first sheet (regardless of whether this is a 'Table'), position cursor in data/table area and from Data ribbon, select From Table (in the Get & Transform section). This will open a query editor (looks like PowerQuery). From here, you can complete whatever transformations or selections you need to filter or re-arrange or wrangle your data. So you could filter by your first column, in your case. But note you can do quite a lot more here (adding new calculated columns, re-arranging columns, removing columns, etc.). When done, select Close & Load in the Home ribbon. Then select New Sheet.

Now when you make changes to your source sheet table, the Query you created will handle the required selection and transformation to mirror to the other sheet. Note to see changes, you'll need to click Refresh on the Data ribbon. You can also schedule automatic refreshes if desired. Click Connections from the Data ribbon, then select your Query and click Properties.

Again, sadly this won't work in Google Sheets. But perhaps it'll be helpful if you have native Excel as an option (if using an older version of Excel, install PowerQuery to get the same functionality): https://www.microsoft.com/en-us/download/details.aspx?id=39379

  • Thanks! This seems to be a very helpful answer for Excel users. – crownusa Mar 4 '16 at 2:27
  • For now I'll mark this as correct due to absence of other answers and that I don't require this feature anymore ;) – crownusa Mar 14 '16 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.