Selection/Range different in Word 2010 ???

I'm testing my code on Office 2010 (latest Beta). Most everything
works, except i'm hitting a strange problem in one of my functions
that inserts text (piece by piece) into  a doc.

I'm calling this function from another function that takes a string
with markup in it, e.g.

	"Name, G. (2002). <i>Title of paper</i>. Santa Cruz, CA."

and turning it into a paragraph. I "walk" through the string, breaking
it into pieces that have or don't have markup. And then I insert each
piece at the end of a paragraph; if the piece has markup, then the
function tells Word to apply its font formatting (e.g. make the text
italic if the original string markup was <i>...</i>)

This function works fine in Word 2007, Word 2003, Word 2000, and Word
97. It's broken in Word 2010. In debugging, I know where it's broken,
but i'm not sure how to fix it. 

The code for the  function is below. Where it is broken is in the
statement:
	Set rng = Selection.Range

In Word 2007, after that statement, rng.Text is the value of the
"theText" parameter that I've just added to the end of the selection.

In Word 2010, after setting the rng, rng.Text is empty.

I'm guessing I've been doing something wrong all along and getting
away with it in past versions, but i don't know what.  Any help
appreciated.

-------------------------------------------------------------------------------
Public Sub InsertTextAfterSelection(ByVal theText As String, _
                                    ByVal theStyle As String, _
                                    ByVal theAttr As Integer)
    Dim rng As Range
    
    Selection.Collapse direction:=wdCollapseEnd
    Selection.InsertAfter theText
    If theStyle <> "" Then
        Selection.Style = theStyle
    End If
    Set rng = Selection.Range
    rng.Italic = False
    rng.Bold = False
    rng.Underline = False
    rng.Font.Superscript = False
    Select Case theAttr
        Case eTextAttrItalic
            rng.Italic = True
        Case eTextAttrBold
            rng.Bold = True
        Case eTextAttrUnderline
            rng.Underline = True
        Case eTextAttrSuperScript
            rng.Font.Superscript = True
        Case Else
            'do nothing
    End Select
End Sub
0
Gary
6/4/2010 7:57:30 PM
word.vba.general 1023 articles. 1 followers. Follow

5 Replies
3979 Views

Similar Articles

[PageSpeed] 23

Hi Gary,

I'm not aware of any difference in this area between 2010 and previous 
versions. Right now I'm not at the computer that has 2010 so I can't test 
until later (and I have the final, RTM version from MSDN rather than the 
beta). But I'll suggest that, for all versions, you should replace this 
section of your macro

    Selection.Collapse direction:=wdCollapseEnd
    Selection.InsertAfter theText
    If theStyle <> "" Then
        Selection.Style = theStyle
    End If
    Set rng = Selection.Range

with this...

    Set rng = Selection.Range
    rng.Collapse direction:=wdCollapseEnd
    rng.Text = theText
    If theStyle <> "" Then
        rng.Style = theStyle
    End If

and move the Selection.Collapse direction:=wdCollapseEnd statement to the 
end, just before End Sub.

The statement rng.Text = theText guarantees that rng will cover the inserted 
text, and everything after that should just work.

-- 
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so 
all may benefit.

