Named Formula on Different Worksheets

[Cammandk]

I have a sheet layout that will be used for multiple sheets. I'm trying to create one master and then replicate from that and then change the new sheet name.

I have a formula in Sheet1!H1 that =if(A1 = "Yes","A","").
Sheet1!H1 has been named FRMStatus

I've tried creating this as a Name which works in the sheet created. However I want to use it in the other sheets. When I use the name FRMStatus in those sheets it keeps refering back to Sheet1 and therefore giving an incorrect result.

Ultimately I know I can create a formula for each specific sheet but I'm hoping there's a more efficient way that would work and allow me to change the formula in one place later for all the sheets.

DK

 

[NARAYANK991]

Hi David ,

If you want that the named range should refer to each worksheet separately , provided of course that the RefersTo formula is identical for every worksheet , then do the following :

In the definition of the named range FRMStatus , in the RefersTo box , enter :

=!$H$1

where the sheet name is not included.

Now , if you are in Sheet1 , the named range will refer to cell H1 on the Sheet1 tab ; if you use the named range on Sheet3 , it will refer to cell H1 on the Sheet3 tab.

Narayan

 

[Cammandk]

Hi Narayan

I had been trying this before posting but every time I say ok to just the cell reference the sheetno is added into the refers to field?

David

 

[Debraj]

Hi David,

Narayan's Suggestion is working fine for me..

Please check the attach..

Name : Deb
Scope : Workbook
Refers to : =IF(!$A$1="YES","A","B")