asked on

Problem with updating of text from another text box when not on the same sheet.

I have a date field that will go on multiple pages of a drawing and anytime the drawing is modified I need to update the date field. I would like to only have to do this on the first page and then have the other pages updated automatically. My first page is named A and the shape on page A that contains the date is named sheet.111. On the 2nd page which is name B, I have added a rectangle and selected insert field, then custom formula. For the custom formula I am using =SHAPETEXT(Pages[A]!Sheet.111!TheText) but when I press OK it tells me there is an error in the formula. Looking in 2 different help forums this custom formula has worked but form some reason I cannot get it to work. Any ideas what I'm doing wrong?

ASKER CERTIFIED SOLUTION

Scott Helmers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chrome 4ever

ASKER

I'm not familiar with the use of background pages to update common fields between sheets but let me look at into it. I chose the method I was using because that was the first method I found when looking online. Let me try the background method and see how that does, but I would still like to know why my formula isn't working. Based on what I have seen and you confirming it, it should work.

Scott Helmers

Let me know whether a background page solves the problem. You shouldn't have any trouble tracking down online help about their use.

Re your formula: can you post the diagram here? Or perhaps a simplified version that exhibits the same problem? I suspect the problem is the internal difference between Name and something called NameU but before I go down that path, I'd like to take a look.

chrome 4ever

ASKER

Instead of sending you the large file I am working with I made a smaller 2 sheet doc with just the text box where I manually enter the data and then the 2nd sheet where it should pull automatically from the first sheet's text box. In creating this I was able to get the formula to work. I even went back and renamed the 2 sheets and it still works. Not sure why it doesn't work in my original but I at least know it works as it should. Thank you for your help on both. The background method works well for what I need.

Scott Helmers

I have found the Pages[ ] function can be finicky -- I'd like to be more precise than that but can't! -- at times. Regardless, I'm glad you can successfully use the formula you posted. I also think that bg pages will be a better solution and am glad it looks that way to you.

One more note about background pages: you can only assign a background page to one foreground page at a time, which is really annoying. If your diagram contains a lot of pages and you'd like a VBA macro to assign a bg to all foreground pages, just let me know. It's simple code that I've used multiple times in the past.