Gary Hillerson wrote:
> I'm testing my code on Office 2010 (latest Beta). Most everything
> works, except i'm hitting a strange problem in one of my functions
> that inserts text (piece by piece) into  a doc.
>
> I'm calling this function from another function that takes a string
> with markup in it, e.g.
>
> "Name, G. (2002). <i>Title of paper</i>. Santa Cruz, CA."
>
> and turning it into a paragraph. I "walk" through the string, breaking
> it into pieces that have or don't have markup. And then I insert each
> piece at the end of a paragraph; if the piece has markup, then the
> function tells Word to apply its font formatting (e.g. make the text
> italic if the original string markup was <i>...</i>)
>
> This function works fine in Word 2007, Word 2003, Word 2000, and Word
> 97. It's broken in Word 2010. In debugging, I know where it's broken,
> but i'm not sure how to fix it.
>
> The code for the  function is below. Where it is broken is in the
> statement:
> Set rng = Selection.Range
>
> In Word 2007, after that statement, rng.Text is the value of the
> "theText" parameter that I've just added to the end of the selection.
>
> In Word 2010, after setting the rng, rng.Text is empty.
>
> I'm guessing I've been doing something wrong all along and getting
> away with it in past versions, but i don't know what.  Any help
> appreciated.
>
> -------------------------------------------------------------------------------
> Public Sub InsertTextAfterSelection(ByVal theText As String, _
>                                    ByVal theStyle As String, _
>                                    ByVal theAttr As Integer)
>    Dim rng As Range
>
>    Selection.Collapse direction:=wdCollapseEnd
>    Selection.InsertAfter theText
>    If theStyle <> "" Then
>        Selection.Style = theStyle
>    End If
>    Set rng = Selection.Range
>    rng.Italic = False
>    rng.Bold = False
>    rng.Underline = False
>    rng.Font.Superscript = False
>    Select Case theAttr
>        Case eTextAttrItalic
>            rng.Italic = True
>        Case eTextAttrBold
>            rng.Bold = True
>        Case eTextAttrUnderline
>            rng.Underline = True
>        Case eTextAttrSuperScript
>            rng.Font.Superscript = True
>        Case Else
>            'do nothing
>    End Select
> End Sub 


0
Jay
6/4/2010 8:59:18 PM
Hi Jay,

Thanks for your help.

Unfortunately, that didn't change anything, but I now see what it is
doing, though I still don't see why. The result that's getting
generated has my "chunks" in reverse order, e.g. when I process this
marked up string:
      Gary Hillerson. (2002). <i>Title of my work</i>.  NY: Guilford.

While debugging i see my function get called three times, as it
should:
1) theText = "Gary Hillerson. (2002). ", attr=0
2) theText = "Title of my work. " attr = 1
3) theText = "NY: Guilford."

And the result I get is
NY: Guilford. Title of my work. Gary Hillerson. (2002).

And the title is not italicized.

Clearly something fundamental is going wrong, as if wdCollapseEnd is
not working correctly.  I've gotta go to a meeting for a few hours,
will be back at it later. If you have any further insights, let me
know.

And thanks Again

- gary

On Fri, 4 Jun 2010 16:59:18 -0400, "Jay Freedman"
<jay.freedman@verizon.net> wrote:

>
>Hi Gary,
>
>I'm not aware of any difference in this area between 2010 and previous 
>versions. Right now I'm not at the computer that has 2010 so I can't test 
>until later (and I have the final, RTM version from MSDN rather than the 
>beta). But I'll suggest that, for all versions, you should replace this 
>section of your macro
>
>    Selection.Collapse direction:=wdCollapseEnd
>    Selection.InsertAfter theText
>    If theStyle <> "" Then
>        Selection.Style = theStyle
>    End If
>    Set rng = Selection.Range
>
>with this...
>
>    Set rng = Selection.Range
>    rng.Collapse direction:=wdCollapseEnd
>    rng.Text = theText
>    If theStyle <> "" Then
>        rng.Style = theStyle
>    End If
>
>and move the Selection.Collapse direction:=wdCollapseEnd statement to the 
>end, just before End Sub.
>
>The statement rng.Text = theText guarantees that rng will cover the inserted 
>text, and everything after that should just work.
0
Gary
6/4/2010 9:53:18 PM
I get exactly the same in 2007 and 2010 (RTM).

-- 
Enjoy,
Tony

 www.WordArticles.com

