Filling formulas in transposed direction?

[mschneider1969]

I wish to simply refer a cell in one tab to a cell in another tab within the same workbook. So Tab1 cell A1 = "Tab2 cell A1"


Then, in Tab1, fill DOWN the formula but moving the reference ACROSS not down.

So that in Tab1, when I fill down three cells, the resulting formulas would be:

A2 = Tab2 B1

A3 = Tab2 C1

A4 = Tab2 D1


I hope this is clear

 

[NARAYANK991]

Hi Schneider ,


For the specific cell addresses that you have mentioned , you can use :


=OFFSET(Sheet1!$A$1,0,ROW()-1)


where , Sheet1 is Tab2 , and this formula can be in any other sheet.


Narayan

 

[vasim]

You can use ={transpose(range)} formula as an array formula

 

[mschneider1969]

I tried the fromula =OFFSET(Sheet1!$A$1,0,ROW()-1)

It did not work when I filled the forumula down a column.

All of the filled cells referred to the same cell without sliding right.

How do I get the reference to slide right, while filling down?


Thanks, and sorry for my simple problems.

 

[Luke M]

mschneider,

Are you sure your workbook is in automatic calculation mode? The formula Narayank gave should work correctly, as it tells the formula to look at A1, then 1 cell to the right of A1 (B1), and then 2 cells to the right (C1), etc.

 

[NARAYANK991]

Hi Schneider ,


If you mean that the formula remains the same in all the cells down the column , you are right.


However , the values returned by the formulae will be different , and will correspond to the values in cell B1 , C1 , and so on.


Narayan

 

[mschneider1969]

Okay, I must be an idiot. I can't get the offset forumula to work. Apologies.


So in Tab "Model", i have the folowing data

Cell AY66, AY67, AY68, AY69

data $1.50 $1.75 $2.00 $2.25


In Tab "Summary", I'd like this data to simply re-display as:

Cell Data

B5 $1.50

B6 $1.75

B7 $2.00

B8 $2.25


I've simplified this, because I have many more things to re-display, but I would GREATLY appreciate your final help.

 

[NARAYANK991]

Hi Schneider ,


The situation you have outlined does not call for using the OFFSET function.


In cell B5 ( on the Summary tab ) , use the formula =Model!AY66 ; copy this down to B6 , B7 and B8 , and Excel will adjust the references correctly.


The earlier use of OFFSET was to convert the column references to row references.


Narayan