Help automating updating columns (find and replace) with macros.

[Stacker]

I routinely get files and people here have helped me with automating one component. However I wanna create a macros that allows m to automate the updating of certain worksheets. I am given an excel file with three tabs.

In the first tab i get a lot of hospital data and one of my tasks is to to copy the last column say MG and paste it into the MH. All of the cells in the column follow a certain formula. The top being like so:

=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[270821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)

I have to paste this column into the next and from there ctrl and f and find and replace. So I would find and replace 270821 with 280821 solely in that column.

Is there a way to copy and paste the previous column and then find and replace by incrememnts of 1 day using a macro.

The second and third stuff are fairly quick so I am not bothered

 

[Stacker]

=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\["& TEXT(D$2;"ddmmyy")&" HRI.xlsx]Daily sitrep'!$F$8:$F$11)

The data is in D2 but the reference still fails

 

[GWteB]

We're in need of the INDIRECT worksheet function.

=SUM(INDIRECT("'\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\["& TEXT(D$2;"ddmmyy")&" HRI.xlsx]Daily sitrep'!$F$8:$F$11"))

 

[Stacker]

It refers to the D2 this time (as in D2 turns blue) but there is an error messaging saying there is a problem with this formula.

 

[GWteB]

In my situation (with a much shorter pathname) it works, provided the top row contains valid dates. Dragging this formula to the right and downwards results in an expected outcome.
Be sure that the single quotes and double quotes are on the right spot.

TargetBookWithLinks.xlsx
	A	B	C
1	4-okt	5-okt	6-okt
2	041021	051021	061021
3	23	230	15
4	356	3530	19
5	4799	10186	12
Sheet1

Cell Formulas
Range	Formula
A2:C2	A2	=A1
A3:C3	A3	=SUM(INDIRECT("'F:\TEST\[Book" & TEXT(A$2,"ddmmjj") & " Source.xlsx]Sheet1'!$A$1:$B$1"))
A4:C4	A4	=SUM(INDIRECT("'F:\TEST\[Book" & TEXT(A$2,"ddmmjj") & " Source.xlsx]Sheet1'!$A$1:$C$1"))
A5:C5	A5	=SUM(INDIRECT("'F:\TEST\[Book" & TEXT(A$2,"ddmmjj") & " Source.xlsx]Sheet1'!$B$1:$D$1"))

 

[Stacker]

It still says there is problem with the formula. The formula should refer to a particular file. I was hoping the file should produce something like "240821 HRI" within the formula.

=SUM(INDIRECT("'\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\["& TEXT(D$2;"ddmmyy")&" HRI.xlsx]Daily sitrep'!$F$8:$F$11"))

This formula should get data from inside the files being referred to.

Could a bracket be missing?

 

[GWteB]

A closer look tells me, where I have the dates on row 1 and the custom formatting (to match the filename) on row 2, you have it the other way around
In your formula, D$2 must therefore become D$1.

 

[Stacker]

I thought that but still says there is a mistake on the formula.

 

[GWteB]

still says there is a mistake on the formula.

I copied your formula and pasted it into an editor. The single and double quotes are in the right place, even the space in the filename is there, so it should work.

Why is sum used when I am not adding anything I am just collecting data from inside a file.

I don't know, you came up with it on your post #17.

 

[Stacker]

I copied your formula and pasted it into an editor. The single and double quotes are in the right place, even the space in the filename is there, so it should work.



I don't know, you came up with it on your post #17.

Apologies i only noticed sum was in my other formulas. My fault.

 

[Stacker]

I get this.