"Gary Hillerson" <garyh@hillysun.net> wrote in message 
news:5vli06h247dschpd6u5s3k46jgq60587ps@4ax.com...
> I'm testing my code on Office 2010 (latest Beta). Most everything
> works, except i'm hitting a strange problem in one of my functions
> that inserts text (piece by piece) into  a doc.
>
> I'm calling this function from another function that takes a string
> with markup in it, e.g.
>
> "Name, G. (2002). <i>Title of paper</i>. Santa Cruz, CA."
>
> and turning it into a paragraph. I "walk" through the string, breaking
> it into pieces that have or don't have markup. And then I insert each
> piece at the end of a paragraph; if the piece has markup, then the
> function tells Word to apply its font formatting (e.g. make the text
> italic if the original string markup was <i>...</i>)
>
> This function works fine in Word 2007, Word 2003, Word 2000, and Word
> 97. It's broken in Word 2010. In debugging, I know where it's broken,
> but i'm not sure how to fix it.
>
> The code for the  function is below. Where it is broken is in the
> statement:
> Set rng = Selection.Range
>
> In Word 2007, after that statement, rng.Text is the value of the
> "theText" parameter that I've just added to the end of the selection.
>
> In Word 2010, after setting the rng, rng.Text is empty.
>
> I'm guessing I've been doing something wrong all along and getting
> away with it in past versions, but i don't know what.  Any help
> appreciated.
>
> -------------------------------------------------------------------------------
> Public Sub InsertTextAfterSelection(ByVal theText As String, _
>                                    ByVal theStyle As String, _
>                                    ByVal theAttr As Integer)
>    Dim rng As Range
>
>    Selection.Collapse direction:=wdCollapseEnd
>    Selection.InsertAfter theText
>    If theStyle <> "" Then
>        Selection.Style = theStyle
>    End If
>    Set rng = Selection.Range
>    rng.Italic = False
>    rng.Bold = False
>    rng.Underline = False
>    rng.Font.Superscript = False
>    Select Case theAttr
>        Case eTextAttrItalic
>            rng.Italic = True
>        Case eTextAttrBold
>            rng.Bold = True
>        Case eTextAttrUnderline
>            rng.Underline = True
>        Case eTextAttrSuperScript
>            rng.Font.Superscript = True
>        Case Else
>            'do nothing
>    End Select
> End Sub 

0
Tony
6/5/2010 12:07:54 AM
Sorry, that was my mistake. At the end of the sub, instead of just
collapsing the Selection (which hasn't been moved from where it was
before the sub started), put this:

   rng.Select
   Selection.Collapse direction:=wdCollapseEnd

That will set things up for the next call.

--
Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.


On Fri, 04 Jun 2010 14:53:18 -0700, Gary Hillerson
<garyh@hillysun.net> wrote:

>Hi Jay,
>
>Thanks for your help.
>
>Unfortunately, that didn't change anything, but I now see what it is
>doing, though I still don't see why. The result that's getting
>generated has my "chunks" in reverse order, e.g. when I process this
>marked up string:
>      Gary Hillerson. (2002). <i>Title of my work</i>.  NY: Guilford.
>
>While debugging i see my function get called three times, as it
>should:
>1) theText = "Gary Hillerson. (2002). ", attr=0
>2) theText = "Title of my work. " attr = 1
>3) theText = "NY: Guilford."
>
>And the result I get is
>NY: Guilford. Title of my work. Gary Hillerson. (2002).
>
>And the title is not italicized.
>
>Clearly something fundamental is going wrong, as if wdCollapseEnd is
>not working correctly.  I've gotta go to a meeting for a few hours,
>will be back at it later. If you have any further insights, let me
>know.
>
>And thanks Again
>
>- gary
>
>On Fri, 4 Jun 2010 16:59:18 -0400, "Jay Freedman"
><jay.freedman@verizon.net> wrote:
>
>>
>>Hi Gary,
>>
>>I'm not aware of any difference in this area between 2010 and previous 
>>versions. Right now I'm not at the computer that has 2010 so I can't test 
>>until later (and I have the final, RTM version from MSDN rather than the 
>>beta). But I'll suggest that, for all versions, you should replace this 
>>section of your macro
>>
>>    Selection.Collapse direction:=wdCollapseEnd
>>    Selection.InsertAfter theText
>>    If theStyle <> "" Then
>>        Selection.Style = theStyle
>>    End If
>>    Set rng = Selection.Range
>>
>>with this...
>>
>>    Set rng = Selection.Range
>>    rng.Collapse direction:=wdCollapseEnd
>>    rng.Text = theText
>>    If theStyle <> "" Then
>>        rng.Style = theStyle
>>    End If
>>
>>and move the Selection.Collapse direction:=wdCollapseEnd statement to the 
>>end, just before End Sub.
>>
>>The statement rng.Text = theText guarantees that rng will cover the inserted 
>>text, and everything after that should just work.
0
Jay
6/5/2010 2:29:32 AM
Thanks, Jay.

That worked, though i had to move my Style assignment elsewhere -- the
interaction between setting the attribute of the range and the style
of the paragraph got things confused, and it really belonged
elsewhere, since it applied to the entire paragraph and shouldn't have
been assigned to each chunk.

