Return multiple values from multiple sheets

[brianfromla]

I'm really not sure exactly how to explain this, but I'll try. I have a workbook with multiple sheets in it. One sheet is an Overview sheet, and the others represent the months (JAN, FEB, MAR, etc). On the Overview sheet, I have a few columns of data set up that I need to retrieve from the various sheets within the workbook. The thing is, this data can occur more than once on any particular sheet and it's very likely to occur multiple times throughout the workbook. For example, compressor #1 was blown down on January 1, January 15, February 5, February 9, February 12, April 22, and so on. Compressors 2-5 were also blown down multiple times throughout the year, as well as various other equipment that I have listed out throughout the numerous monthly tabs. On the Overview sheet, I have the columns Equipment, Date, Temp, Begin PSIG and MCF Loss. I'm needing to get the information from those tabs into these columns.

I first tried a simple VLOOKUP. After all, it's worked for so many situations similar to this, why wouldn't it work here? I quickly found that it won't work here because I need to return multiple results, not just the first one it comes across.

Next, I tried a little Googling and on numerous websites I found an array formula. I got pretty close this time, and I feel like I'm onto something. But when I copy the formula down it seems to return the same results in every cell, not the next result, and the next, and the next, and so on.

Like I wasn't sure how to explain the issue so I hope I didn't leave you all scratching your heads. But if I did, let me know what kind of crucial details you might need to know.

My example spreadsheet is below.

Attachment 230355

[Lemice]

Hello,

I have an idea of what you are trying to do, however I cannot download your attached file (Keep saying to me, "Invalid Attachment specified").
Can you re-upload it, or provide another sample?

[brianfromla]

example.xlsx

Let's see if it works now.

[Lemice]

I don't know if this is what you are looking for, but here, check the attached file.
Basically I made the Overview Worksheet to automatically update and pull everything on other worksheets. All you have to do is to drag the formula down as far as you see "FALSE"

[brianfromla]

That's very close to what I was looking for. There was one thing I forgot to mention, though, and I know you can't read my mind. lol So for that, I apologize. But what I have in my working workbook is a list of all the pieces of equipment. This is due to the fact that I have other types of blow downs included in my monthly tabs, such as pipelines. I'm trying to "grab" and isolate from each tab just the objects in my list, which would be the objects that I listed in my example workbook and then some.

The other thing is, do you know of a way to make it put everything laid out exactly as you have it, but without it repeating the column headers throughout?

Thank you again, your help is much appreciated!

[Lemice]

I have fixed the "header here and there" problem.
About your lookup list, if you have something to differentiate the data you are looking for among worksheets and duplicated data, then there's always a way to look for it (for e.g. name, date, temp, ...)

[brianfromla]

Lemice,

Thanks for your help again.

Here is another copy of my example workbook where I've added a column on the Overview sheet called "Equipment List". This includes not only the equipment that I included in the monthly tabs, but also other pieces of equipment that could also be blown down. I'd like to still be able to cherry-pick any of these items as they appear in the monthly tabs, without any spaces or error values in between... just one item per row. Some of these items may come up one or more times, or not at all, throughout the year and of course they would only show up in columns B:F of the Overview sheet if they actually come up in the monthly tabs. Again, any of these items is subject to show up in any monthly tab any number of times, depending upon what occurred during that month.

If you have a chance to look at this for me again today, I appreciate it!

example(1).xlsx

[Lemice]

Then I suggest you add a helper column to the right of the table, checking whether an item appears in the list of equipments you wanted to lookup for (that's why we have a master list, right?).

So I put up a helper column in column H, which will return TRUE to whatever row has an item which name appears in your "wanted" list. What you can do with it is that you can Filter the results to only show TRUE (only show items that match a name on wanted list). I have already added that column and the filter to the sample file attached.

Note: Filter can be found in Data --> Filter, and the name of your wanted list must be exact. I checked why Slug catcher didn't show up first and noticed that in the sample you attached in post #7 (right above), you have 2 space instead of 1 between "Slug" and "catcher". You should check those thing, because a simple extra space will make the helper column returns FALSE.

And here is the sample file using the file you provided.
Don't hesitate to let me know if you have any other problem.

[brianfromla]

I'll try this out shortly but I wanted to first say thanks again. I also do have one question, because I do like to learn. In G1:L1 on the Overview sheet, what are those formulas doing exactly? I've researched how those functions work individually, but I'm having a hard time following what they're doing in this situation. I can tell they're doing something, and it appears to be critical to what I'm trying to accomplish there, but it's not sinking in through my thick skull. Like I said, I enjoy learning... especially with Excel, so I not only want to have a functioning workbook but I want to understand it as well.

Thanks. I'll let you know if I have any other issues!

[Lemice]

Oh, they are there to return the number of active rows found within each worksheet, I don't want to put them into the "many rows" formula you see, so I put them there.

My idea is, if the current row number of the formula is still smaller than the number of active rows (rows with data) in a worksheet, then pull a row from it, if not, then move on to next worksheet, and so on.