Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-07-2021, 01:05 PM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default Problem referencing a bookmark in table formula

Hi all,



I have an invoicing template that I have been using for a number of years that works pretty well and it will figure out the shipping charges based on a simple formula.

=if(c7>499.99,10.95,IF(c7>199.99,7.95,4.95))

With c7 being the product totals.

But I also have some customers that get free shipping. In a different table on the template I have used a dropdown box to choose between First Class Mail, Priority Mail, UPS, NA (for pick up items) and Free.

I'd like to have the invoice template enter "NA" in the Shipping box instead of having to click on it and type NA.

I tagged the dropdown list with the bookmark "Ship".

I modified the shipping charge formula as:

=if{Ship}="Free","NA",if(c7>499.99,10.95,IF(c7>199 .99,7.95,4.95)))

The formula result (when I Alt-F9) is "!Syntax Error, {"

I suspect it is something to do with the Content Control, but don't know how to work through that. I gave it the bookmark "Ship" but it also has it's own Tag (Shipping).

Can someone tell me what I am doing wrong?

Thanks in advance.
Reply With Quote
  #2  
Old 01-07-2021, 01:37 PM
macropod's Avatar
macropod macropod is offline Problem referencing a bookmark in table formula Windows 10 Problem referencing a bookmark in table formula Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

See Reference Table Cells From Outside The Table in my Microsoft Word Field Maths Tutorial, which you can find in a 'Sticky' thread at the top of the Word forum: https://www.msofficeforums.com/word/...-tutorial.html
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-07-2021, 04:36 PM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default

I read that before I posted the question. I have a copy of your tutorial saved on my hard drive from 2015! I refer to it quite often.



"Only the following functions can accept references to table cells as arguments from outside that table:
AVERAGE(), COUNT(), MAX(), MIN(), PRODUCT(), and SUM()"



Is this my problem, using IF to look at the bookmark? I was not sure if that was the limitation or if it was something in the content control for that field.


Which is why I decided to post the question. I'm stumped, even with the tutorial.
Reply With Quote
  #4  
Old 01-07-2021, 07:17 PM
macropod's Avatar
macropod macropod is offline Problem referencing a bookmark in table formula Windows 10 Problem referencing a bookmark in table formula Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

To be able to refer to the bookmark directly, you'd need to ensure it doesn't include the end-of-cell marker. See the Testing and Returning Cell Contents in Tables topic for an explanation.

As for the syntax, it should be:
{IF{REF Ship}= "Free" "NA" {=IF(C7<200,4.95,{=IF(C7<500,7.95,10.95)})}}
Note the required spaces.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 01-10-2021, 03:39 PM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default

Nothing I did worked. Used the format you suggested and it returned "0". Made a few modifications, never did anything except returning zero.


So rather than keep pursuing that I decided to merge the tables so I did not have to refer to a cell outside the table.


Tried to use-


{=if(d2="Free","NA",if(c10>499.99,10.95,IF(c10>199 .99,7.95,4.95)))}


Where d2 was the cell with the shipping drop down menu. Syntax error.


No amount of playing with the format worked as expected. I tried to use the tag as a label (the content control has the tag "Shipping"), it didn't help.



