Excel will not autofill horizontally (using an abs. reference for a column on a dif sheet)

Hey guys,

So i've been trying to figure this out for awhile now.

I am trying to drag my excel formula to the right. My formula is =+'Hard cost Schd'!$E4*.86. When I drag it horizontally, it simply uses the 3 cells that I highlight, $E4, $E5, and $E6 and then copies those to the right. It will not continue my formula to $E7, $E8, etc.

Automatic calculations is on AND it actually works correctly if I go vertically.. it just won't go horizontally.

What am I missing?


UPDATE: I have been able to get the second value in the column of the sheet I'm trying to reference by using =INDEX('Hard cost Schd'!$E$1:$E$31,COLUMN('Hard cost Schd'!E1)). It allows me to fetch the second value and drag that to the right but I still cannot get the first value.. and I cannot change the formula to $E$0 obviously or else I get an error. I'm not exactly sure how to get by this.

I tried this. When I use ='Hard cost Schd'!E4*.86 and drag it to the right, it changes the formula to ='Hard cost Schd'!F4*.86, ='Hard cost Schd'!G4*.86, etc

I actually found this somewhere else and got it to work. Thanks!

Question though... can you explain what the INDEX function does? I just wanna make sure I actually understand it if I need to use it again.

Originally Posted by TMS

Try:

Formula:

Please Login or Register  to view this content.

=INDEX('Hard cost Schd'!$E$1:$E$31,COLUMN('Hard cost Schd'!D1))*0.86

^^That is the formula I used.. I don't understand why it had me use D1 for the column and not E1...

Originally Posted by TMS

Try:

Formula:

Please Login or Register  to view this content.