MATCH on Different Sheets

[Varlock]

Hi All,

I have a workbook that contains several Data Worksheets. Sheets contain data from the different Vending machines and all of them are formatted similarly. Each sheet is named according to the Serial Number of the Vending Machine, from which the data is taken (so that I have a single page for each Vending Machine). Furthermore, I have sheets - "HelpSheet" (where I save all the named list and other required information) and "DashBoard" (where I calculate all the data extracted from the Data Sheets).

The Data Sheets contain the following information (in total, 1580 entries):

• Column A - Data ID's (ID1, ID2...ID150...ID1580) - which are unique and reiterate every time I import a new data from a vending machine (e.g. ID3 always refers to the "Machine Serial Number");
• Column B - Textual Description of the ID's;
• Column C - Actual Data (Numbers, Dates, Text);

Here is the formula that I use to extract data from the Data Sheets (I will divide it in two parts to keep it simple):

[B]PART 1[/B]    =IF(INDIRECT(ADDRESS(MATCH(VLOOKUP(A7,ALLID,6,FALSE),INDIRECT(J6),0),3,4,TRUE, I6))=D5,
 
[B]PART 2[/B]    OFFSET(INDIRECT(ADDRESS(MATCH(VLOOKUP(A7,ALLID,6,FALSE),INDIRECT(J6),0),3,4,TRUE, I6)),-10,0),"")

Let me define all the variables that are used in the above mentioned formula:

PART 1

• "A7" - Is a drop-down cell, where I choose the product, whose Sales Data I want to Extract;
• "ALLID" - is a named range, which comprises the following Data:
  > Column 1 - Product Names​
  > Column 2 - Product Price ID​
  > Column 3 - Product Sales ID​
  > Column 4 - Product Tests ID​
  > Column 5 - Product Free Sales ID​
  > Column 6 - Product Date ID​
  *All of the 1580 ID's are listed in this 6 columns.​
• "I6" - Is a drop-down cell, where I choose the Vending Machine [Serial Number], whose Data I want to Extract. As far as Sheet names are same as Vending machine serial numbers, this range is used as a variable Sheet name for formulas (specifically, in ADDRESS function);
• "J6" - Automatically returns a range address ("A:A") for the Chosen Vending Machine/Sheet: e.g. If "I6" = 12517834, the range "J6" would equal to '12517834'!A:A (this range is used as a lookup_array in MATCH function).
• "D5" - the desired Date of Data that has to be extracted is entered here.

Please find a detailed explanation of the PART 1 of the formula below:

PART 2

As for the PART 2 of the formula: there is only an additional OFFSET function here, which jumps 10 rows UP and returns a value of the Range, which is a Sales Volume of a chosen Vending Machine ("I6") for a chosen period/date ("D5")


This formula works fine in case I want to extract Data from a single worksheet, but I have a problem while summing the data from all worksheets.

I tried to change the second part of the formula (MATCH function "lookup_array") with something like this (↓), but it does not work:

SUM(OFFSET(INDIRECT(ADDRESS(MATCH(VLOOKUP(A7,ALLID,6,FALSE),INDIRECT[B]('[I]Firstsheet[/I]:[I]Lastsheet[/I]'!A:A)[/B],0),3,4,TRUE, I6)),-10,0)

Please find attached the sample workbook - all important ranges are highlighted and some of them have comments as well.


Sample Workbook.xlsx - Speedy Share - upload your files here


Thanks in advance - any help would be greatly appreciated.

 

[Varlock]

Could someone please help?