Automatically extracting Rows from constantly updated Tables

[HAV000]

Hello superusers, first time poster here!

I would like to extract rows satisfying criteria (akin to tags) from a constantly updated, main table and have them show up linked in the appropriate worksheet tab (in this attached example, appropriate chapters (column F on the main table). I can currently do this by Filtering by that certain criteria, copying the table that shows up, then pasting as linked cells into the appropriate worksheet. However I run into a couple problems:

1) In the main table, the data in the rows can move around to other rows, so I can't just do a simple "copy & paste linked cells" because they only refer back to a set row (e.g. Sheet1!A4) in the main table. Instead I want it to refer back to whichever rows in the entire main table contain the criteria/tag, even as the data in the row has moved.

2) Additionally, when I add new data, including the appropriate criteria/tag (chapter column in my attached table), I want that to automatically be extracted into the appropriate worksheet.

Note: After a perusal of similar questions on excel forum, I could only find ones that required me to view code, then add code, which I presume means it must be saved as a macro-enabled workbook at that point. I would like to do this without using VBA or macros, using instead F(x) like INDEX, QUERY, FILTER, etc.

Please help! If only #1 can be solved for now that is totally fine. Thank you!

[Pete_UK]

You can do it with formulae (although your text might be a bit long in some cases), but rather than have separate sheets for each chapter, wouldn't it be better to have just one sub-sheet where you can select the Chapter number from a drop-down (like a filter) and then display the records that meet the chosen criteria? I can show you how to do that (many other posts of mine cover this topic). Here's an example from a few weeks' back:

http://www.excelforum.com/excel-gene...taneously.html

Hope this helps.

Pete

[Jacc]

He he... I feel like I solved problems similar to this so many times. Last time was this, quite recently: http://www.excelforum.com/excel-form...e-vlookup.html ( I wouldn't call it a reverse vlookup though, that's something else).

Anyway, attached is what I think you're after. I changed your master sheet to an Excel Table. This will expand automatically as you add data and the formulas will also adjust automatically. I only copied the formulas down 15 rows or so in the chapter sheets, if you need more just copy down.

The formulas are array formulas. If you edit one you can't just hit enter afterward, it will ruin the formula, you have to use Ctrl + Shift + Enter.

If you for some reason want to add a row to the top of the master sheet the formulas need to be changed a bit.

[HAV000]

Thanks you guys! I got it finally!

Pete_UK: Thanks, dropdown sounds pretty nice, but probably not right now for what exactly I need. Will let you know if when I do!

Jacc: Thanks for working it out! Yea I know my question is far from unique lol, I just did a quick perusal and it's just hard to decipher all the excel language going on without feeling stuck and confused

[HAV000]

Actually yes, adding rows and moving rows around is something I'll do very often for my type of spreadsheet. I added entries to the main table but the other worksheets do not update. How can I adjust for that?

[Jacc]

The dashes in the workbook that I posted indicate which cells contain the formulas to extract, as I mentioned before they go down to row 15.
I have no idea how big your data set is, maybe they should go down to row 150 or row 1500. Anyway you just copy the formula down until you see dashes, then you know you have enough.
If you are in the 1500 to 15000 row range it will start to get really slow so don't copy down to row 15000 unless you have to.
If you need to automate this too I can put together a macro for you.