PDA

View Full Version : Change file name & path with formula or macro



UncleBob
11-17-2015, 05:00 AM
Hi there,
I need some help please.
The screenshot below is a file that shows who is working on which procject.
The data in C1 rightwards and A5 down are copied in each month by macro. Rows 2,3&4 are all formulas.
The problem is when a new project is added (say, "508 project") the formulas C5 down & right need changing to the file path & name in row 3 - is there a formula or macro that will do this for me please?
(I can't use INDIRECT as it would require all 70+ files to be open to work).

Thanks in advance

Paul
14781

UncleBob
11-17-2015, 06:46 AM
Cracked it!
All it needed was the formula in C5 changed to: =IF(SUMPRODUCT(--('G:\IT\CAPEX\2015 - 2016\[K$3]Resource Plan'!$B$20:$B$208=$A5))>0,"Yes","No")
(although for the next stage I will need help with a macro to extract data to create a summary)
Thanks for all those that looked.
Paul

UncleBob
11-17-2015, 07:12 AM
Sorry, this still isn't working - the formula won't update itself when copied or edited and the file list box keeps showing.

Thanks

Paul