Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 12th, 2009, 11:41 AM
Registered User
Join Date: Sep 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default apply named cell from another worksheet to existing formula


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?

Old September 18th, 2009, 01:01 AM
Authorized User
Join Date: Mar 2008
Posts: 74
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via ICQ to sektor

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. :)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Translating WorkSheet Formula in VBA AndyL Beginning VB 6 2 June 11th, 2008 04:13 AM
Using Formula on Active Cell manojkumarsoni Excel VBA 3 August 26th, 2007 08:56 AM
Exporting to Existing Excel Worksheet from Access kfs Access VBA 2 August 3rd, 2006 10:56 AM
Accessing a Pre-existing Shape on a Worksheet excelthoughts C# 0 June 21st, 2006 02:00 AM
What would the formula "=+D4" do in a cell? BrianWren Excel VBA 2 February 2nd, 2006 03:36 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.