apply named cell from another worksheet to existing formula

[wkt20]

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.

 

[pgc01]

Hi wkt20
Welcome to the board

You did not say if the names are worksheet names or workbook names.

If they are workbook names, you should be able to use

=Jan+Feb

If they are worksheet names, you must qualify them

=Sheet1!Jan+Sheet1!Feb

 

[wkt20]

Thanks for your reply. They are worksheet name. Somehow applying the named cells does not work to existing formulas. I can use the named cells in new formula but Excel seems NOT to be able to apply the names from OTHER worksheet retrospectively to existing formula. (However applying names in the SAME worksheet retropectively works). I don't understand this, please help....

 

[pgc01]

The Apply Names only applies names from the current sheet or workbook names. If you have edited formulas before the worksheet names in other sheets were defined, then you must replace them manually or using Replace.

Is this case you should be very careful. If you decide to replace automatically Sheet1!A1 by Sheet1!Jan in some formulas, make sure those formulas don't refer to addresses like Sheet1!A12 or Sheet1!A100:B200.

 

[The Horse]

Has anybody developed an add-in to solve this problem in Excel 2010 ?

It's funny - search the internet and it's very difficult to get anybody to admit this problem in Excel exists ! Comb the books by Bill J, John Walk etc - and no mention...

For the record (and I'm currently trying to do this) if you define a range name in one worksheet, and attempt to retrospectively apply the name (using Apply Name) to formulae in another worksheet (of the same workbook) it can't do it (Excel provides error message, without explaining why).

Anybody able to enlighten me ?

Thanks

Mick the Horse