Networking WorkBooks Together

[Dillonm92]

Okay so my team has 3 sites we cover. Each with it's own metrics Excel Workbook, but everything is the same except for the site name. That's it. They have 12 tabs each for a total of 36 tabs total. That being said, those workbooks are on our team share (network) drive. I know I can use "=" in one workbook and then click in another workbook to link those particular cells together across the sheets. The issue I have is that I am making a separate WorkBook for my boss that has all three sheets combined into one so he has everything in one place. I know I can do it by linking each cell of data individually, but with the amount of information we have, that would take an astronomically long time to do it individually. I have KuTools but can't find an option that works to apply the "=" sign at once to all the cells I need, then click over into the WorkBook I am making for my boss. Anyone have tips on a quicker way to accomplish this? The idea is that as my team at each site enters data, it populates into the dashboard for my boss. I've field tested it with individual cells and it works, but I can't figure out how to apply the "=" sign for it en masse. Any help is appreciated. Thank You!

 

[6StringJazzer]

If you link cell A1 to A1 of another workbook/sheet, then just remove the $ signs and fill the formula to the right and down as many cells as needed. This only takes a few seconds for one sheet but you will have to do this for all 36 sheets, unless you write VBA to do it.

 

[Dillonm92]

If you link cell A1 to A1 of another workbook/sheet, then just remove the $ signs and fill the formula to the right and down as many cells as needed. This only takes a few seconds for one sheet but you will have to do this for all 36 sheets, unless you write VBA to do it.

I have no idea how to write a VBA for it. Do you have any insight to where/how I can do so?

 

[Dillonm92]

If you link cell A1 to A1 of another workbook/sheet, then just remove the $ signs and fill the formula to the right and down as many cells as needed. This only takes a few seconds for one sheet but you will have to do this for all 36 sheets, unless you write VBA to do it.

It does not seem to be working for me. All it does is put a 0 in one of the sheets and when I try and fill it, it fills with numbers and info I have never seen before.

 

[6StringJazzer]

This solution does not require VBA.

If you have blank cells they will be returned as 0. So if you have blanks cell in the range you are trying to mirror then try this enhancement:

=IF('[Your other file name.xlsm]Sheet Name'!A1="","",'[Your other file name.xlsm]Sheet Name'!A1)

"numbers and info I have never seen before." Of all the things that could go wrong, this one makes no sense to me. Data has to come from somewhere.

If you still have trouble I can post an example set of files.