Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help ~ Lookup from Multiple Pages/Tabs Huge Woorkbook..
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help ~ Lookup from Multiple Pages/Tabs Huge Woorkbook..
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")&"'!D1:D65536")) 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? "Champ" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please Help ~ Lookup from Multiple Pages/Tabs Huge Woorkbook..
Hi,
You may also want to try using Data Consolidate -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Champ" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help ~ Trying to Lookup from multiple pages | Excel Discussion (Misc queries) | |||
Lookup the same part numbers from multiple pages | Excel Discussion (Misc queries) | |||
Printing/# pages inclusive of tabs at bottom | Excel Worksheet Functions | |||
How do you attach tabs to make one page or several pages? | Excel Worksheet Functions | |||
Tabs v.Pages | Excel Worksheet Functions |