Vlookup or alternative for ever expanding spreadsheet.

[jontyoc]

HI All

Got a new data spreadsheet with the normal multitude of information that is largely irrelevant to the team leaders.
Last weeks report was no bother and Vlookup worked fine, but this week I would manually have to change my formula to retrieve the information.
Is there anyway that I can do this automatically?

The data runs currently from column A-Y (sorry at work and cant upload file directly)

[TABLE="width: 1235"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 4"]Wk-6[/TD]
[TD="colspan: 4"]Wk-7[/TD]
[TD="colspan: 4"]Wk-8[/TD]
[TD="colspan: 4"]Wk-9[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Contacts[/TD]
[TD] SLA[/TD]
[TD] SL[/TD]
[TD]# Q > SLA[/TD]
[TD]Contacts[/TD]
[TD] SLA[/TD]
[TD] SL[/TD]
[TD]# Q > SLA[/TD]
[TD]Contacts[/TD]
[TD] SLA[/TD]
[TD] SL[/TD]
[TD]# Q > SLA[/TD]
[TD]Contacts[/TD]
[TD] SLA[/TD]
[TD] SL[/TD]
[TD]# Q > SLA[/TD]
[TD]Contacts[/TD]
[TD] SLA[/TD]
[TD] SL[/TD]
[TD]# Q > SLA[/TD]
[/TR]
[TR]
[TD]Account A[/TD]
[TD]45,084[/TD]
[TD]80.2[/TD]
[TD]80.5[/TD]
[TD]85.0%[/TD]
[TD]12,272[/TD]
[TD]80.7[/TD]
[TD]84.5[/TD]
[TD]85.7%[/TD]
[TD]11,525[/TD]
[TD]80.7[/TD]
[TD]88.8[/TD]
[TD]89.7%[/TD]
[TD]10,929[/TD]
[TD]80.8[/TD]
[TD]89.2[/TD]
[TD]92.7%[/TD]
[TD]12,435[/TD]
[TD]80.8[/TD]
[TD]86.8[/TD]
[TD]89.5%[/TD]
[/TR]
[TR]
[TD]Account B[/TD]
[TD]3,882[/TD]
[TD]70.0[/TD]
[TD]77.5[/TD]
[TD]73.6%[/TD]
[TD]1,005[/TD]
[TD]70.0[/TD]
[TD]84.8[/TD]
[TD]90.9%[/TD]
[TD]860[/TD]
[TD]70.0[/TD]
[TD]84.3[/TD]
[TD]90.5%[/TD]
[TD]831[/TD]
[TD]70.0[/TD]
[TD]80.6[/TD]
[TD]86.4%[/TD]
[TD]751[/TD]
[TD]70.0[/TD]
[TD]79.4[/TD]
[TD]81.0%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]48,966[/TD]
[TD]76.8[/TD]
[TD]80.3[/TD]
[TD]81.2%[/TD]
[TD]13,277[/TD]
[TD]77.3[/TD]
[TD]84.5[/TD]
[TD]87.2%[/TD]
[TD]12,385[/TD]
[TD]77.6[/TD]
[TD]88.5[/TD]
[TD]89.9%[/TD]
[TD]11,760[/TD]
[TD]77.5[/TD]
[TD]88.6[/TD]
[TD]90.9%[/TD]
[TD]13,186[/TD]
[TD]78.1[/TD]
[TD]86.4[/TD]
[TD]87.2%[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]



Next week the report will run from columns A - AG and so on.

What I had been using to pull the data for week 8 was the following:

=vlookup(a3,a3:y5,20,false) to give me the SL for week 8

I currently have over 80 accounts that this would need doing for, so would appreciate the help to setup a one time formula to pull each accounts data each week as the spreadsheet expands.

 

[njimack]

Two suggestions:
- use dynamic ranges (see link here: http://www.contextures.com/xlNames01.html#Dynamic)
- use INDEX & MATCH instead of VLOOKUP, which will mean you don't have to hard-code the column index in your formula.

 

[jontyoc]

Thanks Neil
The link for index and match gave me an idea.
interactive vlookup.

One cell has the week number of new spreadsheet (8,9,10 etc)

=vlookup(a3,a3:y5,(A1-3)*4,false)

This then moves the vlookup the correct number of columns along!!

Thanks Neil.