Excel Series question 1

[Jusenkyo]

Hello all

I have an excel spreadsheet with Dates and totals running down in two columns.
In another workbook I have the dates running right in row 1 and the totals below them in row 2.

What I want to do is to carry on the formula I have in row 1 that looks at the next row in the previous workbook.
i.e. A B C D etc...
A1 A2 A3 A4

At the moment, when I try to carry on the series, it changes the column name instead of the row name!
i.e. A B C D etc...
A1 B2 C3 C4

I hope that makes sense!
Cheers in advance!
J

 

[Griffyn]

I don't quite understand, but have a look at combining OFFSET, ROW() and COLUMN() together.

ie.

=OFFSET(Sheet1!$A$1, COLUMN(), 0)

When you copy that across, it will starting picking up corresponding rows on Sheet1, offset from $A$1.

 

[Jusenkyo]

Excellent, thats the one!

Thanks Griffyn!

 

[GlennUK]

You may need to use COLUMN()-1 to get it exactly as in the example, inside the OFFSET function, otherwise the results will be one column out.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.