MS Query and Row Range Names

We utilize MS queries in a spreadsheet that has range 
names for rows. Unfortunately, when the query returns a 
different # of records than before the query, it messes 
up the reference of the range names (i.e. if row 123 is 
named "Partner1" before the refresh of the 
query, "Partner1" will not move with the associated row 
if new records are inserted and/or deleted - NOR does it 
stay at row 123). Does anybody know what is going on and 
how to overcome this issue?

Thank you.
0
anonymous (74722)
12/11/2003 9:54:47 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
253 Views

Similar Articles

[PageSpeed] 9

Correction to my original message, the range name does 
stay at row 123 (i.e. absolute). But I have tried to use 
relative references in my named ranges to no avail. Any 
ideas???

>-----Original Message-----
>We utilize MS queries in a spreadsheet that has range 
>names for rows. Unfortunately, when the query returns a 
>different # of records than before the query, it messes 
>up the reference of the range names (i.e. if row 123 is 
>named "Partner1" before the refresh of the 
>query, "Partner1" will not move with the associated row 
>if new records are inserted and/or deleted - NOR does it 
>stay at row 123). Does anybody know what is going on and 
>how to overcome this issue?
>
>Thank you.
>.
>
0
anonymous (74722)
12/11/2003 10:36:27 PM
Reply:

Similar Artilces:

Query Criteria 05-24-07
Finding it hard to get my head round this so help needed! I have a query based on form fields. There is date range field (always selected) and 2 other optional criteria needing 4 WHERE/OR statements to get the required results. I want to add another optional criteria field which i think will leave me with 11 WHERE/OR statements and a big headache! Is there a simple way around this? ...

Dynamicly change spreadsheet tab names depending on cell value
Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 for each day of the month) These tabs are named - 1,2,3 e.t.c Is it possible to define rule that would ad "!" to day which is weekend day. In other words is it possible to dynamicly change tab name depending from value of cell ? If this is possbile how do i do this ? Right-click on the spreadsheet tab, select View Code and paste this in:- Private Sub Worksheet_Activate() ActiveSheet.Name = Range("A1").Value End Sub To test this I put the date in cell B2 and this formula into A1:- =TEX...

FW: Watch this corrective pack from the MS Corp.
--neuueiaxmiymuhxw Content-Type: multipart/related; boundary="vzbdpgbyx"; type="multipart/alternative" --vzbdpgbyx Content-Type: multipart/alternative; boundary="khzemrmkfyjhgz" --khzemrmkfyjhgz Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to protect your com...

How do I change a data label on an xy scatter to a unique name as.
The only way I have figured out is to create a separate series for each data point, which is tedious for 100+ points...any ideas? Hi, Try one of these free addin to link cells to data labels. This way you can have a single data series rather than multiples. Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Cheers Andy SOSCIENT wrote: > The only way I have figured out is to create a separate series for each data > point, which is tedious for 100+ points...any ideas? -- Andy Pope, Microsoft MVP - Excel http://www.andyp...

Filtered Rows using Mode Function?
Hi Excel Forum, I am using numeric "filtered" data and I need to find the most frequen / re-occurring values (1st, 2nd, 3rd, 4th, 5th etc.) from the visibl filtered rows. The Mode function seemed likely, but I cannot get it t work with filtered rows. Can you assist with working examples, please: Formula based input direct on worksheet. VBA Macro using Formula. VBA UserDefined Function. Thank you QT -- Message posted from http://www.ExcelForum.com I would use one of 2 methods :- 1. Formula in another column and sort descending :- =COUNTIF($A$1:$A$20,A16) Need to sort out dupl...

how to create button commande to refresh data in query in excel 2.
I have a excel spreedsheet that contain external data. I would like to put a button in the excel sheet to update the sheet without doing right click and refresh. My user here are very dummies. Jean Francois If this is external data then bringing it in should launch the external data toolbar. They will only need to click the exclamation (!) mark. Seems like re-inventing the wheel. If you must then put a button on the worksheet and assign it to this macro Sub refreshdata() Dim wks As Worksheet Dim qryTab As QueryTable Set wks = ActiveSheet For Each qryTab In wks.QueryTables qryTab.Refr...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

Passing Query Parameters from BP to a URL
Hey Eighties Fans: After constructing a query in Business Portal, is it possible to pass a parameter from that query into a URL? Doing so would be similar to the SmartList Builder feature where you can create a web site (URL) "Go-To" link and pass a parameter within that link that literally becomes part of the web site address that appears in your web browser. Thanks! childofthe1980s ...

