Dragging formulas by row reference..?

**Freckles25nw** · 01-23-2008, 10:49 AM

Hello

Does anyone know how I can drag the below formula:

='G:\Folder\Folder\[Spreadsheet.xls]TAB'!B5

to make the B5 at the end into: B5, C5, D5, E5, F5 etc?

Thank you!

**NBVC** · 01-23-2008, 11:08 AM

Unless I am not thinking straight...and assuming you mean dragging the formula downwardss....then you will need the use of Indirect()

Please Login or Register  to view this content.

will be equivalent to ='G:\Folder\Folder\[Spreadsheet.xls]TAB'!B5

Then copy down...

The only hitch is that Indirect() only works if the other workbook is open....

If the workbook will be closed you will need to download and install the Morefunc.xll addin free from this site: http://xcell05.free.fr/english/

and use the Indirect.Ext() function.

Please Login or Register  to view this content.

**Freckles25nw** · 01-23-2008, 11:41 AM

I just tried that and it doesn't want to work. Something about too many brackets or something...

When I try to drag downwards (or sidewards), I'm just getting, B5, B6, B7, B8..... I need the ending to keep the number, but the letter changes if that makes sense (?) Putting the dollar sign doesn't seem to work either.

**NBVC** · 01-23-2008, 11:50 AM

I don't get any errors with the formula....

Make sure you copy it and paste it in the cell and only change the book names and sheet names without adding extra brackets, etc....

Also, if you are dragging sideways, you don't need my formula and if you don't have the $ before the B, then it should change to C5, D5, E5, etc.... just by dragging it....

Check that you are in Automatic calculation mode through...Tools|Options, Calculation tab...check on Automatic.