Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data From A Table
The following is an extract from a 12 month worksheet
January Week This Month Cumalitive Week No 1 2 3 4 4 January To Date Quantity 22 34 55 77 77 188 188 Row 2 is the week #'s. Cells B3:E10 will contain the quantities When the week # is typed in cell F2, I want the macro to select the following data and put it into the detailed cells For week #4 the contents of cell E3 is put into cell F3 the contents of cells B3 to E3 are added together and the result is put into cell G the contents of cells B3 to E3 are added together and the result is put into cell H3 the contents of cell F3 is only applicable to the quantities produced for that particular week the contents of cell G3 is only applicable to the quantities produced for that particular month the contents of cell G3 will change as the week # changes for that particular month so as you go from weeek # 1 to week #4 there will be a different quantty depending on the week #. the contents of cell J3 is the overall quantities produced upto and including the week# selected in cell F2 I want to be able to obtain this information for a full 52 week period when the week # is typed in cell F2 Many thanks in advance for any help that you can provide. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data From A Table
Here's a formulas set-up for a full 52 weeks
which should achieve exactly what you're after: http://www.savefile.com/files/349982 Extracting data from 52 week table.xls Take a look, let me know .. the contents of cell J3 .. I presumed you mean H3 (typo) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "zephyr" wrote: The following is an extract from a 12 month worksheet January Week This Month Cumalitive Week No 1 2 3 4 4 January To Date Quantity 22 34 55 77 77 188 188 Row 2 is the week #'s. Cells B3:E10 will contain the quantities When the week # is typed in cell F2, I want the macro to select the following data and put it into the detailed cells For week #4 the contents of cell E3 is put into cell F3 the contents of cells B3 to E3 are added together and the result is put into cell G the contents of cells B3 to E3 are added together and the result is put into cell H3 the contents of cell F3 is only applicable to the quantities produced for that particular week the contents of cell G3 is only applicable to the quantities produced for that particular month the contents of cell G3 will change as the week # changes for that particular month so as you go from weeek # 1 to week #4 there will be a different quantty depending on the week #. the contents of cell J3 is the overall quantities produced upto and including the week# selected in cell F2 I want to be able to obtain this information for a full 52 week period when the week # is typed in cell F2 Many thanks in advance for any help that you can provide. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting Data From A Table
Not sure why you haven't received the festive cheers package sent
earlier ?? Think the link to d/l the sample is good (just checked) Anyway, here's a text descript of the set-up in the sample construct .. Week#s are placed in B2:BA2, viz.: 1, 2, 3, ... 52 Month#s are input within B1:BA1 to "coincide" with the last week for the month, viz in E1: 1, in I1: 2, in N1: 3, ... in AR1: 10, in AV1: 11, in BA1: 12 (all other cells within B1:BA1 are left blank) Qty1, Qty2 data starts in row3 down, viz. within B3:BA3, B4:BA4, etc The desired Week # will be input in BB2, eg: 7 In BC2: =IF(BB2="","",VLOOKUP(BB2,$BF$3:$BG$14,2)) This will retrieve the correct month# for the week# input in BB2 The table array for the vlookup above is set up in BF2:BG14 as: Week# Mth# 1 1 5 2 9 3 14 4 18 5 23 6 27 7 31 8 36 9 40 10 44 11 48 12 Then In BB3: =IF(BB$2="","",INDEX(B3:BA3,MATCH($BB$2,$B$2:$BA$2 ,0))) BB3 returns only the qty fig for the particular week # indicated in BB2 In BC3: =IF(BB$2="","",IF(BC$2=1,SUM(OFFSET(B3,,,,MATCH($B C$2,$B$1:$BA$1,0))),SUM(OFFSET(B3,,,,MATCH($BC$2,$ B$1:$BA$1,0)))-SUM(OFFSET(B3,,,,MATCH($BC$2-1,$B$1:$BA$1,0))))) BC3 returns the total figs for the particular month # in BC2 In BD3: =IF(BB$2="","",SUM(OFFSET(B3,,,,MATCH($BB$2,$B$2:$ BA$2,0)))) BD3 returns the overall qty figs up to and including the week# indicated in BB2 BB3:BD3 is selected and copied down as far as required Hence based on the single Week # input in BB2, the above will return all the required results, viz.: in BC2, and within cols BB to BD (from row3 down) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Data In Pivot Table | New Users to Excel | |||
Pivot Table - Extracting specific data | Excel Worksheet Functions | |||
Another Exciting Data Table Question!!!! | Charts and Charting in Excel | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
changing proportion of chart and data table | Charts and Charting in Excel |