Have I found an excel bug?

Hello all

I have the following urgent problem:

Cell A1: ='[logESM2.xls]per dag'!$D670
Cell A2: ='[logESM2.xls]per dag'!$D677
Cell A3: ='[logESM2.xls]per dag'!$D684

The formulas are pointing to another document with daily values in column 
format.
I want to have all values from monday, tue... in another document (every 7th 
row).
Very simple I guess(ed).....
When I copy the cells, excel doesn't count per 7 but makes very strange 
jumps to other cells:

A4='[logESM2.xls]per dag'!$D673
A5='[logESM2.xls]per dag'!$D680
A6='[logESM2.xls]per dag'!$D687
A7='[logESM2.xls]per dag'!$D676

I need 691, 698 etc ....

Is this a bug?

thanks a lot for any help on this! 


0
kyrbi (8)
10/25/2006 1:38:40 PM
excel 39879 articles. 2 followers. Follow

5 Replies
281 Views

Similar Articles

[PageSpeed] 20

kyrbi,

Not a bug.  That is how formulas are copied.

In cell A1, use the formula

=INDEX('[logESM2.xls]per dag'!$D:$D,670+(ROW()-1)*7)

and copy down as far as you need....

HTH,
Bernie
MS Excel MVP


"kyrbi" <kyrbi@datoeternietoe.com> wrote in message 
news:ANJ%g.147929$TD4.2328682@phobos.telenet-ops.be...
> Hello all
>
> I have the following urgent problem:
>
> Cell A1: ='[logESM2.xls]per dag'!$D670
> Cell A2: ='[logESM2.xls]per dag'!$D677
> Cell A3: ='[logESM2.xls]per dag'!$D684
>
> The formulas are pointing to another document with daily values in column
> format.
> I want to have all values from monday, tue... in another document (every 7th
> row).
> Very simple I guess(ed).....
> When I copy the cells, excel doesn't count per 7 but makes very strange
> jumps to other cells:
>
> A4='[logESM2.xls]per dag'!$D673
> A5='[logESM2.xls]per dag'!$D680
> A6='[logESM2.xls]per dag'!$D687
> A7='[logESM2.xls]per dag'!$D676
>
> I need 691, 698 etc ....
>
> Is this a bug?
>
> thanks a lot for any help on this!
>
> 


0
Bernie
10/25/2006 2:08:54 PM
This is not a bug, this is specified behavior of Excel.
A4 is 3 lines below A1, do the references in the formula are increased by three.

Use the ROW() function multiplied by 3 and the INDIRECT() function to get this done.

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"kyrbi" <kyrbi@datoeternietoe.com> wrote in message news:ANJ%g.147929$TD4.2328682@phobos.telenet-ops.be...
| Hello all
|
| I have the following urgent problem:
|
| Cell A1: ='[logESM2.xls]per dag'!$D670
| Cell A2: ='[logESM2.xls]per dag'!$D677
| Cell A3: ='[logESM2.xls]per dag'!$D684
|
| The formulas are pointing to another document with daily values in column
| format.
| I want to have all values from monday, tue... in another document (every 7th
| row).
| Very simple I guess(ed).....
| When I copy the cells, excel doesn't count per 7 but makes very strange
| jumps to other cells:
|
| A4='[logESM2.xls]per dag'!$D673
| A5='[logESM2.xls]per dag'!$D680
| A6='[logESM2.xls]per dag'!$D687
| A7='[logESM2.xls]per dag'!$D676
|
| I need 691, 698 etc ....
|
| Is this a bug?
|
| thanks a lot for any help on this!
|
| 


0
nicolaus (2022)
10/25/2006 2:10:06 PM


Not a bug.  That is how formulas are copied.

In cell A1, use the formula

=INDEX('[logESM2.xls]per dag'!$D:$D,670+(ROW()-1)*7)

and copy down as far as you need....

HTH,
Bernie
MS Excel MVP


Ok, i'll give it a try by using your formula but I don't understand the 
logic in my results 


0
kyrbi (8)
10/25/2006 2:20:05 PM
ROW() returns the number of the row where the formula resides.  ROW() in cell A1 returns 1  (1-1)* 7 
is 0, so the INDEX function will return the 670th value in column D, or cell D670.  In cell A2, 
ROW() returns 2, so (2-1)*7 is 7, and the INDEX function will return the 677th value in column D, or 
D677....and so on....

HTH,
Bernie
MS Excel MVP


"kyrbi" <kyrbi@datoeternietoe.com> wrote in message 
news:poK%g.147979$Pr7.2491505@phobos.telenet-ops.be...
>
>
>
> Not a bug.  That is how formulas are copied.
>
> In cell A1, use the formula
>
> =INDEX('[logESM2.xls]per dag'!$D:$D,670+(ROW()-1)*7)
>
> and copy down as far as you need....
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> Ok, i'll give it a try by using your formula but I don't understand the
> logic in my results
>
> 


