Query multiple sheets and create report

[tbutori]

Hello,

I am new to the forum but have used many of the suggestions posted. Thanks to everyone for sharing your knowledge!

I have a workbook with over 100 worksheets. 90 of the worksheets contain equipment check sheets with a section at the bottom of each sheet where follow-up required can be entered. The column positions of each sheet is the same but the row(s) can be anywhere on the sheet. I would like to have all of these follow-ups from each of the 90 sheets transferred to on one report worksheet named "Follow-up".

I don't know how to write VBA code. I tried to use the VLookup function by adding a formula in column A that will return the value of 1 if there is follow-up information in that row. Using the VLookup for every row on every sheet and make it dynamic in case more rows are needed will take forever.
I have watched videos that will transfer the data from multiple sheets to one sheet but the data must be in a table and it must start at the same cell on every sheet. I don't want to change these sheets into tables and the starting cell can be anywhere on the sheet.
I thought Power Query might be the answer but I am new to that as well and have only used it a few times.

I would post an example but I'm not sure how to do that.
Thanks for any help you can provided.

 

[Logit]

.
Actually, after looking closer at the workbook and the results of the code for consolidating all data to the FOLLOW UP sheet, I am not pleased with the results.

It is capturing most of the data from the various sheets but overlooking others.

May I recommend you re-design all of the sheets that include the "Follow Up Required" section ? Doing so will assist us in capturing all the data you intend.

Here are two designs for consideration :

Regardless of which method you choose, make certain in the "Follow Up Required" area of each sheet, that you DO NOT USE MERGED CELLS. NO MERGED CELLS AT ALL.
Merged cells should be avoided at all cost in all of your projects. They usually create more problems than they are worth.

Designs

#1 : Make certain the "Follow Up Required" section (all of the rows including the headers for each column) are absolutely identical to one another. AND, make certain
this section of each sheet is located in the same rows / columns on each sheet. i.e., If the title "Follow Up Required" is located on rows 27 of the first sheet, make certain
all the others sheets show "Follow Up Required" in also on the 27th row. If the remainder of the section (approx the next 7 rows) ends at row 34, make certain all the sheets
conform to the same layout. Including the columns. Also, do not have anything else on those rows to either the left or right side of the "Follow Up Required" section rows.

#2 : If it doesn't interfere with the daily use of the sheets by your employees, and won't make it more confusing for them, consider not having the "Follow Up Required" section
on each sheet. Think about having a command button or a hyperlink on each sheet that will take the user to a single "Follow Up Required" sheet. All of the "Follow Up" data will
be confined to a single sheet (no need to copy and paste like we are trying to do now). There is code for either a command button or a link (such as a word or symbol) that can
be clicked that will take the user to the Consolidated Follow Up sheet and to the correct area of that sheet so they can enter their findings for follow up.

Either suggested design will require more work but in the long run the results will be worth it.

 

[tbutori]

Thank you again Logit. Very good suggestions. I hadn't thought about a hyperlink to the "Follow-up" page. That might be the answer.

 

[Logit]

.
Great !

If you will put together the basics of your workbook, with the consolidated "Follow Up Required" Sheet, I'll be able to correctly insert the links on each sheet (please indicate where on each sheet you want the link)
and I can also have that link take the user to the appropriate beginning row for that specific area.