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]

If you omit the $ signs from your formula you can copy it down and across:

=[test1.xlsx]Sheet1!B2

If you have the date and column header in a couple of cells you can use an INDEX/MATCH formula:

http://www.contextures.com/xlFunctions03.html#IndexMatch2

 

[Dancey_]

If you omit the $ signs from your formula you can copy it down and across:

=[test1.xlsx]Sheet1!B2

If you have the date and column header in a couple of cells you can use an INDEX/MATCH formula:

http://www.contextures.com/xlFunctions03.html#IndexMatch2

Thanks for the response but unfortuantely it isnt quite that simple.

My 'template/daily report' isnt in the same layout format as the data source. The data source is a plain layout with the date in column A going down and the data in row's going across. So dragging the formula wont work.

My template is a printable format and has all sort of other stuff on it and unfortunately isnt laid out simply it is more reader friendly once printed than user friendly while on screen, I need to be able to update the formulee daily to change for example from Row 2 for today, then tommorow I want to go in and repopulate my template from data in Row 3 and so on each day.

Alternatively is there a way I could have an input cell (or popup?) on the template/daily report sheet where the user (myself and an able colleague) could input a date (the date would be data in Column A in the other workbook) then the cell formulee within the template would update to reflect data for that day?

Thanks for your help!

Thanks

 

[Andrew Poulsom]

Did you try an INDEX/MATCH formula?

 

[Dancey_]

Did you try an INDEX/MATCH formula?

I have been trying to but I am not sure how to make it INDEX/MATCH in another workbook.

I just cant seem to get it work. Would you be able to advise please?

Both workbooks are stored in a folder called "Daily Report" the data source work book is called "Daily Report V2 DATA SOURCE"!

Thanks

 

[Andrew Poulsom]

What formula are you currently using and where is the date on your daily report sheet? And what range does the data you want to look up occupy? You could also use VLOOKUP.

 

[Dancey_]

What formula are you currently using and where is the date on your daily report sheet? And what range does the data you want to look up occupy? You could also use VLOOKUP.

Hi mate,

I havent had a formula that even remotely works yet so I have deleted them all

I will need to write loads of these statements, but once one is working it should be straight forward.

The statement would need to go into cell B5 on the daily report spreadsheet, the date will be in H1 on daily report. The date I want to look up is on Daily Report V2 Date Source in cells A3:A306 similarly the data that needs to be populated in B5 on daily report is in B3:B306 on Daily Report V2 Data Source.

The folder that all of this is stored in is Z:\eGroup Team Managers\Daily Report

Thanks for your help!

 

[Andrew Poulsom]

With the source workbook open you can use either of these formulas:

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

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

The formula will expand to include the path if you close the source workbook, but they will still work.

 

[Dancey_]

Hi mate,

I havent had a formula that even remotely works yet so I have deleted them all

I will need to write loads of these statements, but once one is working it should be straight forward.

The statement would need to go into cell B5 on the daily report spreadsheet, the date will be in H1 on daily report. The date I want to look up is on Daily Report V2 Date Source in cells A3:A306 similarly the data that needs to be populated in B5 on daily report is in B3:B306 on Daily Report V2 Data Source.

The folder that all of this is stored in is Z:\eGroup Team Managers\Daily Report

Thanks for your help!

I have now managed to do it with VLOOKUP using "=VLOOKUP(H1,'Z:\eGroup Team Managers\Daily Report\[Daily Report V2 DATA SOURCE.xlsx]Reports'!$A$3:$B$306,2,FALSE)"

Thanks for the ideas

 

[Dancey_]

With the source workbook open you can use either of these formulas:

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

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

The formula will expand to include the path if you close the source workbook, but they will still work.

My next issue occurs when I want to use the date in column A but the data in column C, the below formula return data from column B:

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

How can I make a table array with gaps in it? IE misses out column B?

Cheers