Advancing cell formula by one row using VBA (or another way?)

[Dancey_]

Afternoon guys,

Was wondering if I could get a bit of help please?

Basically I have been tasked with creating a daily report for the call centre I am a Manager in.

I have built a 'data source' spreadsheet, column A is a list of every date in the year, then columns going across are the bits of data I need on the report.

My 'daily report' spreadsheet is basically a template.

What I can do is quite easily create links from my daily report back to the raw data within my data source. Of course that is only good for one day, I dont want to have to go back and edit each cell's formula each day to update the report. It has about fifty parameters.

Quite simply I need something that when I tell it to will change all the chosen formulas on my template from for example "=[test1.xlsx]Sheet1!$B$2" this would be for today, but tommorow it would need to be "=[test1.xlsx]Sheet1!$B$3" and so on and so forth each day it would advance one.

Can any one help me please?

Thanks

 

[Andrew Poulsom]

Change the 2 to 3:

=VLOOKUP(H1,'Z:\eGroup Team Managers\Daily Report\[Daily Report V2 DATA SOURCE.xlsx]Reports'!$A$3:$C$306,3,FALSE)

If you want to copy the formula across you can use:

=VLOOKUP($H1,'[Daily Report V2 Date Source.xls]Sheet1'!$A$3:B$306,COLUMNS('[Daily Report V2 Date Source.xls]Sheet1'!$A$3:B$3),FALSE)

 

[Dancey_]

Change the 2 to 3:

=VLOOKUP(H1,'Z:\eGroup Team Managers\Daily Report\[Daily Report V2 DATA SOURCE.xlsx]Reports'!$A$3:$C$306,3,FALSE)

If you want to copy the formula across you can use:

=VLOOKUP($H1,'[Daily Report V2 Date Source.xls]Sheet1'!$A$3:B$306,COLUMNS('[Daily Report V2 Date Source.xls]Sheet1'!$A$3:B$3),FALSE)

Thanks dude, changing the numbers doesnt work, it doesnt give an error, it just returns zero but the cell that it should be looking at C5 has the value ten in it?!

Also, with the second option, how do you tell it which column to look at?

Thanks again!

 

[Dancey_]

Thanks dude, changing the numbers doesnt work, it doesnt give an error, it just returns zero but the cell that it should be looking at C5 has the value ten in it?!

Also, with the second option, how do you tell it which column to look at?

Thanks again!

My fault, I needed to save the Data Source spreadsheet before the formula would update, doh!

Seems to be working perfectly! Thanks

Cheers