Fill & Drag to Right !! HELP !

[Jez]

Hi,

I have a spreadsheet with to worksheets, Sheet1 & Sheet2.

On Sheet1 column 'I' has a formula that gets it's data from column
'A'.
=MID(A8,12,4)

This works fine and 'I' is populated correctly.

On Sheet2 I have rows of fields that needs to be dynamically linked to
column I on Sheet1.
Sheet2 B4 has the formula =VALUE(Sheet1!I8) which produces the correct
result from Sheet1 I8.

What I would like to do is copy the formula from B4 to C4, then D4,
then E4 etc..

If I try this I get:
=VALUE(Sheet1!I8), =VALUE(Sheet1!J8), =VALUE(Sheet1!K8), =VALUE
(Sheet1!L8) etc

NOT:

=VALUE(Sheet1!I8), =VALUE(Sheet1!I9), =VALUE(Sheet1!I10), =VALUE
(Sheet1!I11) etc.

How do I do this ??

Thanks

 

[Stefi]

Sheet2 B4 formula:

=VALUE(INDIRECT(ADDRESS(COLUMN()+6,9,4,1,"Sheet1")))

Drag it to the right!

Regards,
Stefi


„Jez†ezt írta:

 

[Mike H]

Hi,

try this

=VALUE(INDIRECT("Sheet1!I"&COLUMN(H1)))

Mike