Ranges

Hello Sarah,

Hope you enjoyed your Microsoft Excel Advanced course with Best STL.

Thank you for your question regarding using range names.

Range names can be applied on any sheet within a workbook, however, cannot be used in another workbook. You would have to reset these.

If you had a worksheet that had a number of cells such as rate values which you wanted to refer to in other workbooks then you can create a name that refers to a worksheet cell in another workbook.

Let's say that cell A1 of a sheet called Rates contained the current VAT rate of 20% in a Workbook called Budget. You enter data in a different Workbook called Sales Data and you have created a formula that calulates VAT on the sales and you want the formula to refer to cell A1 in the Budget Workbook.

From the Sales Data Workbook, go to the Formulas ribbon, then click Define Name from the Defined Names group. Type the name i.e. Vat then in the Refers to: box click the button on the right-hand side then double click the Budget Workbook on the taskbar. This will allow you to click cell A1 in this workbook which will then create a named cell from the Budget Workbook which you use in the Sales Data Workbook.

If the Vat rate is updated in the Budget Workbook then all formulas using that named cell will automatically be updated.

Give this a try!!!

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer