Log In
Register
Home
Active Member
March 10, 2021
Offline
Hello,
I have a workbook with 60+ sheets that contains burial information for the local cemetery.
Sheet A is updated via web query, and has a VLOOKUP formula in column J, checking sheet A1 to see if there are any new names. If so, it places the word "NEW" into the J column on the row of that new person. This is repeated for sheets B & B1, C & C1, etc.
I would like to have a new sheet, that will pull the rows from all sheets that have the "NEW" in column J.
So if sheet A has 2 new people, sheet B has 1 and sheet C has 4, then all 7 people will be listed on this new sheet, the complete row to include the information from Column A to Column I (Name, burial location, birth, death, etc)
I've included a small sample workbook with 4 pages and 10 persons on each, as well as a sheet of what I'm trying to accomplish.
Please know that I do not have much Excel knowledge, I only use a small percentage that it provides, I'm seeing this the longer I work on this workbook lol.
So please dumb it down for me if you can, just a bit. Or provide a formula that I can edit. I hate asking that. 🙂
NOTE: I did check out the VLOOKUP Multiple Sheets article, but it was a bit over my head, and I couldn't get it to work.
September 9, 2020
Offline
Hi B Fig,
Voici une solution en utilisant PowerQuery (in A22).
BR,
Lionel
Active Member
March 10, 2021
Offline
Thank you for your help. I have tried to recreate it myself but am having trouble with getting it to pull from the other sheets.
Would you be ok with me providing the complete file, once it is finished, and post it on Dropbox for you to check out?
July 16, 2010
Offline
Hi,
I would approach it without using the VLOOKUP because it's not good practice to add formulas to columns in a table that is the output of a query. Instead, I'd use Power Query to find the new records using a merge.
I'd start by consolidating all original tables into one big table, and all new tables into another big table. You can use Power Query to consolidate the sheets. Then you can use Merge to extract a list of new records.
I hope that points you in the right direction. Please come back if you're still stuck after watching the tutorials linked to above.
Mynda
September 9, 2020
Offline
Hi,
My first post was only an answer to the problem I was facing. Now that other questions have emerged, and as Mynda says very well, other answers must be considered. Above all, we must avoid mixing up everything: the data collected, the data analysed, the consolidated data... There really must be a separation.
BR,
Lionel
Active Member
March 10, 2021
Offline
I had to try it out a few times, but it is working well. I just need to finish adding the rest and then I will apply this to the whole set.
Do you happen to know if there is a way to pin a certain sheet's tab to force it to always be visible at the bottom? There are so many sheets building up, it would be nice to have this new consolidated sheet stay in one place easily accessible.
Thank you both!
Active Member
March 10, 2021
Offline
Ok, after my last post I was wondering if I can just do away with all tabs. I know that deleting them breaks it, so I decided to start fresh and create a new workbook and import each web-based table into one sheet. So adding the first was easy, then I just did a "Close & Load To..." for the other imported tables. It looks great. However when I try to do the merge to be able to see the changes/differences, I can only select one table and not the whole sheet. It feels like I am not doing something correctly. I thought to merge the tables but then not sure what that would do to the web connections.
Is it because each table I have comes from a different webpage and needs its own connection that this might not be possible?
Attached a sample file.
1 Guest(s)
