copying data from a different sheet and autofilling.

[larry]

I am trying to open a work sheet for each day of the
month and the new sheet has some columns where the data
is copied from the previous day.(like closing balance of
day1sheet!c1 going to opening balance on day2sheet!a1.The
macro automates the sheet name as day1!,day2!etc on each
day. I am using the Indirect function to get the data.
The problem is The first column is filled with the right
data.But the cells below has the same value of cell c1
and not like c1 to a1,c2toa2,c3toa3 etc..The formula I
am using is,
A1=INDIRECT("'" & $H$4 & "'!C1")
H1 HAS A CONCATENATE FUNCTION TO RETURN DAY1,DAY2,DAY3
etc.,
A1 HAS THE RIGHT VALUE OF C1.BUT WHEN THE FORMULA IS
DRAGGED DOWN TO THE CELLS BELOW,C1 REMAINS C1 AND NOT
CHANGING TO C2,C3,C4 ETC.,
Can anyone help,p[lease?
I appreciate your efforts.
Larry.

 

[Guest]

Hi

Try this::
=INDIRECT("'" & $H$4 & "'!C"&ROW(1:1))

 

[larry]

Thanks Andy.It did work when tested. But when i put in
the macro,it shows compile error.
I would really appreciate the code for it inside a macro.
larry.

-----Original Message-----
Hi

Try this::
=INDIRECT("'" & $H$4 & "'!C"&ROW(1:1))

--
Andy.

I am trying to open a work sheet for each day of the
month and the new sheet has some columns where the data
is copied from the previous day.(like closing balance of
day1sheet!c1 going to opening balance on day2sheet! a1.The
macro automates the sheet name as day1!,day2!etc on each
day. I am using the Indirect function to get the data.
The problem is The first column is filled with the right
data.But the cells below has the same value of cell c1
and not like c1 to a1,c2toa2,c3toa3 etc..The formula I
am using is,
A1=INDIRECT("'" & $H$4 & "'!C1")
H1 HAS A CONCATENATE FUNCTION TO RETURN DAY1,DAY2,DAY3
etc.,
A1 HAS THE RIGHT VALUE OF C1.BUT WHEN THE FORMULA IS
DRAGGED DOWN TO THE CELLS BELOW,C1 REMAINS C1 AND NOT
CHANGING TO C2,C3,C4 ETC.,
Can anyone help,p[lease?
I appreciate your efforts.
Larry.

.