Can a Formula be written to send data to a closed file rather then pulling data?

Hello Everyone,

I have 12 workbooks and at the end of every day the Ranking sheet would be updated.

The Ranking Workbook has in these cells F4, I4, O4, U4, AA4, AG4: The following Formula's would go to File1 and Update the numbers

F4
=SUM('C:\testfolder\[File1).xlsm]Sheet1'!$F$4:$F$54)

I4
=SUM('C:\testfolder\[File1).xlsm]Sheet1'!$G$4:$G$54)

O4
=SUM('C:\testfolder\[File1).xlsm]Sheet1'!$H$4:$H$54)

U4
=SUM('C:\testfolder\[File1).xlsm]Sheet1'!$I$4:$I$54)

AA4
=SUM('C:\testfolder\[File1).xlsm]Sheet1'!$J$4:$J$54)

AG4
=SUM('C:\testfolder\[File1).xlsm]Sheet1'!$K$4:$K$54)

Here is the issue:

These Excel Files1 thru Excel File12 are no longer running from the test folder, they are now running from the Users Desktop and access to these folders is difficult because they are not in one place for all.

Is it possible to have a Command Button in each of these Excel Files 1-12 to reverse the process - instead of going out to each of these Excel files and updating the data can it be the other way and the Excel Files 1-12 send the Update to the Ranking sheet

Any thoughts would be appreciated,

Best Regards,

John

It should be possible via VBA to send this information over the other way around, as long as it isn't done at the same time by all 12 files (as the VBA code would have to open the "Master file" and paste the information in).

This does require the "Master file" to be centrally available to everyone.

Hello All,

I think that FerdyHar left the Forum before seeing my responce.

It sounds as though this is possible - Are there any samples of this I can review and try on my files??

Regards,

John

Thanks for the title change.

Hi FDibbins,

My apologies for not following the correct procedure, It was not my intent.

Again my humblest apologies

Regards,

No, a formula cannot "send" anything anywhere. It only applies to the cell it resides in.

Saving files on a desktop is not a particularly good idea. The files cannot (as a matter of course) be routinely be backed up to a server, and - as you have found - are not accessible to any others on the server. I have seen too many cases of desktop files being accidentally deleted or over-written.

Im sure that your IT department could set up a folder that all could access, so the files could reside there, and be easily accessed.