• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sheet Name keeps adding to Named formula

C

Cammandk

Member
I have a worksheet "SCH5" that I have created to serve as a template - so that I can copy and rename for further SCH'S" within the workbook.

I have tried to create a named formula in SCH5 - "FRMStatus" -

IF('SCH5'!AQ16="PAID","P",IF('SCH5'!I16="F","F",IF('SCH5'!I16="o","A",IF('SCH5'!I16="p","P",IF('SCH5'!I16="b","A",IF('SCH5'!I16="I","U",IF('SCH5'!I16="S","U",IF('SCH5'!I16="e","U",IF('SCH5'!I16="r","P",IF('SCH5'!I16="r2","P",""))))))))))

Everytime I save it the 'SCH5' keeps being added to the cell references.

When I now duplicate the worksheet and rename new sheet to SCH6 Excel creates another local sheet name of FRMStatus.

I was hoping to be able to create just one version of the formula that could be used across the 30 odd worksheets and if needed could be changed in just one place.

Help would be greatly appreciated as this is a pretty core need for what I am trying to achieve.

DK
 
So you want it without the 'SCH5'! prior to each cell reference in your if equations? I tried and cannot duplicate. Upload a sample so we can see what you're dealing with.

If you need an easy way to remove part of the equation, use a replace all - press ctrl-F, choose replace, type 'SCH5'! in the find what box and leave the replace with box empty.
 
@Cammandk

Put like below in your name range formula
=IF(INDIRECT(MID(CELL("FILENAME"),SEARCH("]",CELL("FILENAME"))+1,256)&"!"&"$AQ$16") = "PAID","P","")

Modify this. Name range will take reference of sheet name automatically

Advise if any issue.

Regards!
 
Try dropping the Sch5, reference from the formula, in that way the formula can be copied to other sheets and will refer to the sheet it is on

Kanti
 
Hi David ,

I am not able to reproduce your example :

1. I created a named range FRMStatus on the SCH5 worksheet with the definition you have given

2. I made a copy of the above sheet , and renamed it SCH6

3. When I go the Name Manager , I do not see any additional named ranges other than the first one that I created.

What you have mentioned happens only if the scope of the named range is SCH5 instead of Workbook ; if while creating the named range you modify the scope to Workbook , you will get exactly one copy of the named range.

Narayan
 
You must log in or register to reply here.
Back
Top