copy formulas by dragging vertically

[Guest]

I have Sheet 1 with raw data in it. Sheet 2, Cell A1 is looking to Sheet1,
H1 for the data (ex. =Sheet1!H1). What formula can I use in cell Sheet2, B1
that will tell it to look to cell Sheet2, A1 and copy the cell just below
Sheet1, H1 or (=Sheet1!H2)?

 

[RagDyer]

Not exactly what you asked for, but will this work for you?

In Sheet2, A1,
Enter this formula, and drag right to copy as needed:

=INDEX(Sheet1!$H:$H,COLUMNS($A:A))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Jack Sons]

Rag,

A little bit related:

In sheet2 B1 I have = Sheet1!D4 (and a lot more of that kind of stuff).
Works fine, but frequently sheet1 is "refreshed". In the refreshing process
rows 1 to 200 are deleted. After that I see in sheet2 B1 =Sheet1!#VERW!
(don't know the English error code, I use the Dutch version). Is there a
method to overcome this?

Jack Sons
The Netherlands

 

[RagDyeR]

Is this what you're looking for:

=INDIRECT("Sheet1!D4")
?
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Rag,

A little bit related:

In sheet2 B1 I have = Sheet1!D4 (and a lot more of that kind of stuff).
Works fine, but frequently sheet1 is "refreshed". In the refreshing process
rows 1 to 200 are deleted. After that I see in sheet2 B1 =Sheet1!#VERW!
(don't know the English error code, I use the Dutch version). Is there a
method to overcome this?

Jack Sons
The Netherlands

 

[Jack Sons]

RD,

That's it! Thanks.

With formulas like that autofilling won't work? I want to cover some
onehundred cells, very tedious.

When I try in A4 of Sheet2 (the name of the other sheet is S05-06 and the
F"&ROW(A4) is for autofilling purposes)

="=INDIRECT("&"'"&"S05-06'!F"&ROW(A4)&")"

I get neatly

=INDIRECT('S05-06'!F4)

but that is text, I'm afraid. Anyway, it does not result in the value of F4
of sheet S05-06.

Also, autofilling horizontally will not work. An extra (complex) trick is
needed to change the F into G into H etc. Brrrrrr, I think I'm not on the
right track.

Can you help me further?

Jack.

 

[RagDyer]

This will link F1 on Sheet S05-06,
To any cell that you enter it into,
In Sheet2 of the same WB:

=INDIRECT("'S05-06'!F"&ROWS($1:1)&"")

And copy down as needed.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Jack Sons]

RD,

Marvellous.

What is the secret of &"" at the end of the formula? How does it work? It
makes all the difference, I think.

Jack.