Linking 2 workbooks and using linked cells in calculations

[Paris2016]

Hello.

I am fairly new to building complex Excel spreadsheets so I could really use your help

This is also my first question, so if there's anything I forget please let me know so I can do better in the future

My first project on my new job is to create a daily Sales document that I want to link to yesterday’s version of the same document for calculating changes between yesterday and today.

There are several tabs for several different points of Sales and 1 sheet combining all together for a total.

Per sheet I would like to do it by product. Each tab has the same number and sequence of products.

Unfortunately I’m not at all sure how to approach this.

My main goal is that by linking the 2 worksheets, I would not need to update the formulas every day. The links would be needed in the formula.

I am using Excel version 2013 and am not very familiar with VBA.

Honestly, I don’t even know where to start.

Is there any (fairly) simple solution that you could recommend me? Or even a starting point?

I would be extremely grateful for your help and advice on how to handle this project!!!

If needed I will upload what I have so far. Let me know.

Many thanks in advance!

 

[James006]

Hi,

In your message, there is nothing really precise ...

For your general knowledge ...take a look at https://support.office.com/en-us/ar...workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5

HTH

 

[miless2111s]

Can you be precise about the file(s) you're dealing with?

Is there a separate file that contains yesterday's figures or are they added to a new worksheet in the same workbook? In either case do they have the same structure in terms of either their file name or tab name?

If you have a separate file (TEST-30-01-2016) you can call a cell (F1) contents into your other sheet by using:
='[TEST-30-01-2016.xlsx]Entry'!$F$1
Thus if you have a standard format you could use the indirect commend to "build" that file name
=indirect("'[TEST-&something to build the date ref&".xlsx]Entry'!$F$1

The date build could be something like
A1: =today()-1
A2: =day(A1)&"-"&month(A1)&"-"&year(a1)
Call $A$2 into the indirect formula above.

Copy the formula down to cover all the cells you want to import from yesterday into a "yesterday" sheet and away you go.

Hope that helps

Miles

 

[Paris2016]

Hi,

In your message, there is nothing really precise ...

For your general knowledge ...take a look at https://support.office.com/en-us/ar...workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5

HTH

Hello James,

I know that I am not being awfully precise, but I am struggling to define my problem.
I did have a look at that exact same page before asking for help on mrexcel but it did not help me very much if I am honest.
I tried connecting the two worksheets and have managed to establish a connection for each tab. But once this is done I don’t know how to “use” the connection for my formulas.
The main point of creating the document that way is that the formulas should also update when I change the source document. For example, when I want to create todays document, I want to take yesterday’s document, save it under today’s name, change the source to yesterday’s file and then update the new file for today with the new data. There will be some formulas to calculate changes between yesterday and today.
I don’t know if I am being clear.
I really appreciate your help on this!!!
Thank you so much for replying

 

[Paris2016]

Hello Miles,

Yesterday’s figures will be a separate file, I do not want to add them to the new workbook.
I only plan to use them for calculations, they will not need to actually appear in the new workbook.
Yes, both files have the same structure in terms of tab name. The file name will include the date so it won’t be the same, but a similar structure.
I will experiment with your suggestions and let you know.

Already many thanks for your kind help!

 

[miless2111s]

Hello Miles,

Yesterday’s figures will be a separate file, I do not want to add them to the new workbook.
I only plan to use them for calculations, they will not need to actually appear in the new workbook.
Yes, both files have the same structure in terms of tab name. The file name will include the date so it won’t be the same, but a similar structure.
I will experiment with your suggestions and let you know.

Already many thanks for your kind help!

The only real reason for pulling the figures into the sheet would be to allow you to visually check that they're right etc. Personally I always feel more comfortable being able to do this even if the sheet is hidden.
The file name structure will always have to be the same for this to work. The other way would be to have a cell which contains the file name to look for but that would mean that you'd have to edit the sheet once per day, but it would allow for changes in the format of the file name etc
Good luck
Miles

 

[Noodleski]

Why not build an incremental file? Keep all your sales data centralised into one file. Otherwise you will need to open the file from yesterday to recalculate that of today.