Your diligence and knowledge are appreciated!


g

On Fri, 04 Jun 2010 22:29:32 -0400, Jay Freedman
<jay.freedman@verizon.net> wrote:

>
>Sorry, that was my mistake. At the end of the sub, instead of just
>collapsing the Selection (which hasn't been moved from where it was
>before the sub started), put this:
>
>   rng.Select
>   Selection.Collapse direction:=wdCollapseEnd
>
>That will set things up for the next call.
0
Gary
6/5/2010 7:13:37 AM
Reply:

Similar Artilces:

different scale on x-axis
just wonder how can i change the x-axis value to different values in the same chart? e.g 0-->120 (within this range...the graph shows major unit such as 10, 20, 30, 40 etc....) then 100--> 1000 the graph just shows 200, 300, 400 etc as major unit~ Hi, You would have to construct you only axis labeling. See Jon Peltier's page on arbitrary axes http://peltiertech.com/Excel/Charts/ArbitraryAxis.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "katy" <katy@discussions.microsoft.com> wrote in message news:6CC63DCC-0A9C-4D56-8D3F-B790B...

Can't open word file with password: "can't find encryption code"
I have a word file with a password and encrypted used to open in word 2003 with the compatibility pack as the file was created in another pc in word 2007. Now, after installing office 2007, can't open that file: it says can't find the encryption code or source. Thank you. ...

drop down selections
Hi all, I have a problem. I have approximately 100 combo boxes to select item that are linked to various cells. Each drop down holds 4 items. When use the boxes to select items and save the book afterwards, th information in the linked cells is ok BUT, the item displayed in th combo box may not necessarily be the one that was selected. I woul only like this to remain so others can see what was selected in th combo box. Any ideas??? Cheers!!! -- Message posted from http://www.ExcelForum.com Hi Why don't you use Data Validation lists instead? -- Arvi Laanemets (When sending e-mail...

How select time zone in timedate.cpl? (batch command)
What single command (for use in a batch file) will change the PC's time zone to GMT+10 Is there a line command which will do this directly? ---------- I have started to look at the "Time And Date" control panel but it doesn't quite do what I want ... The following command opens the "Time And Date" control panel and selects the time zone tab. But what do I do to automatically select the GMT+10 setting and then close the control panel? rundll32.exe shell32.dll,Control_RunDLL timedate.cpl,,1 "Jonno" <johnno@mail.invalid> s...

Bug in 2010 beta
I keep ON files on my usb stick and sync between home and work computers. After I sync, if I close the notebook ON gives a message and says that some sections could not be synchronized. However I did have received no error message when the sync was being done. This happens on either of the computers. And actually, the sync is done without problems but when u attempt to close the notebook you get this error message and then you have to delete several pages which are indicated as not synced. On the other hand, compared to 2007, I experience less sync problems. Yet, ON 2010 beta ...

word 2007 document start up
When I open Word 2007 a previously saved document opens. How do I open Word 2007 to a new blank document? -- dkmerritt This newsgroup is intended for questions about Access, the database product that's part of Office Professional. You'd be best off reposting your question to a newsgroup related to Word. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "dkmerritt" <dkmerritt@discussions.microsoft.com> wrote in message news:7AA391B1-D8A7-4D73-9907-2B225DBE00BA@microsoft.com... > When I open Word 2007 a previously saved...

Prevent word from accessing excel file on open
Hello, I am using word 2007, excel 2007 on a windows XP machine with all updates done. I have a word file that has links to excel file. These are {Link Excel.Sheet.8 ......} fields. None of the fields have the \a switch - i.e. all links are set to manual update. The word switches "Update Automatic Links on Open", "Update fields before printing" and "Update Linked data before printing" are all unchecked. I am trying to prevent the word file from accessing the excel data at all before i do some management in a Sub AutoOpen() routine. Specifical...