0
Bernie
10/25/2006 2:32:05 PM
You have your answers, but if you want to understand why it does this, then 
look up 'absolute vs relative referencing'

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"kyrbi" wrote:

> Hello all
> 
> I have the following urgent problem:
> 
> Cell A1: ='[logESM2.xls]per dag'!$D670
> Cell A2: ='[logESM2.xls]per dag'!$D677
> Cell A3: ='[logESM2.xls]per dag'!$D684
> 
> The formulas are pointing to another document with daily values in column 
> format.
> I want to have all values from monday, tue... in another document (every 7th 
> row).
> Very simple I guess(ed).....
> When I copy the cells, excel doesn't count per 7 but makes very strange 
> jumps to other cells:
> 
> A4='[logESM2.xls]per dag'!$D673
> A5='[logESM2.xls]per dag'!$D680
> A6='[logESM2.xls]per dag'!$D687
> A7='[logESM2.xls]per dag'!$D676
> 
> I need 691, 698 etc ....
> 
> Is this a bug?
> 
> thanks a lot for any help on this! 
> 
> 
> 
0
ken.wright (2489)
10/26/2006 6:30:02 AM
Reply:

Similar Artilces:

When I open up excel a worksheet that has been inputed opens up w.
How do I start excel with a blank worksheet Hi There is either some excel file in the starup folder or files are opened through Tools, Options, General Tab, "At startup open every file from folder:" (please note this option may vary since I am using a french version) HTH Cordially Pascal "EL GUAPO" <ELGUAPO@discussions.microsoft.com> a �crit dans le message de news: C033BB21-8298-46A6-8697-34B5F9BB56F0@microsoft.com... > How do I start excel with a blank worksheet No, that's not it. That does just what it says, it opens every file in that folder. H...

Why does my excel spreadsheet show ### in place of a sum?
For some reason, some autoSUM cells in my spreadsheet are showing up as "#" symbols even though they are correctly calculated in the function window... Any ideas? Try making the column wider. "S. Teichman" wrote: > For some reason, some autoSUM cells in my spreadsheet are showing up as "#" > symbols even though they are correctly calculated in the function window... > Any ideas? Typically the # characters indicate that the column is not wide enough to display the result. Try making the column wider. -- Cordially, Chip Pearson Microsoft MVP - E...

How do I merge similar data across different excel worksheets?
If you have a unique key value that's on each sheet, you could use =vlookup() or =index(match()). You could look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions02.html and http://www.contextures.com/xlFunctions03.html MK wrote: -- Dave Peterson ...

Bugs?
Hi, Why does Outlook "receive" the same email twice? When I get mail on a pop3 account it seems to want to download it twice. Most annoying! I leave Outlook running it downloads incomming mail as expected. When I reboot and run Outlook again it will download already downloaded email. And secondly when I quit Outlook it frequently asks me if I want to save over the normal template. Obviously this is coming from Word but Outlook may have something to do with the problem. I'm using Word as the email editor. I have Outlook 2003 running on Windows XP. Any help or suggestio...

Word 2004: Style List / Style Dialogue Box Bug
Hello all -- I'm in Word 2004. Upon opening a new document, it defaults to showing only a couple of styles in the style list. I want to be able to see all of them. Word Help suggests going to the Formatting Palette and selecting all styles. This works, but I don't use the Formatting Palette that often. The second option: View All Styles in the Style Dialogue box doesn't work. This is the one I want to use. It tells users to go the the Format menu and select Style command, then select All Styles on the List popup menu. I did this and clicked apply, but when I go back to the documen...

