Forum Discussion
Excel will not autofill horizontally (using an absolute 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?
2 Replies
- Use the transpose formula for your working:
https://support.office.com/en-us/article/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027
It does need using the array function, read a bit about it before actioning:
https://support.office.com/en-us/article/create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d?redirectSourcePath=%252fen-us%252farticle%252farray-formulas-c4fdebf4-f41b-4438-8b8d-ca511d66e44c&ui=en-US&rs=en-US&ad=US It might be the $ in front of the E. I know you described it as an absolute reference, so that means that no matter what column you're in as you drag or extend, you want the reference to be to column E. I may be missing something, but you might try removing the $ so that it isn't an absolute reference but a relative one and see if that solves the problem.
