Parenthesis in a Formula Won't Let The CrossBars Calculate

GWee

New Member
Joined
Nov 8, 2005
Messages
24
The formula I'm using is: =INDEX(Sheet1!A:A,2)
The formula is in: Sheet2 A2
I need to repeat the formula down 800 rows.
Automatic Calculation is On.
I selected the A2 cell and clicked and hold the crossbar on right bottom corner and dragged......doesn't calculate down, just copies the A2 cell.

If you take out one parenthesis and the = to test,.....it works! I noticed that formulas with parenthesis don't calculate down, they just copy.

All my formulas calculate down just fine if I don't use parenthesis...that's a bummer.

Does anybody have a fix? :confused:
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
That formula is indexing the same cell, because you tell the formula which row to use. When you fill down it will only change cell references, if not preceeded by $, What do you want the 800 rows to contain, if you want them to contain the contents of the similar cell on a different sheet just use:-

=Sheet1!A2

And fill down

Apologies if I have got the wrong end of the stick

Andy
 
Upvote 0
very strange post this is :confused:
a few words, only examples

A2: = A1
copy down
result
A3: = A2

A2: =(A1 + B1)*2
copied down
A3: =(A2 + B2)*2

A2: =INDEX(sheet1!A:A,2)
will not change when copied down, since it is refering to an entire column

A2: =INDEX(sheet1!A2:A100,2)
copied down
A3: =INDEX(sheet1!A3:A101,2)

wondering what you mean by
If you take out one parenthesis and the = to test,.....it works!
provide an example please (if it is relevant)

when reading this
doesn't calculate down, just copies the A2 cell
seems like you think that "A,2" is cell A2, but it isn't !!
take a look at the helpfiles for INDEX
=INDEX(array,indexnumber)
the indexnumber is the "relative rownumber"
in this formula
=INDEX(sheet1!A3:A101,2)
you are refering to cell A4 which is the second in the range A3:A101

does this shade some light on the problem ?

kind regards,
Erik
 
Upvote 0
Yes I have used it that Way!

It works fine (=Sheet1!A2) but when I delete the row in Sheet1, Sheet2 gives out #REF!. =INDEX does not do that. Since INDEX is the way to go, I can't fill down with the crossbar, it just copies that cell. I have 800 rows to go and about 10 columns. Thats alot of typing.

Thanks for your reply!!

andyh69uk said:
That formula is indexing the same cell, because you tell the formula which row to use. When you fill down it will only change cell references, if not preceeded by $, What do you want the 800 rows to contain, if you want them to contain the contents of the similar cell on a different sheet just use:-

=Sheet1!A2

And fill down

Apologies if I have got the wrong end of the stick

Andy
 
Upvote 0
HOTPEPPER said:
You really should keep this to your original post:

http://www.mrexcel.com/board2/viewtopic.php?t=181022
You really should keep this to your original post:

I started out with the problem #REF, then it was fixed. Then I couldn't get it to fill down correctly as stated. It was better to make another subject line of that problem because the first problem was fixed.

But anyway It's all working now! Thanks for your help!

(y) (y) :p (y) (y)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top