Macros in Excel
I have a question about macros...why does Excel open a workbook for a macro? I created 2 of them (I'm a beginner with Excel macros) and I noticed that when I run a macro another workbook opens with the macro name. Is there any way to prevent this annoying feature? I used to macros in WordPerfect eons ago and this never was an issue. Hi Karen, Don't you want to be able to use the macro in *any* workbook. To me that is what macros are all about, extending Excel, to provide facility that is not there, or should have been there. Correctly installed into personal.xls you wo...

EXCEL #37
�Podr�a algui�n explicarme para qu� sirve la Herramienta Solver y c�mo funciona? Gracias! Hola, solver te sirve para buscar resultados cambiando otros datos. Por ejemplo, tenes el total de ventas y costo, lo que te da un margen del 10%, con solver le podes pedir saber que cantidad de ventas necesitas para alcanzar un margen del 12% Si esto te ayudo por favor hace click en Yes, gracias "7C agotz" wrote: > ¿Podría alguién explicarme para qué sirve la Herramienta Solver y cómo > funciona? Gracias! > > > > ...

Excel Relative Reference
On Excel XP, how do you switch back to the normal column/row headings where it would equal A1 rather than the relative which is 1:1? Please let me know how I can fix this. Thanks, Trisha Hi Trisha, You probably refer to the Reference style: Tools>Options>General, uncheck R1C1 Reference Style -- Kind Regards, Niek Otten Microsoft MVP - Excel "Trisha" <anonymous@discussions.microsoft.com> wrote in message news:332901c3fd54$8b9dca40$a001280a@phx.gbl... > On Excel XP, how do you switch back to the normal > column/row headings where it would equal A1 rather ...

Excel:I set the font color to be "Red". Next time file is open, f.
Say I sent the font color to Red in the cell. The next time I open the file, the font color in the cell changes? Why Maybe you didn't save the file? >-----Original Message----- >Say I sent the font color to Red in the cell. The next time I open the file, >the font color in the cell changes? Why >. > ...

Please verify BUG in Microsoft Windows XP/2003 Accessibility *MPS*
Hello, The Problem (Multi Processor only): This occurs when there is MORE than one Logical/Physical CPU on the system. When a console winevent hook function is active, changing the codepage of a console window causes the console window to not respond. I set a hook using SetWinEventHook to get notified on events EVENT_CONSOLE_CARET to EVENT_CONSOLE_END_APPLICATION all is well, events are coming. When I open a CMD window and change the codepage to a different codepage than the default one, the window stops responding. From my tests it appears that the problem is somewhere in the SetCo...

excel chart to tiff or eps format
Hello, I have created several detailed charts in excel for a paper that I am sending to a journal. The journal requires that my files have eps or tiff extensions. How do I convert the excel chart into tiff or eps? I have tried to 'print' to tiff with microsoft office document image writer , but the quality of the chart is too low. I also tried to copy and paste the graph into paint shop pro and to save it as .eps , but agai the quality is too low. Any other suggestions would be much appreciated. Thanks. Raoul In VBA you can use ActiveChart.Export "C:\test\MyChart.tif"...

Sharing Excel files
I'm sharing a file on our network (approx 1mb in size). occasionally, the print areas are completely cleared. aside from user intervention, is there anything that can cause this? Not sure about Excel 2000, but in Exel 2002 there is a sharing functio you have to turn on. From you discription, you do not have it turne on. Tools > Share Workbook... Most of the options that come up are self-explainitory, but you ma want to review the help files are keeping tracking of a change histor if you want to do that ----------------------------------------------- ~~ Message posted from http...

How do I delete cells in Excel without changing the data in others
I want to get rid of some of the rows in my spreadsheet but the data in the ones I am leaving behind are linked by formulas? As soon as I delete them all the data goes from the the others. Gemma, I may not understand your situation. An example of what I think you're saying is: Cell A2: A1+1. You want to delete cell A1, but leave A2 with the value it currently has. If this is the case, you need to copy A2 and the, using paste special, paste the value back into A2. This way A2 no longer has any formula at all and will remain unchanged when you delete A1. Art "G...

Excel cell format #2
how can i display preceding zeros in excel without formatting as text? In article <5CBDC357-B0B2-49C2-906C-73E94C6172B9@microsoft.com>, "rockfam8" <rockfam8@discussions.microsoft.com> wrote: > how can i display preceding zeros in excel without formatting as text? Precede your entry with an apostrophe. For example... '012345 Hope this helps! Or give it a custom format like 00000 (as many 0's as you need) rockfam8 wrote: > > how can i display preceding zeros in excel without formatting as text? -- Dave Peterson ...

why are some gridlines in an excel worksheet dark and some light?
I have lots of data entered==6-700 different names, address etc. Some of the gridlines printed dark, some were light and some were dotted..How do I make them uniform? Select your entire grid and set the borders the way you want them. -- JoAnn Paules MVP Microsoft [Publisher] "Joyal" <Joyal@discussions.microsoft.com> wrote in message news:9074E731-EBD6-4C49-A780-ABFA29ACD281@microsoft.com... >I have lots of data entered==6-700 different names, address etc. Some of >the > gridlines printed dark, some were light and some were dotted..How do I > make > ...

Excel 2000 #2
How come the Move or Copy Sheet under Edit is grayed out for this particular data file? I can't access it. Is the workbook protected? Tools|Protection|Unprotect workbook? (You may need a password) Jerick wrote: > > How come the Move or Copy Sheet under Edit is grayed out for this particular > data file? I can't access it. -- Dave Peterson ...