Dynamic Range Defined by Value of Cell
I am just getting started with dynamic ranges. I have data as follows: A B C Product1 qty cost Product2 qty cost .... ProductN qty cost TOTAL qtytot costtot The number of products varies. There is other data below this that is unrelated. How can I define a range dynamically that will always capture A:C and as many rows up and including the first time it finds "TOTAL" in column A? Any help greatly appreciated. Thank you. I don't know what you mean by "define a range" b...

Name NOT Show in Global Address List
Hi all, I'm running SBS 2003 with exchange 2003 server. Recently, I hided one of the user on our exchange server so that the name won't show in the GAL. Now I'd like to show this person name on the GAL, but it's not showing up. I double checked to make sure the "hide from gal" is not check. What should I do? Please Help !! Thanks in advance, Ed Ed wrote: > Hi all, > > I'm running SBS 2003 with exchange 2003 server. Recently, I hided one > of the user on our exchange server so that the name won't show in the > GAL. Now I'd like to ...

I wish my distributions names to be anonymous when sending emails
I would like my Distribution list to be only showing as the List name in the email and not expanding so others can see who else had received my email. Is this at all possible? You'll need to put them in the BCC field. If you want something to show in the To field, make a contact named 'list name' and use your address for its email address. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net ...

Bug in ActiveDocument.Range.Font?
I'm trying to check whether files contain certain font attributes (hidden, smallcaps, etc.) anywhere within the whole document or in its sections but the code below seems to return wdUndefined (9999999) irrespective of whether the document has any hidden text or no at all. Strangely enough, it works for short test documents but when I try it on a "real" document 20 or 30 pages long, it always returns .Font.Hidden = 9999999 even though running a Find for hidden text shows that the document has no text with hidden font.. It also fails at Section level but works proper...

Formula In A Query
Hi I am trying to create a formula that checks to see if a date in a record is from the same month as the current date and if so return a value The formula is not falling over but always returns the OR value. Could someone take a look and point out my error MonthNo: IIf([Date SubmittedtoLab]=Month(Date()),MonthName(Month([Date SubmittedtoLab])),5) Thanks Richard Not sure what you're trying to do with this. I you just want the month number you could simply use DatePart("m",[Date SubmittedtoLab]) This is (I think??) what your 1st post was looking for - but I don't ...

Find data in two ranges
Hello, I have a worksheet with in the same row two different ranges: one with nummeric data one with time values. These two ranges works as follow the first nummeric data cooresponds with the first time value, the second nummeric data corresponds with the second time value. For the nummerric data I calculated the best of 3. Now I need the corresponding time value for those best of 3. Thanks for your help with this one. Someone replied to your other post and asked for more detail. -- Biff Microsoft Excel MVP "Santafe" <Santafe@discussions.microsoft.com> w...

Update query help 02-06-08
I have Two tables with the same information. Table Two has a column called Errors with data. How can I update table one Error column with the same info in table two errror column. table 1 table 2 nameid name date items errors nameid name date items errors 100 Floyd 2/5/08 2500 100 Floyd 2/5/08 2500 4 200 Anna 2/5/08 6000 200 Anna 2/5/08 6000 7 Use an update query. Perhaps that would look like the following. STEP 1: BA...

Why if I insert a blank row in a sheet does the auto complete, for
Please repost your question in the body of a message... In article <EE1E49B1-8DCE-4C83-A753-46AAF8543B6B@microsoft.com>, "Bylin" <Bylin@discussions.microsoft.com> wrote: I have built a spreadsheet that uses the same names over and over but because I have an empty row between each pair of entries, the text doesn't fill in automatically when I start typing in a cell below a blank row. Any ideas? "JE McGimpsey" wrote: > Please repost your question in the body of a message... > > In article <EE1E49B1-8DCE-4C83-A753-46AAF8543B6B@microsoft.c...

load query results into ado recordset
I want to loop through query results in vba. This query is in the same access db as the vba module. I thought I wanted to load the query results into an ado recordset, but cannot find the syntax. I can re-create the query in vba, substituting the wildcard character "%" for "*", and that works, but is not what I want. I want to be able to manipulate the query in access, then process the results in vba, one row at a time. Any suggestions? ...

