Any Excel experts in here?

[Deleted member 651465]

Hi all,

Basically we've got a series of Excel workbooks that are linked using formulas.

To copy a particular formula I just click and drag to fill the formula down multiple rows.

e.g..

MONTHLY'!B$5
MONTHLY'!C$5
MONTHLY'!D$5

Usually, this works fine but because the formula is linked to another workbook, Excel doesn't increment the column reference, so I end up with this..

='C:\Documents and Settings\User\Desktop\[FILENAME.xlsx]MONTHLY'!B$5
='C:\Documents and Settings\User\Desktop\[FILENAME.xlsx]MONTHLY'!B$5
='C:\Documents and Settings\User\Desktop\[FILENAME.xlsx]MONTHLY'!B$5

I just need the column (highlighted yellow) to increment..

It's a pain because sometimes I need to add a row between 2 values, so I can't fill the remainder of the formulas as all values afterwards would end up the same reference - time consuming if you need to add a row at the top of the table!!

If anyone has any ideas I'd appreciate it. I've Google'd around but nothing is coming up with any realistic answers, and I'm already using relative references etc.

Thanks

 

[DampCat]

I've never got auto-increments to work cross workbook, i'm afraid. It can be done, but nothing as simple as the "click and drag" answer that you're looking for

 

[kaiowas]

Remove the $ from the first formula before dragging.

Also noticed you're talking about 'incrementing the row' and 'dragging down' yet your example show you trying to increment the column, which is it that you're trying to do?

 

[Deleted member 651465]

Remove the $ from the first formula before dragging.

Also noticed you're talking about 'incrementing the row' and 'dragging down' yet your example show you trying to increment the column, which is it that you're trying to do?

I want to implement the column (the letter), that's why the $ is there (to lock the row).

Apologies.

 

[Dolph]

Are you dragging the formula down or across? Because auto-incrementing the column only works when dragging the formula across incrementing columns normally

 

[Deleted member 651465]

Are you dragging the formula down or across? Because auto-incrementing the column only works when dragging the formula across incrementing columns normally

Down.

I thought it was possible this way

 

[kaiowas]

You can drag across to increment the column then do copy>paste special>transpose to get those formulae oriented how you want them.

 

[Deleted member 651465]

I love you

Thanks, you really have saved me hours of work.