1

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.

1
  • Yes it's possible,, use Named Range,,, Ctrl+F3, and anytime you can use it anywhere, with formula. Also Named Range should dynamic too. – Rajesh S May 4 '20 at 6:11
3

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:

enter image description here

(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':

enter image description here

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:

enter image description here

It will automatically reflect in the formula in the next worksheet:

enter image description here

There you go. I hope this helps :)

3
  • it's quit helpful,,, add one more,, how to create dynamic named range,, ☺ – Rajesh S May 4 '20 at 7:43
  • There it is, have a look :) – Sanu_012 May 4 '20 at 8:45
  • 1
    @Sanu_012,, you can even use any of the values ( or elements of an array ) from Named Range like =INDEX(VatRate,2) returns 15. And if use this ,,=SUM(VatRate,2):INDEX(VatRate,5)) will SUM B3:B6. – Rajesh S May 4 '20 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.