OFFSET problems in dynamic range

Hi,
I'm having the strangest problem with the OFFSET function.

I have 5 dynamic ranges in my worksheet.

Aimline
=OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1)
Sessions
=OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1)
WCPM
=OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1)
Date
=OFFSET ('Weekly ORF'!Aimline,0,-1)
Date2
=OFFSET ('Weekly ORF'!WCPM,0,-1)

The problem is that the two names for Date and Date2 keep reverting to
=OFFSET ('Excel Template.xls'!Aimline,0,-1) and
=OFFSET ('Excel Template.xls'!WCPM,0,-1)
Which consequently prevents me from being able to adjust the SERIES in
the associated chart.

Does anyone know why this is doing this and how I can stop it? Thanks,
cabybake

0
cabybake (15)
2/15/2005 4:29:38 PM
excel 39879 articles. 2 followers. Follow

4 Replies
545 Views

Similar Articles

[PageSpeed] 58

cabybake wrote...
....
>I have 5 dynamic ranges in my worksheet.
>
>Aimline
>=OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1)
>Sessions
>=OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1)
>WCPM
>=OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1)

You're missing ! between 'Weekly ORF' and the range/columns addresses
in the COUNTA calls, which DOES throw syntax errors, so these can't be
the actual formulas you're using. Are these only missing the !s?

>Date
>=OFFSET ('Weekly ORF'!Aimline,0,-1)
>Date2
>=OFFSET ('Weekly ORF'!WCPM,0,-1)

Are Aimline and WCPM defined as worksheet-level names rather than
workbook-level names? If not, that's your problem.

0
hrlngrv (1990)
2/15/2005 7:22:53 PM
Are these only missing the !s?

Oops, I actually mistyped it for this email, but it does have the !s in
the formula itself.


Are Aimline and WCPM defined as worksheet-level names rather than
workbook-level names?

How do I define these as worksheet-level vs. workbook level?

Thank you, cabybake

0
cabybake (15)
2/17/2005 1:37:13 PM
cabybake wrote...
....
>Are Aimline and WCPM defined as worksheet-level names rather than
>workbook-level names?
>
>How do I define these as worksheet-level vs. workbook level?

Names may be defined at workbook-level (default) or worksheet-level. If
you highlight the range B2:E5 in the worksheet named foo, then run
Insert > Name > Define and enter just

bar

as the name, then bar will be a workbook-level name. On the other hand,
if you enter the name as

foo!bar

then bar will be a worksheet-level name which you may refer to just as
bar in the foo worksheet, but as foo!bar in other worksheets.

If you run Insert > Name > Define, do the lines containing Aimline or
WCPM in the name list show the worksheet names on the right hand side
of the line? If not, they're workbook-level names. If they're
workbook-level names, then you shouldn't precede then with worksheet
names.

So, what happens when you change the definitions of Date and Date2 to

Date 
=OFFSET (Aimline,0,-1) 

Date2 
=OFFSET (WCPM,0,-1)

?

0
hrlngrv (1990)
2/17/2005 8:26:49 PM
Thank you, thank you- everything worked!!!

0
cabybake (15)
2/17/2005 10:15:50 PM
Reply:

Similar Artilces:

Comments problem
I've created a number of comments. When I position the comments where I want them, they only reflect their positions on "Show" or "Edit" comment. All the comments default to the same relationship to the cells when you pass over them. This is a problem because I've also used data vaildation (with lists) on these cells and you can't display the comment with the list pulled down at the same time because the comments are tool close to the validated cell. Thanks in adavance. The comment usually appears to the right of the cell, and should still allow you to see t...

