Getting a named value in Excel - like a global variable

Question

I often have a sheet in my Excel spreadsheets, that contain some global values I use all over. for example a VAT rate or a currency exchange rate.

I can access one of these "global values" something like this:

Settings!$B$2

Say that cell contains the global VAT rate.

Is it possible to easily assing a variable name like VATRATE, so I could just type $VATRATE (or some other named syntax) to use it in a formula - but it would still be updated from cell Settings!$B$2.

Answer 1

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:

=OFFSET(Settings!$B$2,0,0,COUNTA(Settings!$B:$B),1)

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 :)