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

Dancey_

New Member
Joined
Aug 18, 2010
Messages
41
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 :)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Dancey_

New Member
Joined
Aug 18, 2010
Messages
41
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
 

Dancey_

New Member
Joined
Aug 18, 2010
Messages
41

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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_

New Member
Joined
Aug 18, 2010
Messages
41

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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_

New Member
Joined
Aug 18, 2010
Messages
41
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 :biggrin:
 

Dancey_

New Member
Joined
Aug 18, 2010
Messages
41
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
 

Forum statistics

Threads
1,141,015
Messages
5,703,743
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top