Home |
Search |
Today's Posts |
#1
|
|||
|
|||
value lookup help
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 -- elitewolverine ------------------------------------------------------------------------ elitewolverine's Profile: http://www.excelforum.com/member.php...o&userid=27893 View this thread: http://www.excelforum.com/showthread...hreadid=474031 |
#2
|
|||
|
|||
On Fri, 7 Oct 2005 00:46:29 -0500, elitewolverine
<elitewolverine.1wipyc_1128665153.2263@excelforu m-nospam.com wrote: 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 __________________________ |
#3
|
|||
|
|||
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 -- elitewolverine ------------------------------------------------------------------------ elitewolverine's Profile: http://www.excelforum.com/member.php...o&userid=27893 View this thread: http://www.excelforum.com/showthread...hreadid=474031 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |