Formula Transpose

[snax500]

In Excel2000, I have the following formula in cell b2...

=+'[08 Expenses.xls]Facilities'!$U$101

I would like to copy this across the row so that cell b3 reads

=+'[08 Expenses.xls]Facilities'!$U$102

and b4 =

=+'[08 Expenses.xls]Facilities'!$U$103 and so on.

Is there some formula that I can use to do this so I don't have to
edit each cell?

THanks

 

[David Biddulph]

Well if you copy from B2 to B3 and B4 you will get exactly what you ask for
if you omit the $ in front of the row number
(so ='[08 Expenses.xls]Facilities'!$U101 will become ='[08
Expenses.xls]Facilities'!$U102 and then ...103) [note that you don't need
the + sign after the = in the formula], but as you talk of copying across a
row, you may intend to copy from B2 to C2 and D2?
If so, try =OFFSET($U$101,COLUMN()-COLUMN($B2),0)

 

[Pete_UK]

You could try something like this:

=INDIRECT("'[08 Expenses.xls]Facilities'!$U$"&100+COLUMN(A1))

in B2, then copy across.

INDIRECT will only work with open workbooks, so you will need to
ensure that the file "08 Expenses.xls" is open for this to work.

Hope this helps.

Pete