value lookup help

[elitewolverine]

well have a lookup problem, and i just for somereason cant find the help
i need in online examples...

our company keeps track of the year by using periods and weeks...where
there is 4 weeks in one period so effectively giving 14periods per year
for the 52 weeks...

well on our spreadsheet we keep track of each periods cash flow +/-, to
see if someone is steeling or the like...

what i am having trouble with is, every week we go in and clear the
sheet of data to have a blank sheet, i have already done leaps and
bounds for the boss in having half the sheet automated, calculations
for the boss, he used it only because he liked the lines lol.

so every week we have to manually enter the cell named "PTD" for the
period cash, so say its week 3 and period 11, it would be the total of
three weeks worth +/-.

well it would be tons easier if the spreed sheet only required as
little input as possible.

On sheet 1, is our main data we use everyday, in cell b2 we have the
period number, in cell d2 we have the week number

towards the bottom in cell a10, we have the cell "PTD"

i want that cell to look at cell b2 and d2 determine the period and
week, look in sheet 3 find the row value that represents the value of
the period on sheet one, then find the column representing the week,
and give me the value at the intersection....

so far i tried =hlookup(b2, sheet3!a2:a15, sheet3!a3:a6)

or something like that, tried the vlookup function and tried index
function just right now i cant think straight after doing 3d
programming for a hobby .

this would save us tons trouble if i could find the right function
probably a index one just cant put it together

 

[Richard Buttrey]

well have a lookup problem, and i just for somereason cant find the help
i need in online examples...

our company keeps track of the year by using periods and weeks...where
there is 4 weeks in one period so effectively giving 14periods per year
for the 52 weeks...

well on our spreadsheet we keep track of each periods cash flow +/-, to
see if someone is steeling or the like...

what i am having trouble with is, every week we go in and clear the
sheet of data to have a blank sheet, i have already done leaps and
bounds for the boss in having half the sheet automated, calculations
for the boss, he used it only because he liked the lines lol.

so every week we have to manually enter the cell named "PTD" for the
period cash, so say its week 3 and period 11, it would be the total of
three weeks worth +/-.

well it would be tons easier if the spreed sheet only required as
little input as possible.

On sheet 1, is our main data we use everyday, in cell b2 we have the
period number, in cell d2 we have the week number

towards the bottom in cell a10, we have the cell "PTD"

i want that cell to look at cell b2 and d2 determine the period and
week, look in sheet 3 find the row value that represents the value of
the period on sheet one, then find the column representing the week,
and give me the value at the intersection....

so far i tried =hlookup(b2, sheet3!a2:a15, sheet3!a3:a6)

or something like that, tried the vlookup function and tried index
function just right now i cant think straight after doing 3d
programming for a hobby .

this would save us tons trouble if i could find the right function
probably a index one just cant put it together

Hi,

Assuming your table of data on sheet 3 is in cells A1:E14, where
A2:A14 are periods 1 to 13 (not sure why you have 14 periods in 52
week year of 4 weeks per period), and B1:E4 are periods 1 to 4

Do the following

1. Create three range names,

period should be cell B2
week should be cell d2
data should be cells A2:E14

Now in your PTD cell enter the formula

=INDEX(data,period+1,week+1)

The plus +1 is to allow for column A being period numbers and row 1
being week numbers with the monetary values in B2:E14

HTH



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[elitewolverine]

thanks, worked great, was able to aply it to a if function and some sum
functions as well for some custom work sheets of my own

oops that was a typo on my part it is 13 periods not 14...

lol boss face was great when she went in there and found 90% of her
spreadsheet automated. Also got me a small raise