Grabbing data from various tabs to highlight in a summary

It would help if you attached a sample Excel workbook, and you can do that within this forum - some contributors do not like (or are not allowed by company firewalls etc.) to download files from third party sites.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

Thanks Pete, attached now.

I'm about to go off-line for a few hours, but take a look at this thread from yesterday, which asked for a similar thing:

https://www.excelforum.com/excel-gen...nt-sheets.html

I've attached my worked file from that thread.

Hope this helps.

Pete

Cheers Pete it is pretty close indeed, I will look into every formula you suggested to understand it and see what can I use in my example.

Hi Pete, I have looked into your formulas and it is a really smart approach. Wow you are a real Excel Guru.

After half a day spent on breaking your formulas down I understand most of the concept although not everything and I think if I get them all I can try to adjust formulas to make my own example working.

So green I get, red I am missing something, and my comment is in blue:

put zero in cell V1 of the BUY_1 sheet and this formula in V2:

=IF(SUM(S2:U2)>0,MAX(V$1:V1)+1,"-")

and copy this down to the bottom of your table - it will identify the records which match your criteria and give each a unique sequential number. We want to continue that numbering into the other sheets, so in cell V1 of BUY_2 you can use this formula:

=MAX('BUY 1'!V:V)

and then you can use the previous formula in V2, copied down. Similarly, in V1 of the BUY_3 sheet use this formula:

=MAX('BUY 2'!V:V)

and this one in V2:

=IF(SUM(S2:U2)>0,MAX(V$1:V1)+1,"-")

copied down. In the RECAP sheet we can produce a summary table of the number of records in each of the subsidiary sheets, so list the sheet names BUY 1, BUY 2 and BUY 3 in cells AA2 to AA4, put zero in cell AB1 (this is important), and this formula in cell AB2:

=MAX(INDIRECT("'"&AA2&"'!v:v"))

1. Why do we need the number of records for each sheet?
2. I understand MAX. In indirect I get that we need word from AA2 to look for as a sheet name, & sign on the both sides of AA2 means to look on tabs on right and left of Buy1? I know & sign is about connecting, but not sure of this usage. Then we go and check a v column which is fine.
3. I also don't get why we need extra ' and " for AA2. Is is to treat AA2 as a text I assume but that would be just a pair of ""...

Copy this down to AB4.

It will also help if we use two more helper columns in this sheet, one for the sheet name and the other for the row in that sheet where the data can be found, so put this formula in W2:

=IFERROR(INDEX($AA$2:$AA$4,MATCH(ROWS($1:1)-1,$AB$1:$AB$4)),"")

4. I get iferror. Index looks in a range given $AA$2:$AA$4 I don't really get a need of 4 times $.
5. I get Match but MATCH(ROWS)? Cant find on the internet when you actually put ROWS wording for an example. Match gets the position of an item in an array, so we look on the value which is first row -1 (whatever it means) and our array is AB range so like a tiny reference to all sheets?
6. Not sure about $ before row 1:1 again. Also why do we -1 here?

and this one in X2:

=IF(W2="","",MATCH(ROWS($1:1),INDIRECT("'"&W2&"'!v:v")))

7. If is simple, then we grab position of a row 1 in an array. ROWS again, when I have removed it return completely different value, why is that I have never not needed to use it like that?
8. ("'"&W2&"'!v:v") - referance from W2 and V column I assume? I have asked similar question earlier.

Copy these down to the bottom of your table. Then you can use this formula in cell A2:

=IF($W2="","",INDEX(INDIRECT("'"&$W2&"'!a:u"),$X2,COLUMNS($A:A)))

9. A bit lost again here. So index says we are looking in array INDIRECT("'"&$W2&"'!a:u") for a row - which is cell X2 and column A. So why do we put $ sign before X2 and A?
10. Also don't rally get how we are looking via Column A to find name of tab and go threw whole table of content a:u?

This formula can be copied across to U2, and then the formulae from A2:U2 can be copied down to the bottom of your table.

Sorry to be a pain, just trying to understand your work better to sort my example and learn as well.

If you could kindly answer that would be great,

Thank you
Jake

Hi Jake,

the first formula sets up a sequential numbering system for those records which meet the criteria (in that particular case, it was if the sum of columns S to U was greater than zero). Suppose this reaches 10 on the first sheet, then on the second sheet we want the numbering to continue from that point, hence the formula in V1 which looks back to the previous sheet. Those numbers are then gathered in a small table, to allow us to determine which sheet we want to get the data from, i.e if we are looking for record 8 it occurred on Sheet1, whereas record 11 can be found on Sheet2, and so on.

The INDIRECT function takes a string of text which represents a cell or range reference, and converts this into the actual reference so that Excel can make use of it. The apostrophes are needed if there are any spaces (or other special characters) in the sheet names, so the text in the inner bracket is made up of an apostrophe plus the sheet name plus another apostrophe followed by the ! character and then the actual range.

To understand the MATCH function, you need to first understand what the ROWS function is doing - initially it returns the number of rows between row 1 and row 1 (i.e. 1). However, when this formula is copied down the ROWS($1:1) will change to ROWS($1:2), then ROWS($1:3) and so on for consecutive rows. Note that the first parameter does not change (because of the $ symbol in front of the 1), and so the function effectively will return 1, then 2, then 3 etc. on consecutive rows. Thus we are re-creating the set of sequential numbers when this is copied down. The MATCH function returns the relative position of the matching item, and because there is no third parameter, this means that we want to match with the numbers 0 up to the second number in column AB, hence we subtract 1 from the ROWS function. Note that the INDEX range is from AA2 to AA4, and we need $ symbols in those cell references so that they do not change when the formula is copied down.

I am about to go out for a few hours, but I'll continue with the detailed explanation later this afternoon.

Hope this helps.

Pete

It helps a lot, will wait for more, thanks Pete.

Jake