linking data from two spreadsheets automatically

[rdumas74]

Morning,

I have two spreadsheets I want to link automatically each month w/o going back and forth. First spreadsheet is data specific to a location, second spreadsheet I have just daily totals by month for multiple spreadsheets. Every month in the second spreadsheet I create a new tab for the specific month, than go back and forth line by line with the (=) sign linking to the first spreadsheet. Is there an easier way of doing this, maybe using a macro? I don't use macros but would love to start if this is the easiest way to do so. j
Thanks much fo rany help!:

[djapigo]

Hi rdumas,

It's hard to picture you question. Can you give us a spreadsheet(s) to show exactly what you do? You can give fake data.

Thanks,
Dennis

[FDibbins]

as mentioned above, hard to say, but after you link the 1st cell, have you tried to just copy that formula down as far as you need it?

[rdumas74]

I have attached the two spreadsheets. Oct 2012 spreadsheet is where all my site speadsheets are link to Site 3 is one of eight source spreadsheet. I usually link them with just the = and the path to the cell in the Site 3 spreadsheet. This is time consuming, each month and was hoping for something more automatic.

Thank you for any help given.

[djapigo]

Hi rdumas,

This might look messy, but it might do the trick. I've attached back your spreadsheets. (notice that I had to hardcode some of your formulas because the links was giving me junk.

Site 3.xlsmOct 2012.xlsm

Anyway, here's my take on how I would go about this.

A couple of assumptions that I'm making here...
1. Are your locations changing? If so, then we will have to use index/match instead of a simple vlookup.
2. For the 'Site 3.xlsm' spreadsheet, I'm assuming you add the next month on the next tab? If so, it's important that you simply copy the previous month. As you can see, I changed the tab name to October and added November (with fake data)

So this is what I did...
1. Name your "totals area" in 'Site 3' with something meaningful... in your case, I named cells A50:M54 as 'Site3' (this way, when you copy this tab to make November, this name follows
2. On the 'Oct 2012' spreadsheet, notice that I added something in B1, C1 (linked to A4), D1 and a formula in E1; also I added C3:G3 with just the week name so correspond with the named range in item 1
3. The VLOOKUP in cell C22 simply uses the INDIRECT formula on E1 to look up your values
4. Now if you change cell A4 in Oct 2012 spreadsheet to November, then it should pick up the values in the November tab

If nothing happens, press F9 to calculate... make sure Calculation is turned on to automatic, go to Options if needed.

The VLOOKUP can be changed to INDEX/MATCH if needed, especially if you move around the locations in different columns. There are many ways to attack this problem, hopefully you can build around this.

The key is the INDIRECT function.

Please let me know if you have further questions.

Take care,
Dennis

[rdumas74]

Thanks!! I"ll give it a try and let you know