Hi,

I am trying to apply named cell from another worksheet to existing formula.

For example I have a formula in sheet2 which looks like =sheet1!A1+sheet1!B1. I then subsequently name sheet1!A1 = Jan and sheet1!B1 = Feb. Thinking that if I apply these names the formula in sheet2 would look like =Jan+Feb. However I get the message “Microsoft Office Excel cannot find any references to replace” when I try to apply the names using Excel menu “Insert – Name – Apply”. Am I doing anything wrong and is there a way to resolve this problem?

Thanks.

You didn't write your actual steps in defining names. Working with names is tricky because you can have two types of names: global(workbook) and local(worksheet). You can put anything into your name - not only a range. For example, if you use some multiplier across entire workbook and you know that it changes frequently, you can put this multiplier into name. When time comes to change, you will need to change it only in one place - in Name Manager.

Here's catch: if you have two names with same "name", then here's how Excel decides what name to choose: if you don't prefix your name with worksheet's name, then Excel uses GLOBAL name, but if you prefix it, then Excel uses LOCAL name.

Thus, if the scope of your name is worksheet, then you can use it ONLY in a worksheet it was defined in. If you defined name with help of address box (on the left side of Formula bar) AND prefixed it with your sheet's name, then you get LOCAL name and thus you can't use in another sheet.

Hint: You can create hidden name (and worksheet too). You won't see it in Name Manager, but it will exist. It's done thru VBA. :)