Lookup sheet names in formulas
What function or instruction do I use to lookup a sheet name in formula -- digica ----------------------------------------------------------------------- digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1492 View this thread: http://www.excelforum.com/showthread.php?threadid=27735 Hi see: http://www.xldynamic.com/source/xld.xlFAQ0002.html "digicat" wrote: > > What function or instruction do I use to lookup a sheet name in a > formula? > > > -- > digicat > ----------------------------------------------------------...

Question regarding inserting rows and data.
In Sheet1 I have the following (from A1 to C3): 2 3 4 3 4 7 8 8 1 In Sheet2 I have the following (from A1 to C3): =Sheet1!A1+0.001 =Sheet1!B1+0.001 =Sheet1!C1+0.001 =Sheet1!A2+0.001 =Sheet1!B2+0.001 =Sheet1!C2+0.001 =Sheet1!A3+0.001 =Sheet1!B3+0.001 =Sheet1!C3+0.001 ....which means that the values displayed in Sheet2 are the following: 2.001 3.001 4.001 3.001 4.001 7.001 8.001 8.001 1.001 Now, suppose I insert a new row enter new data in Sheet1, like so: 2 3 4 3 4 7 1 1 1 8 8 1 Sheet2 looks the same, and the bottom row's formulae are changed to: =Sheet1!A4+0...

Query update from MSN
I have a worksheet that uses a query table to update stock prices from MSN. It worked fine a few months ago but now it causes Error 400 when I call the Refresh method. Has anything changed at MSN that might cause this? -- Peter Aitken Remove the crap from my email address before using. Try using yahoo and/or going to xltraders and downloading a feee file under the author donalb36. xltraders-subscribe@yahoogroups.com -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Peter Aitken" <paitken@CRAPnc.rr.com> wrote in message news:OBwLsFkAGHA.2044@TK2MSFTNGP12.ph...

Saving Queries from Address Book Views
I am working on a test machine with at least 40 custom search queries in Address Book View for the current profile; I would like to get a copy of 40 custom queries and apply to another system without having to re-write it again. Anyway to export queries from Address Book Views? OR Where is the location that keeps these queries? Prefs? Hoping if copy and paste query file on other machine would work. Thanks in advance. ...

Criteria Row
Good afternoon, I have a question problem that I can't seem to solve. It primarily regards the usage of "and" and "or". I have a table that looks like the following: www,4,rrr, 1,2,3,4 xxx,4,eee,0,3,6,7 yyy,4,fff,0,0,0,0 zzz, 4,ddd,40,40,40,40 ggg,4,jjj,4,0,7,0 kkk,4,ddd,51,8,0,9 What I would like to do is select all rows with non zero values less than 50 in the 4th, 5th, 6th , and 7th column. In the above example I would want the www, xxx, zzz, and ggg rows because all the values are not zero and none of the values are greater than 50. I do not want yyy because th...

MS Query #2
This is a multi-part message in MIME format. ------=_NextPart_000_0013_01C3675C.4E772870 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Does anyone know where an SQL beginner can get basic help with commands = in MS Query. I have limited knowledge of SQL and want to be able to create my own = Queries in MS Query but as usual the help isn't. Web pages, news Forums etc.... I'm also after a job if anyone wants to know! Regards Dean=20 dkso@ntlworld.com=20 http://homepage.ntlworld.com/dkso=20 --- Outgoing mail is certified Viru...

Sort names and e-mails from one column
Hi Just wondering if any could help me with a problem I have sorting names and e-mails from one column. I have an excel spreadsheet and in column A it has a list of names and e-mail addresses. I need to split them into 2 columns emails in one names in another for importing into another program. Is this possible? The emails are all odd and the names even if this helps. A B C 1. email 2. name 3. email 4. name 5. email 6. name Thx in advance for any help. -- jul3s ------------------------------------------------------------------------ jul3s's Profile: http://www.ex...

Where to download MS Money 14?
I've just purchased a new PC and would like to install MS Money on it. I'm happy with the version I have on my old machine, which is version 14 (not sure how that equates to a year). Where do I download an old version? Thanks, Mike MS Money is a purchased program, not free download. Go to http://www.microsoft.com/money/default.mspx to get it. Frank "Mike B" <MikeB@discussions.microsoft.com> wrote in message news:65FE76B5-7709-4927-BB39-9B8FE58437FC@microsoft.com... > I've just purchased a new PC and would like to install MS Money on it. > I'm ...