Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Transpose
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Transpose
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 On Jan 8, 5:24*pm, snax500 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Transpose
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) -- David Biddulph "snax500" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a formula to transpose numbers, e.g. change 36 to 63 | Excel Discussion (Misc queries) | |||
formula to transpose | Excel Worksheet Functions | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
Formula Transpose | Excel Discussion (Misc queries) | |||
Transpose formula | Excel Worksheet Functions |