{=if({Shipping}= "Free" "NA" {if(c10<200,4.95,{=if(c10<500,7.95,10.95)})}}


This formula doesn't show anything as a result, but at least it is not a syntax error.


I added a bookmark to the cell d2 (Ship)


{if({Ship}= "Free" "NA" {if(c10<200,4.95,{=if(c10<500,7.95,10.95)})}}


Syntax error.


{if({ref Ship}= "Free" "NA" {if(c10<200,4.95,{=if(c10<500,7.95,10.95)})}}


Syntax error.


I can bookmark the whole cell but I can't bookmark just the controlled content part, it is greyed out when I click "Insert bookmark" (with the dropdown box selected).


I must be making a fundamental mistake but can't figure out what it is.


Also when I am reading your tutorial document it comes up as Compatibility mode for me and all the internal links do not function. Also the MS KB211253 linked in the doc is no longer available.


Please help, I'm really not grokking this.

Reply With Quote
  #6  
Old 01-10-2021, 03:51 PM
macropod's Avatar
macropod macropod is offline Problem referencing a bookmark in table formula Windows 10 Problem referencing a bookmark in table formula Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Without actually seeing the problem document, it can be difficult for anyone to diagnose the issue. Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 01-11-2021, 12:17 AM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default

It's really silly that I am so obsessed with this. I know the amount of time I will save getting this to work is probably not ever going to equal the amount of time I have put into it (probably 8 hours or more so far).


But I want to learn and understand!


From reading Gregory Maxey's website I am starting to think my version of Office (2007) can't do it. He says the only way to use variable content without VBA code is to use the legacy Drop Down Form Fields. It has a checkbox "Calculate on Exit", so I was thinking this would fix the drop down selection and allow me to use it in the next formula.


But when I created this type of drop down cell I can't get it to be a drop down form. Per his notes- "Documents containing formfields “MUST” have editing restriction (protection) applied to enable the formfields."


I go to "Protect Document" and the editing restrictions (and everything else) are greyed out. Apparently I am not allowed to protect my documents...


It's late, I will attach the DOTM to a post tomorrow.


Mark
Reply With Quote
  #8  
Old 01-11-2021, 07:58 PM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default

Well, figured out why I am unable to protect a document. It's because I am using mailmerge to import customer name/address to the invoice. So that's a dead end, unless I figure out another way to import customers into my invoices (another long project I am sure). But using mailmerge gives me a way to search for and import customer details.


Here is the basic invoice template. Without the linked customer file obviously...


Any ideas would be great.


Mark
Attached Files
File Type: dotm Invoice.dotm (30.7 KB, 5 views)
Reply With Quote
  #9  
Old 01-11-2021, 10:00 PM
macropod's Avatar
macropod macropod is offline Problem referencing a bookmark in table formula Windows 10 Problem referencing a bookmark in table formula Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Apart from the fact your document has no:
• 'Shipping' bookmark for the field code to reference; and
• field coding for the referencing,
I don't see the problem. See attached.
Attached Files
File Type: dotm Invoice.dotm (32.7 KB, 10 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 01-13-2021, 02:03 PM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default

Paul,


I'm sorry, I had so many different templates I was working on I selected the wrong one to share.


This one has a test formula in cell F2 of the main body table. It refers to the bookmark "Shipmethod" in cell D2, the result is "No" for all of the dropdown selections. I expect "NA" for ship method "Free".


I am not going to complicate it with a formula adding the tiered shipping charges until I understand why the first part isn't working.


Thank you for helping,


Mark
Attached Files
File Type: dotm Invoice.dotm (43.6 KB, 4 views)

Last edited by madkiwi; 01-13-2021 at 02:15 PM. Reason: Error in attachment
Reply With Quote
  #11  
Old 01-13-2021, 02:07 PM
macropod's Avatar
macropod macropod is offline Problem referencing a bookmark in table formula Windows 10 Problem referencing a bookmark in table formula Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The problem with your latest template is that you have bookmarked the entire cell containing the content control; not just the content control.

Check out the template attached to my previous post - which also includes the working formula.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 01-16-2021, 11:44 AM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default

Well, as I don't know shite about VBA and never learned programming or even how macros work it's Greek to me.


I'll just live with what I have. It works.


Mark
Reply With Quote
  #13  
Old 01-16-2021, 02:14 PM
macropod's Avatar
macropod macropod is offline Problem referencing a bookmark in table formula Windows 10 Problem referencing a bookmark in table formula Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

This has nothing to do with VBA (aka macros)...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #14  
Old 01-16-2021, 06:21 PM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default

Yes, further proof I have no idea what I'm doing.


Thank you for your help, but I'm a one-man business owner and don't have the luxury of learning all this. So, as mentioned above, I'm out.
Reply With Quote
  #15  
Old 01-18-2021, 07:40 PM
madkiwi madkiwi is offline Problem referencing a bookmark in table formula Windows 7 Problem referencing a bookmark in table formula Office 2007
Novice
Problem referencing a bookmark in table formula
 
Join Date: Jan 2010
Posts: 11
madkiwi is on a distinguished road
Default

Yeah, NOT SOLVED, so whoever marked it as such, stop it.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem referencing a bookmark in table formula Formula for Cross Referencing Numbered List hiwire03 Word 9 08-09-2020 12:01 PM
Referencing Entire Columns in Excel Formula w/ Conditional Fomating OldIronBoots Excel 1 06-18-2018 03:42 AM
Problem referencing a bookmark in table formula NEED: Formula for referencing data in two files ross.lowe Excel 1 02-21-2017 09:34 AM
Naming Cells for formula referencing lynchbro Excel 6 06-26-2014 07:45 AM
Referencing a value over 255 characters within a table PLUS strange formula behaviour TishyMouse Excel 2 01-08-2013 09:39 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:30 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft