Vba multiple spreedsheets linked

[DEEARO]

I have created a spreadsheet and made a copy for each user so they store their own information and then information is linked to a master excel sheet via a power query. However the issue I faced was that if I wanted to make a change to main sheet I have to go to each user sheet and copy the changes over. For example. I wanted to add a column and wanted to reflect on all users sheets.

is there an easier way?

[bluesky63]

hi,

you can create a power query parameter, just need to update in one table and propagate to other user query, can visualise your spreadsheet, best you can upload a mock-up of your workbook

[DEEARO]

hi bluesky63 I have added a file in post #1

[DEEARO]

Is anyone able to help future

[FDibbins]

You supply 1 file with 1 sheet and no data at all - not really that much to go on, not is it?

[DEEARO]

Don't need data at the moment, the sheet is the master copy of the table. There are 10 people with each having their own copy of the file. However I want to add an extra column to each of the 10 users. Is there an easier way set up the file so in the future I don't need to go into each of 10 users file and add the column.

[bluesky63]

Hi Deerao

Are you saying each users will have originally 6 fields (your attached master files)initially

and subsequently when you add the 7th field in the master file, each user will have to manually add the 7th fields ?

There are many way to do it and we need to know your entire flow of update and also the finally reporting etc

Either each of the users will have a connector (Goto Data > Connections > Add the master file) goto existing connection and
master file fields, whenever there is an update or additional of the master fields, users just need to right-click and refresh to
get their table updated

Kindly please illustrate and explain a little bit more so that we can provide you the suitable solution

[DEEARO]

Hi bluesky63 /ALL

Thank you for your replies. as requested further details


I have attached a zip file and the files individually.


Master: This workbook is main copy and I have copied this file for each user
Staff1, staff2, are each user to collect their own data (copy of the Master workbook)
DATA COLLECTION: this file uses power query to collect all users data to 1 single file


I wanted to add new columns to each user file and change the name of one the fields (as their was a spelling mistake), so I went into each workbook and manually completed this task (at work I had almost 90users, so took a very long time to complete)..


How do set up my files so in the future I can do this task without going into each user, hence I only have to change the master workbook.

[AliGW]

(as their was a spelling mistake) ...

That's ironic!

I think the updating of the slave worksheets will probably require VBA - I don't believe it can be done with PowerQuery without deleting any data that has been manually entered.

[DEEARO]

Hi Ali, thank you for the response. I Don't know vba, are you able to help with the writing the code?

[AliGW]

Unfortunately not, and it was merely an observation, but there are others here who do VBA.

[DEEARO]

is there any one who is able to find a solution for please ?

[DEEARO]

is there any one who is able to find a solution for please ?

[AliGW]

You are getting no interest. Shall I move this to the VBA section for you and see if you get a bite there?

[DEEARO]

hi maybe that would be a good idea or what about the power query group (https://excelfox.com/forum/forumdisp...t-and-Power-BI) or both?

[DEEARO]

Ali, how do we transfer case over to another section

[bluesky63]

Hi Deearo,

Done using Data connections and Power Query

Master.xls ==> this is the master header for you to change whenever you want
Staff1a.xlsx - Contain staff1a data entries and also always link to Master.xls, any changes in the master headers will update in user file
Staff2a.xlsx - Contain staffa data entries and also always link to Master.xls, any changes in the master headers will update in user file
combined_Report.xls - Combine all the Staff files into a single one

My Query code is basing on From Folder, please change the source to your own local drive, copy all the staff files and Master, combined into the folder

This is what I can think of the quick and easy way without VBA, may not be the best

Rgds

[DEEARO]

thank you very much this looks what I want to achieve. I just tried to create my own connections but no headers shows. Please can you help by giving me the step by step guide how to create a connection please

I have tried to look for a video on youtube but cannot find a video which suits this scenario

[DEEARO]

thank you soooooooooooooooooo much, amazing I got it to work.

[bluesky63]

Hi Deearo, so you managed to figure out ?

[DEEARO]

Yes I have managed to figure it out. I have found few issues, if there are 2 tables on one sheet it combines it into one table. In addition, If you add a column in the middle of the Master table, then rest of the data in the staff sheet does not move accordingly

[bluesky63]

Hi Deearo,

...... if there are 2 tables on one sheet it combines it into one table.............. don't understand this, give me a sample

this solution is a workaround without using VBA or other scripting, inserting new field in the master header will not make the other staff1a, staff2a worksheets insert a the data column accordingly,

This is not "issue", as mentioned by Ali, updating of slave worksheets require coding

Rgds