Yes, there is a way to achieve this and the process involves creation of "Named Range".
For reproducing your scenerio, I have made two excel sheets here - one of them named as "Settings". Let's say, you have a value of
VatRate equal to
20 in Settings!$B$2.
Now go to
Formulas tab and click
Name Manager. Click on
New and create your Named Range by giving it a name, scope of that name and the reference cell address:
(Be aware that you cannot use space in named range but can include underscore or period)
Click "OK" and close it. Now, head to another sheet in the same workbook where you want to use it, here I used 'Sheet2'.
Enter in the cell, a formula. For example,
=SUM(VatRate, 0). It will result in the Sum of the value of VatRate i.e. 20 and 0 which will output
20 in that cell in 'Sheet2':
ADDITIONAL: For Dynamic Named Range, you can do the same by tweaking this process a bit and using
OFFSET functionality. If you have a column named "Vat Rate" in the "Settings" sheet, and want to update the value in other sheets automatically when you enter a value in that range of cells, edit the Named Range and use this formula:
So, when you enter multiple values in that specified data range:
It will automatically reflect in the formula in the next worksheet:
There you go. I hope this helps :)