Dedicated Formula tab

**MarcLiq** · 10-06-2009, 12:11 PM

I would like to have a tab in a workbook dedicated to formulas. I have a timesheet with common formulas across multiple tabs and I have heard that it is possible to save space by having information on the weekly tabs reference a common formula on a separate tab and return a result on the weekly tab.

Any guidance or help would be appreciated.

**Cheeky Charlie** · 10-06-2009, 01:15 PM

I have not come across this, but have come across defining formulae as names - could this be what you're referring to?

**MarcLiq** · 05-22-2010, 03:00 PM

This sounds like it. Can I have all my named formulas on one page and then refer to them on different sheets? I have tried this but run into problems using the same name on more than one sheet. I think using something called a "dynamic named range" might be what I am trying to do but I can't find any decent instructions on how to do it.

Thanks,

**arthurbr** · 05-22-2010, 03:09 PM

There must be thousands of posts on this forum about Dynamic ranges, try http://www.contextures.com/xlNames01.html#Dynamic

**Cheeky Charlie** · 05-23-2010, 04:12 PM

For instructions on DNRs the above contextures link, or ozgrid.com.

I don't think, however, that dynamic named ranges is what you're looking for. I think it would make the most sense if you could provide examples of the formulae you are trying to manipulate and how you would envisage it working

**royUK** · 05-23-2010, 04:34 PM

I think you want to use Named Formulas. You can create a name that holds a value.

In Excel 2007 & above open the names Dialog from the Formulas tab. Click Define Names.

To create a formula to add the current UK VAT rate follow these steps

Name Box; VAT
Scope:Workbook
Comment: Standard Tax
Refers to: =117.5

In any cell type =100*VAT. This will add the VAT to100,i.e.117.50

The name Manager in the same tab allows you to edit the names easily