vlookup formula help

[excel?]

Is there a way to convert this formula to sum a range instead of the single column?

=VLOOKUP("Production Total",Input!$A$6:$AK$195,$M$3+1,FALSE)

$M$3 is a number generated based on the selection from a pull down box. If you select a date (10/4/06) from the Pull Down List, cell M$3$ returns "4", so the VLOOKUP Formula then looks in Column 5 and returns the "Production Total" for that day.

What I'm trying to do is have a pull down box by Week instead of by Day. So If I select Week 1, the formula returns the Sum of the "Production Total" from Column 2 thru 8 (10/1/06 - 10/7/06).

 

[barry houdini]

If the week number is in A2 then you could try

=SUM(VLOOKUP("Production Total",input!$A$6:$AK$195,($A$2-1)*7+{2,3,4,5,6,7,8},0))

confirmed with CTRL+SHIFT+ENTER

...although perhaps a simpler way would be to use

=SUM(OFFSET(INDEX(A6:A195,MATCH("production total",A6:A195,0)),,(A2-1)*7+1,,7))

which only requires ENTER

 

[excel?]

Thanks for the respoonse - can you tell me what is Cell A2 supposed to be? What is in this cell?

The Pull Down offers the user to choose "Week 1", "Week 2", "Week 3", and "Week 4". If "Week 1" is chosen then in Cell M3 is the number "1".

I do not see how to get your formula to Sum columns 2 thru 8 based on the user selecting "Week 1"

 

[barry houdini]

Sorry, I assumed your week numbers would be determined by a different cell to M3. Just substitute M3 for A2, i.e.

=SUM(OFFSET(INDEX($A$6:$A$195,MATCH("production total",$A$6:$A$195,0)),,($M$3-1)*7+1,,7))