Please Help ~ Lookup from Multiple Pages/Tabs Huge Woorkbook..

[Champ]

I have a HUGE spreadsheet, 212 tabs at the bottom.
I need to do a lookup to bring that data, from multiple cells, into a master
sheet.

My information is as follows:

1st tab:
Jan 1990

2nd tab:
Feb 1990

3rd tab:
Mar 1990

4th tab:
Apr 1990

Through current Month and Year.

Each tab/sheet is as follows:
cell A1 cell B1 cell C1 cell D1 cell E1 cell
3092323 hammer green 2 $14.34 pr


cell A1 cell B1 cell C1 cell D1 cell E1 cell

4456782 tape yellow 47 $3.71 ea

and so on, to fill 65,536 lines down by 31 lines across.(lots of information)

I want to bring the information, 8 of the 31 lines/cells across, from each
of the 212 tabs/sheets in this workbook and have them auto update the master
sheet information that is already put into a master sheet, as a count to the
appropriate cells, as follows:

cell A1 cell B1 cell C1 cell D1 cell E1 cell
4456782 tape yellow 47 $3.71 ea

cell A2 cell B2 cell C2 cell D2 cell E2 cell
4456782 tape yellow 47 $3.71 ea

cell A3 cell B3 cell C3 cell D3 cell E3 cell
4456782 tape yellow 47 $3.71 ea

and so on - basically I want to count, from each month tab and each year
tab, how many of each part went in each month and year, so I can build a
trend for inventory.
The cells in each page/tab; i.e. cell A1 on any given page is not the same
as any other page either. (that is due to some months that part was not used).
This is huge and I have no idea how to do it, so I do realize that this is
beyond what I know. Please help.

 

[~L]

You could construct a table with dates across the columns and product names
down the rows, then use Sumproduct with indirect to set the arrays and
text(C1,"mmm yyyy") to capture the tab name for an indirect function.

The end result would be something along the lines of (in cell C2 with a date
in C1 and a product name in A2 and a color in B2)

=SUMPRODUCT(--(INDIRECT("'"&TEXT(C$1, "mmm
yyyy")&"'!B1:B65536")=$A2),--(INDIRECT("'"&TEXT(C$1, "mmm
yyyy")&"'!C1:C65536")=$B2),INDIRECT("'"&TEXT(C$1, "mmm yyyy")&"'!D165536"))

With a disclaimer that I expect it to be really really slow with that much
data. To make it workable, you can switch to manual calculation, do only one
column at a time, then copy/paste values after the results load.

Then at the end of the date range, use SUM(C1:HI1) (where HI is filling in
for whatever your actual last column is).

That should be a sum for each combination of colors and product types you
had down the A and B columns.

Is this what you had in mind?

 

[Ashish Mathur]

Hi,

You may also want to try using Data > Consolidate

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com