Running SQL SELECT statements in Access runtime
[Access 2003] I have a number of applications deployed to clients that have only Access runtime throughout the organization. In all cases, I have remote access to their systems, since I also provide IT support. All are split FE/BE implementations. Occasionally, a customer will need a quick, one-time query or report, or I need to do a query remotely to identify a data anomaly. Since I cannot run a query directly in Access runtime without embedding it in a form, I have (at least) two choices (leaving out the option, for the moment, of deploying a full copy of Access to the se...

Weird phenomena when copying content of Word document into body of e-mail
When I cut and paste the content of a Word document into the body of my e-mail using Outook 2002 it appears fine; however when I send it in HTML format a portion of the text (in the middle part of the document) changes from an 11-point arial font to a 10-point arial font. I discovered this when I sent a blind cc to myself recently. It looks very unprofessional and only happens with the content of one particular Word file that I regularly copy and send. Any ideas what could be going on here, anybody? ...

Keyboard Viewer not working in Word
I have in Intel iMac running OS 10.4.6 and Word 2004 (11.2). When I turn on the Thai language in the OS and then turn on Keyboard Viewer all is fine and dandy in Finder and Safari and TextEdit, but when I switch to Word, the Keyboard Viewer switches to US (English). I can still type in Thai in Word, but they Keyboard Viewer doesn't show the Thai charactors. Does anyone know of a fix or workaround? Is this a Rosetta thing since Word runs in Rosetta and Safari and TextEdit don't? Thanks. Incase anyone is interested I think I answered my own question. I found that if an app runs i...

Time Difference
Hi, I want to find the time difference between column1 & 2 I have indicated in the last column what I should be getting. Pls assist. Thank you and Rgds. :) column1 column2 column1-column2 should be 23:45 00:15 23:30 minus 30mins 09:15 09:30 -00:15 correct 07:30 07:15 00:15 correct 00:15 23:45 -23:30 30mins -- kvani ------------------------------------------------------------------------ kvani's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27725 View this thread: http://www.excelforum.com/showthrea...

how do I create a chart using nonadjacent ranges?
I am trying to create a chart using nonadjacent ranges in Excel 2007. The directions say to select the first range, then hold down CTRL and select the second. When I hold down control it will only let me select one cell. What am I doing wrong? Thanks. The directions are correct, when you select the second column just drag the mouse down the column best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "floridamurph" <floridamurph@discussions.microsoft.com> wrote in message news:49C44302-91C9-4E7A-A25B-EBDBE8C317A1@...

Selecting cell ranges only with text using a macro
Hi, I would like to select only the cells with text inside in a specifie column, and then define them with a name. For example, my data starts at cell B5. How would I select all th cells with data from B5 to Bx and then define them with a name? Thanks. : -- Message posted from http://www.ExcelForum.com Select B5 to B(whatever). Edit|goto|Special Now your choices begin. If you mean text (like constants), check that option button. You can even limit it to numbers/text/logicals (True/False)/errors. Now with those cells selected, you can use Insert|name|Define to give it a nice name. (...

How to share macros in Word 2003
Let me start off by saying I do not write VBA code. I can make some simple macros using the Record Macros feature in Word. I need to know how I can share my macros with others. I have Word 2003 and I am on a network. I have tried various things I have found on the internet, including directions on how to share macros in Word 2002 (which seemed to work for some but not all and did not copy any shortcut keys for the ones it did work for). I even created a template and using the macros Organizer transferred NewMacros from my Normal.dot to this template, then attached it as an...

Selecting Sheet By Codename
I have a spreadsheet with multiple layers of sheets, where I have used the Sheet Name to give a Description meaningful to other users, but have a separate codename that uses a numbering system, which should allow a button/macro to step up through the layers. In an earlier version the Sheet Name was linked to the Codename, but I now have too many layers & variations in Sheet names to manage easily. Example of problem and Code below Is anyone able to advise where I need to make some mods for it to work? Thanks very much Kris Example Sheet Structure Sheet ...

Turning word-wrap OFF in Outlook printed monthly calendar
With Outlook 2000, I was able to print a monthly calendar WITHOUT word-wrap for each appointment, and with that, I was able to see all the appointments even in the small space of the monthly view. Now with Outlook 2003, all appointments wrap and I need to use a miniscule font size to display all appointments in printed form. How do I turn word-wrap OFF in the printed form of Outlook 2003 monthly calendar? I ONLY want the first line of appointments displayed! Thanks a bunch! ...

