Excel: Fill down formulas in columns adjacent to data does not work!!! 1

[jollyreaper]

Fill down DOES NOT WORK. I have a big data dump in one of my sheets and I have column A serve as an index for it -- the dump starts in column B and goes off to the right for a zillion columns. Based off of conditions in that dump, I concatenate a reference number that is then referred to from another sheet. Every few days I have to go back in and copy down the formulas. The data range keeps growing and growing but the formulas are left right where they were.

Anybody have workarounds for this?

 

[xlbo]

Copy down formulae only works to the RIGHT of a dataset - this is due to the order in which excel calculates a worksheet

Simplest solution is to place index to the right
If this isn't possible due to other formulae etc then you would need to use VBA to populate the range

That is all.....

 

[jollyreaper]

Hmm. Well, there's no reason it can't be on the right. I'll just stick it there instead. Where did you discover that tidbit? I don't think I saw that mentioned anywhere in the documentation. Grr.

Thanks!

 

[xlbo]

tried doing similar myself one time. Didn't work. Switched formula to the right and it worked. Looked it up on an excel website that deals with calculation trees and orders.

It is the same reason that for a vlookup formula, the lookup value must be to the left of the dataset.

 

[jollyreaper]

And I just love the illogic of it. Naturally, if vlookups are on the left, and if you are trying to something similar, it just makes sense to stick it on the right.

Ah, Microsoft, how you taunt us.

 

[xlbo]

I dunno - I think it is pretty logical - everything works from left to right.....