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.

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.

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
---