Excel Mail Merger Problem
When merging excel with word for..my currrency field is dropping off zeros to the right of decimal point..if field has $ 56.03, it imports correctly, but if it is $56.00, it comes in as $56, if it is $56.30, it comes in as $56.3...this process is for invoices..any advice (I've tried everything)? Debra Dalgleish has given the following reply in newsgroups http://google.com/groups?threadm=3EBB0685.5080002%40contextures.com and has also identified the following MS KB articles in such as : http://google.com/groups?threadm=3DFA8DA8.6000209%40contextures.com 304387 - WD: Date, Phone N...

synchronising problem with Sony NWZ-S545
Up until recently i could sync tracks/albums etc to my sony S545 MP3. now when i try and drag tracks to the sync folder then click start sync the mp3 player just freezes, WMP states "processing" but nothing happens. Have searched the web for a solution but nothing. done sysem restore etc etc. I guess it must be a recent update but what one? Its a pain in the backside and hope someone can help "Wookywoo" wrote: > Up until recently i could sync tracks/albums etc to my sony S545 MP3. now > when i try and drag tracks to the sync folder then click s...

Printing problem from Excel 2007
Greetings, I am having an issue with printing from Excel 2007. Many users when printing from Excel with get several pages of non-sensical "junk" printed out on the pages. If it actually prints what is on the page, none of the items are in the cells, but look like they are just randomly thrown about the page. I have found that this most often is related to the Calibri font somehow, because I instruct them to change the font to Arial and all prints fine. That being said, if the user converts the Excel sheet into a PDF and print, it prints perfect. This is a great workaround, but kind ...

VBA Help problem
I'm using Office 2003 SP3 on Win 2000 SP4. When I use VBA help, if thee are hyperlinks on the page I get a popup with the following error: "Runtime error has occurred" Line 16: Error: not implemented If I choose debug from this dialog box I am taken to a script and another error popup: "htmlfile: Not implemented" there is a yellow arrow at the line: "document.onclick = window.parent.closeList" Am I missing a reference somewhere in VBA or is this a more serious problem? The Windows 2000 install has been around a long time... I've ...

Problem hot synching Outlook 2002 with Palm Pilot
I recently had to reformat my c: drive and reinstall Windows XP and Office XP, including Outlook 2002. Since then, whenever I hot synch my Palm Pilot with Outlook, the hot synch is interrupted with a dialog saying "This action cannot be completed because the 'Inbox Microsoft Outlook' program is not responding. Choose 'Switch to" and connect the problem." When I press the 'Switch to" button, I get a second dialog, which says, "Microsoft Outlook - A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? ...

Problems printing in Publisher 2003 with HP PSC 2410 #2
I am new to publisher 2003 and am having great difficulties printing anything properly using an HP PSC 2410 all in one. As an example when trying to print a half page side folded pre-designed greetings card all that prints is page 1 and part of page 4, minus any text. Pages 2 and 3 are missing altogether. The print preview however displays everything correctly. I have reinstalled Office 2003 and updated the printer drivers to the latest versions but the problem remains. Any help is much appreciated. ...

code problem
Hi this is a follow on to a previous thread (Copy Macro) We are now successfully creating a new Workbook using the code below. With ActiveWorkbook FName = .Path & "\code.txt" .VBProject.VBComponents("Module2_sort").Export FName End With ThisWorkbook.Worksheets(Array("Supplier Concerns")).Copy ActiveWorkbook.SaveAs Filename:="c:\Week\testing" & strFileName & ".xls" ActiveWorkbook.VBProject.VBComponents.Import FName Problem is when we open the new Workbook and run the macro the code defaults ba...

conversion lotus 123 files to excel -- problem
I am converting lotus123 files to excel2002. One problem is that in lotus, literals are ignored when found in a cell within a formula. Excel on the other hand is not doing this and therefore causing #value errors on all the formulas where this occurs. Is there a way to handle this in excel other than manually having to change all the formulas? ...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

save as problem
I cant save my excel file The "save as" option is not eviable. Whats happening? "From a prior post by Ron DeBruin, You can reset your menubar to default. Right click menubar. Customize > Toolbars Tab. Select worksheet menubar. Click Reset button. Using VBA Application : CommandBars(""Worksheet menu bar"").Controls(""File"").Reset Or the whole menubar Application.CommandBars(1).Reset You lost the things you add to the menubar!!" ========================================================== And one link that offers some sugges...

Odd problem with worksheets when opening file
Okay, here's the odd problem that's come up. When you double click on a excel file, excel opens up, but you can onl see the toolbars. The grid area looks like a snapshot of whatever you current background is before the file opened up. If you were showin your desktop, after the excel file opened, you'd still see your deskto in the datagrid area. If you start up a new excel file, then do the File>Open>file name, th file will open correctly. This problem happens across users here. Everyone is currently runnin office 2K. Any ideas on what causes the problem and any...

Problem OL 2002 Email
I am having a problem with OL 2002 email settings. I have two computers which I am setting up. One computer works perfectly. The second computer I have configured exactly the same...incoming/outgoing servers and ports, etc... are all setup the same. On the second computer when I press test email settings, everything tests fine. But when I try to do a Send/Receive, I run into problems. First, the POP3 tries to connect to the POP3 server twice at the same time. (I am able to see this on my POP3 server console.) Second, the SMTP sending mail never connects to my SMTP server. (Ag...

2007 B2 MSOO has encountered a problem
More often then not when I click to open an email in my inbox I get this MSOO pop-up with a checkbox, checked telling me MSOO has encountered a problem and needs to close. Has this been an issue for others and is there a fix. If you have Adobe Acrobat Pro 6/7 (not the reader), go into Control Panel, Add/Remove Programs, click change for Adobe Acrobat Pro and remove the Office & Outlook plugins. Patrick Schmid -------------- http://pschmid.net "Jax" <Jax@discussions.microsoft.com> wrote in message news:A74147F4-CD75-4FF8-98A7-5A18B01A8FDD@microsoft.com: > More of...

Sumproduct Problem (AB)
Hi everyone, can you please sort out this problem for me? SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Opening Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138) 'Client Opening Stock'!B2:B138 is the Client Name Client Opening Stock'!C2:AH138 is the Product 'Client Opening Stock'!C2:X138 is the Amount I belive the formula is not the proper formula, but this is the result I need. Thank you for your kind help You need to tell us what the problem is, and what solution you want. Why is it "not the proper formula...

Query Problem in Test.
I keep getting errors on the WHERE part of my query. I'm sure it's something simple. I don't know if you will need the whole code to see what the problem is. It is lengthy, so I will start with just where the problem is. Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM (Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID = qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5 = T2.ZIPCode WHERE " & strWhere) ' See if found none If rs.RecordCount = 0 Then MsgBox "No Churches meet your crite...

HTA problem with IE8
We discovered an issue with some inbedded VBScript code inside an HTA written by a developer that is no longer with us. The code work in IE6 but error out on the set objMsgArea statement indicating that the object requires 'objMenuFrame' which should have been set in the previous line of code. If I put in an 'On Error Resume Next" statement in that sub the HTA appears to work correctly however I don't like putting a band-aid on something like this. Does anyone have any solution for this? Here's the code: sub AppStatusDisplay(strMsgIn, bolAddToStatusLogIn) s...

Sending email in CRM web app problem
Hi, I found this problem very annoying. Our CRM's SMTP service is hosted in YahooMail. The SMTP address is mail.bizmail.yahoo.com and this is a "locked down" server, which means required authentication by username and password. I configured the CRM server's registry by settings the SMTPServerUrl and other stuff, such as Username and password entries. Now, when we send emails from CRM web app, it works... BUT... when we send email using a template which has embedded images, it cannot send the email and reports an error: "Not valid association" something... A...

Problem with 07 Office Pdf/Xps Add-in
Hello Despite having installed the 2007 Office SaveAs Pdf/Xps add-in, I have seen Mary Sauer's posting on this somewhere asking if it in the Add-in of the Office programmes. I do not see it any of the programmes even though I have reinstalled twice with genuine validation. Anything else I can do? Thks. Menk It's like a printer...you print to it. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Menikos" <menikos88@hotmail.com> wrote in message news:u2EEuVswIHA.4564@TK2MSFTNGP06.phx.gbl... > Hello > > Despite having installed the 200...

Unable to deliver the message due to a recipient problem #3
Hi can anybody help. I check with varies newsgroup but to no scucess. like for example below When you send mail to an X.400 personal address book (PAB) entry or one-off address, you may receive the following non-delivery report (NDR):... However, this one-off entryis not active in our server. ...

CRM 4.0 CTP 3 Workflow problems
I'm trying to create a workflow that triggers on Status Change to the Quote Entity, which will create a new task. When I publish the workflow, and then try to do anything with a quote, I receive an error: You cannot switch the execution context's caller if the context is impersonating the system user. Has anyone else seen this in 4.0, and does anyone know what can be done to fix it? Thank you, Brett Whats the role of the user that has published the workflow? Whats the role of the user that is trying to perform action on the quote? If you unpublish the workflow, does the action ...

Changing of range (Address) to (Cell)
Hi people I have encounter a problem with the use of range From a recorded macro, it's listed this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2,A24:M28"), PlotBy:=xlRow And I edit it to this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2," & Cells(StartX, StartY), Cells(LastX, LastY)), PlotBy:=xlRow And obviously VBA compiler won't let me go this easily, it happen to give an "evil-comment" on my source range, May i know how can i solve this Thank You Hi Kaiyang, Try this, assuming the...

Scanning Problem no one seems to be able to fix
I have been having this problem trying to scan and acquire these files i have. i get all the way to the public box where the documents are, but at the end of an acquire it says "Error in retrieving thumbnail BMP. internal error occurred. connection lost. please refresh." I've had a couple IT guys in here messing with it and still nothing seems to work. I'm just not sure where to look or begin. any help or ideas would be appreciated greatly. thanks -- Travar1410 ------------------------------------------------------------------------ Travar1410's Profile:...

outlook client problem #2
Hi I have a one user that have problem with outlook client are always prompt to login outlook 2007 crmclient V3C and have all patch install You might want to create a new profile for that client. Also check that they are running SQL 2005 Express on their desktop as opposed to an MSDE instance. Their was a shift in databases from 2000 to 2005 during the life of CRM v3.0 "sjm" wrote: > Hi > I have a one user that have problem with outlook client are always prompt to > login > outlook 2007 > crmclient V3C and have all patch install Hi sjm, Is the user also prom...