extend a series of formulas

[Guest]

I have a column that represents values from cells in another tab in the same workbook. On one tab the series is in a row. A5, B5, C5, etc

In my new tab I need to represent those values in a colum. I have linked the new tab cells to the original tab cells. Periodically I need to extend the series on both tabs. The problem I am having is when I try to extend the column of cell references, it wont extend the series like I want....

example, the column on the new Tab looks like
+Tab1!H$11
+Tab1!I$11
+Tab1!J$11

I put the '$' symbol to keep it from extending the row number(110,111,112, etc). Any ideas on how to get it to extend the column letter (H,I,J, etc) when I extend the series down within the column

Thanks
Mik

 

[Frank Kabel]

Hi Mike
if you start in row 1 (e.g. A1) on your new sheet enter the following
formula
=OFFSET('Tab1'!$G$110,0,ROW()-1)
and copy this down

 

[Guest]

Frank

Thanks a lot. That worked great. It took me a minute to realize that you meant to have the '1' in ROW()-1 = to whatever row number i started that series in. After that it worked like a champ

Thanks again
Mike

 

[Peo Sjoblom]

You can make it less row dependent if you use this instead


ROW(1:1)-1

--

Regards,

Peo Sjoblom

Frank,

Thanks a lot. That worked great. It took me a minute to realize that you

meant to have the '1' in ROW()-1 = to whatever row number i started that
series in. After that it worked like a champ.