98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Linking multiple sheets, REF! Error
Linking multiple sheets, REF! Error
Resolved · High Priority · Version 365
Thu 27 Apr 2023, 11:06Delegate Ghilaine said...
Ghilaine has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Linking multiple sheets, REF! Error
Hi,
I'm setting up a master spreadsheet (which contains at least 60 other sheets) and 1 sheet contains 6 sources (linked sheets).
Here, the other sheets link to each region:
- Apac
-Europe
-MEA
-UK
-LATAM
-NAM
I have done all the linkages to get a real-time update on the figures for each region, but as soon as I close the source (i.e. APAC), it shows #REF! instead of the data/numbers.
Do you know how I can solve this?
It's very important because this master spreadsheet is used by 5 other people, and I cannot ask them to open 6 other sources to view the data.
Thanks,
G
Thu 27 Apr 2023, 11:43Trainer Jens said...
RE: Linking multiple sheets, REF! Error
Hi Ghilaine,
Thank you for the forum question.
You get #Ref! when the the reference is wrong. This can happen if the source files get moved to another folder, if the sheet names get changed, or if something else is wrong, when you reference the sources. unfortunately Excel do not support a number of tools when linking. It can also be you security settings, which not allow you to get the data, when the source is closed.
Sorry the error can be based a large number of issues.
I personally would never use link sheets. I have had so many issues doing it in the past.
A much better solution is to connect to the source files by using Power Query in Excel. The connection will be live connections and Power Query does not care if the file is open or not.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Thu 27 Apr 2023, 11:51Delegate Ghilaine said...
RE: Linking multiple sheets, REF! Error
Thanks Jens, very helpful to know!
Would you be able to give a few steps for how to use Power Query? or what should I look for on youtube.
Would power Query do the same thing?
First time i hear about it :)
Thank you.
G,
Thu 27 Apr 2023, 11:51Delegate Ghilaine said...
RE: Linking multiple sheets, REF! Error
Thanks Jens, very helpful to know!
Would you be able to give a few steps for how to use Power Query? or what should I look for on youtube.
Would power Query do the same thing?
First time i hear about it :)
Thank you.
G,
Thu 27 Apr 2023, 12:07Trainer Jens said...
RE: Linking multiple sheets, REF! Error
Hi Ghilaine,
I Power Query is a magical tool all Excel users should use.
Most of our clients could save up to 80% of the time they spend in Excel by getting knowledge of Power Query. You can automate everything and create sheets which update themselves when new records are added to the source.
I found a video which shows the issues by linking sheets and shows how to do it with Power Query.
https://www.youtube.com/watch?v=YnjcXFjtpa0
STL has a 1 day Power Query course if you want to know more and want to save a lot of time spent in Excel.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 28 Apr 2023, 11:10Delegate Ghilaine said...
RE: Linking multiple sheets, REF! Error
Yey! It worked!!!! Thanks SO much Jens.
A final 'best practice' question on this topic: do you advise on a limited number for data imports (sources/tables) via Power Query?
Or is it fine to upload as many as needed (approx 60)
Have a great weekend and thanks again!!
Best,
G
Fri 28 Apr 2023, 11:32Trainer Jens said...
RE: Linking multiple sheets, REF! Error
Hi Ghilaine,
I am very pleased to hear that it is working.
Always first think Power Query when you are doing Excel tasks. It is crazy how many things it can do and automate.
Max number of sources?
The only limitation is your PC. Power Query can handle billions of records and tables.
Well if you are using the 32 bit version of Excel max will be 1 gigabyte of data (millions of records) if you have the 64 bit version of Excel it is your installed memory size which will be the limitation. If you have 8 gigabyte RAM memory you can work with billions of records in millions of tables.
It is a good idea to have a fast PC, if you use huge data sets.
And a good weekend to you
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Sat 6 May 2023: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Hiding Formulae in the Formula BarIt is possible to protect the contents of a cell reference(s) from amendment by applying cell protection. The contents of the cell reference can also be stopped from displaying in the formula bar. |