Replicated account for a different domain
Hard for me to figure out how to properly ask this question. I have a user on an Exchange 5.5 server. They want to use a WM5 device now. We have a 2003 server for another domain in the company. We may eventually migrate all their mailboxes to the 2003 server but it will be a couple of months away. I was thinking perhaps we could use some sort of rule to continuously copy all their e-mail, contacts and calendar onto another e-mail account so they could at least get real time data on the PDA. Perhaps there is a way to set up just one e-mail address of the other domain on this exchange...

invoices selected for payment report
is there a way to create a report of invoices that are due for payment. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=c45b1991-84e1-4ff0-b559-60091ec32b15&dg=microsoft...

Changing Selection for the "Center Across Selection" alignment
I'm sure others must have had this problem. Hopefully someone can help me fix it. When I format a cell as center across selection, it centers the info over whatever area I had highlighted. But once I've done it once, I can't figure out how to change the selection to center the text over. For example, let's say I center A1 over A-H, then I delete the information in column G and H and I only want to center the info over A-F. No matter what I do I can't seem to change the selection. I've tried highlighting the new area, and hitting "center across selectio...

Select Between Two Dates
Hi, I need to create a query to select dates that are between date 1 (1985-11-04) and date 2 (1985-11-11). Currently no records are showing up when I do this: Between (1985-11-4) And (1985-11-11) I've checked and made sure that there is at least one record that meets all specified criteria. Any help would be appreciated. Thanks, jay_2882 Try... Between #1985-11-4# And #1985-11-11# If your dates are in variables, use the DATEDIFF function (look up in Access Help) -Dorian "jay_2882" wrote: > Hi, > I need to create a query to select dates that are between date 1 &...

Word integration
How do I incorporate Word seamlessly into CRM? i.e., that I write a document in Word that automatically gets reflected against customer/event? cheers Richard Hello! Can you explain more in detail what you mean! Perhaps our new Add-on can help you! We have a Word Add-On called "WordMailMerge for MSCRM 1.2". *)This Add-On gives you the ability to create MailMerges and after that saves the document as outgoing letter activity with the content of the Word Document as description. *) Sace ANY Word Document in Microsoft CRM. The new "Add Document to CRM" button on your W...

Research Pane Opening when Word is launched
I'm wondering if there is a way to prevent the researche pane from opening when word is launched? I have looked at several setting and searched several sites, but am unable to find the answer. Please let me know. Maria I haven't heard of this happening. If you hold the Alt key and click on something in Word, that generally opens the Research pane. Could there be something peculiar in how you're opening Word? Does it happen all the time? For example, does it happen when you double-click a document? Or does it happen only when you use a shortcut to open Word? Or...

Select Query 04-08-10
I've created a random record query that works. It pulls 50% of records for a particular timeframe. The problem is that I don't want random records, I actually just want it to pull 50% of records for the timeframe based on another criteria ([prac name]). The reason I want to pull the same records each time is it will take more than one day to perform the audits. Any suggestions??? Here is my SQL. SELECT TOP 50 PERCENT EncountersToAudit.ID, EncountersToAudit.[Prac Name], EncountersToAudit.[Created By], Staff.[last name]+", "+[first name] AS FullName, Encounters...

Dynamically defined named ranges and INDIRECT
I have several lists which I've defined dynamically, such as: List1 =OFFSET(A1, 0, 0, COUNTA(A1:A5000),1) List2 =OFFSET(B1, 0, 0, COUNTA(B1:B5000),1) so that they will change automatically when I add or remove items. I'm trying to allow the user to choose a list and automatically gives them statistics from that list. A2 on Sheet2 has a data validation list of all list names. A6 is supposed to display the sum of all entries in the chosen list. The formula I have for A6 is: =SUM(INDIRECT(A2)) This gives me a #REF! error. It works if I define the list statically, such as $A$1...

Need a query to return a value if out of range
I have the table below. I need to create a query that will return a score based on a passed Age and a passed LimitValue. For example: If Male= True, Age = 33 and LimitValue = 70 I need to return a score of 4. Because Age <= AgeHighLimit and Male = True when the LimitValue >= HighLlimit (60) in the table the highest score should be returned. Likewise, if Male= True, Age = 33 but LimitValue = 24 I need to return a score of 1. Because for that age and gender when the LimitValue <= HighLlimit (25) in the table the lowest score should be returned. I can write a query that ...