changing the vlookup col_index_num

[dawn]

I have a spreadsheet where I update the col index number each week to reflect
the new weeks data for about 100 customers. At this point each week I go in
and change the col_index_number in each vlookup by 1 (100 times). Is there
an easier way?

Problem 2;, I also change the graph range to reflect the prev most 6 weeks,
thus these charts (100) need updated each week as well. Can anyone help?

 

[Jarek Kujawa]

1. insert yr col number in a particular cell
then refer to the cell in yr formulae
change the week number by changing that cells' value

2. to achieve this you would need to provide more details - the solution may
require working out some formulae

 

[dawn]

Problem 1 - thank you - I did not think that I could do that.
Problem 2 -

A simple graph pulling in data in rows 1-101 Columns b-ba hold weeks 1-52
sales data.
for instance week 6 I use col b thru g for the data points. Week 7 I need
to use c thru h for the data points.

hope that makes sense.

 

[Kassie]

Vlookup

Use a cell to refer to the offset column. Change you VLOOKUP formula to
refer to this cell, rather than the offset column.

Let's say you use Cell A1 to insert your latest col offset nr.

If you have data in A2:K2, and you want to extract the latest col number's
data in L2, then use =VLOOKUP(A2,A2:K2,$A$1,0). Copy this down to all your
rows, and in future you will only update cell A1

--
HTH

Kassie

Replace xxx with hotmail

 

[Jarek Kujawa]

2. you might try to use 6 helper columns to define chart series - somewhere
else in your worksheet
(insert weeknumber in say AA1)

=OFFSET($A$1,,$AA$1-COLUMN())
then drag/copy right

and change yr references for chart series through right-clicking on yr chart
then->Select Data (depending on Excel version you use)

hope this isn't vague - if so do come back with further questions

 

[dawn]

Still having isues with the 2nd part... 1st part works great - thank you.

I have excel2007 so what am I changing my cell reference to in the table?

I can get a return of the first cell reference only in my item - but not the
6 weeks that I need...

I used =OFFSET('[FY09 VMI Detail.xls]VMI'!$J$61,0,$B$4,0,-6)

[FY09 VMI Detail.xls]VMI'!$J$61 - is where the data table is starting with
week 1
$B$4 is where I would enter the current week

can you tell what "part" I am missing?