deleting phrases in Excel and/or Word
My question is for beginners but I am working with spreadsheets and documents which contains a lot of the same words I want to delete. Can anyone please tell me how I can do this? THANK YOU BEFOREHAND FOR YOUR HELP! Here's one quick play using SUBSTITUTE for progressive deletion .. Assume text data is running in A1 down Enter the exact words/phrases (case sensitive) you want deleted in say K1 down. Eg assuming you have 3 words to delete: in K1: the, in K2: Oops, in K3: milk Then place in B1: =TRIM(SUBSTITUTE(A1,INDEX($K:$K,COLUMN(A1)),"")) Copy B1 across by 3* cols to ...

Office / Excel 2003
Hello, Hope someone here can help me out. Upgraded from Excel 97 (Word too) to Office Pro 2003 installing everything on an XP Pro desktop connected to a network running Microsoft SBS2000. Now, when I simply open Excel, I get: "The file cannot be opened because your virus scanner has detected a problem with the file.". But I'm not trying to open any file, just Excel. HELP! I get the same thing in trying to open an old file, saved in Excel 97. If I add anything to one cell (or two, just something at all) and then save the file it saves it but when I go to open it with the O...

Formula for computing work time in Excel
Hi, For instance, A1 = 8:20, B1 = 16:30. I need to compute in C1 the following: B1 - A1 - 0.5 hour. What is the formula? If I use formula B1 - A1 - 0:30 I get invalid value. Thanks, -- Alex Vinokur email: alex DOT vinokur AT gmail DOT com http://mathforum.org/library/view/10978.html http://sourceforge.net/users/alexvn =B1-A1-TIME(0,30,0) -- HTH Bob Phillips "Alex Vinokur" <alexvn@big-foot.com> wrote in message news:uGyW$ZgvFHA.2008@TK2MSFTNGP10.phx.gbl... > Hi, > > For instance, A1 = 8:20, B1 = 16:30. > I need to compute in C1 the fo...

Save as Web Page for Excel 2000
I am new to Excel 2000 and attempted to save an Excel Document as Web Page that can be changed interactively. However, when I change the content in that spreadsheet via IE, it seems that the underlying spreadsheet is not updated. Is there anything wrong ? Thanks You should "publish" not save as. In the SaveAs Web Page ... dialog box, click "publish". You have to edit via the excel.htm as it really isn't an IE web page. If you edit with IE you are only changing the html not excel data. . >-----Original Message----- >I am new to Excel 2000 and attempted...

Need help with excel autofill VBA style
Hi everyone, I need help with autofill. The situation is that I have an excel spreadsheet that contain 2 worksheets: sheet1 and sheet2. The sheet1 basically contains just raw data; while sheet2 contains a number formula that are used for calculation. <Sheet1> <Sheet2> A B A B 1 2 1 1 Sum AVG 2 3 9 2 =SUM(Sheet1!A1:Sheet1!B1) =SUM(Sheet1!A1:Sheet1!B1) 3 7 1 3 4 8 2 4 The question is that I want to write a VBA script that will autofill the formula in colum...

Excel 2003
Hi! I often work with spreadsheets of 4-10 columns and as many as 400 rows. Lately (maybe since I loaded 2003, I can't remember) whenever I try t copy or cut large numbers of cells I get the message: "The picture is too large and will be truncated." I used to cut and copy large numbers of cells in Excel for XP all th time...can anyone help? thanks! Greg -- Message posted from http://www.ExcelForum.com I'm the same guy who posted the question... ...but here's the thing I've since figured out: Even though I get that message, Excel still copies all my data to...

Publique sus archivos Excel en la web en tiempo real
Publique sus archivos Excel en la web en tiempo real. consultas a consultor_4@hotmail.com presupuestos sin cargo. ...

POS (Not RMS) Item Not Found After Creating
We added two new items to our inventory, both bottled juices, creating them using the Code 39 barcode on the each item's label. After adding them, we tested their availability (price, in stock, description) by scanning each barcode. The POS system prompts us to Create New Item (which we define as not found in the database). Checking the item list, we do not find the items we just created. Searching for Item Number, Description, etc, produces no search results. So, we attempted to re-enter them into the POS system. On one item, a prompt states Item Description already in use. O...

Deleting pictures in Excel using visual basic
I have written a visual basic module to insert pictures from a list contained in a worksheet. I have figured out how to delete the picture when I load the next picture. For this I need to know the picture number. My problem is the indexing or the picture number. The number of shapes ActiveSheet.Shapes.Count doesn't change But the picture number does increment. I am using workaround by updating the picture number in a cell on the active sheet BUT this is a kluge! More importantly, I will be using this to display a large number of images [1000s 100000s]. I don't know wheter there is an ...