fill copy of link

  • Thread starter Thread starter jv
  • Start date Start date
J

jv

Good day to all,

I have 2 different report sheets whereas i have to
fillcopy 8 columns of datas from Dept.xls.

Hereunder are my sample

Master.xls
Col.A B C D E F
Month Expenses Salaries Purchase Sale Cost
January 12,613 14,500 143,330 109,351 78,574
February
March

Dept.xls
A B C D E H I J K
Jan Feb Mar Apr May Jun Jul Aug

Expenses 12,613 3,879
Salaries 14,500 26,000
Purchase 143,330 167,207
Sales 109,351 135,174
Cost 78,574 152,239

From the above example, making all the links for the
January row of Master.xls, i would like to fill copy down
for the remaining months. When I copy this down to the
next row
='[Dept.xls]Report'!B3

it gives me: ='[Dept.xls]Report'!B4

I want the result to be like this:
='[Dept.xls]Report'!C4
='[Dept.xls]Report'!D4

For your kind assistance.

Thanks and regards

jv
 
Think your description of the sample tables is a little mixed up.

I'm assuming you have the situation below:

In sheet: Report of Dept.xls
----------------------------------
Col.A B C D E F
Month Expenses Salaries Purchase Sale Cost
January 12,613 14,500 143,330 109,351 78,574
February
March

The data above starts in B2 down/across
(i.e the top left corner value "12613" is in B2)

In sheet: Summary of Master.xls
---------------------------------------
A B C D E H I J K
Jan Feb Mar Apr May Jun Jul Aug
Expenses 12,613 3,879
Salaries 14,500 26,000
Purchase 143,330 167,207
Sales 109,351 135,174
Cost 78,574 152,239

And what you want is to fill in the figs for the table above
with corresponding data from sheet: Report of Dept.xls

The figures above also starts in B2 down/across
(i.e the top left corner value "12613" is supposed to be returned in B2)

Try this:

In sheet: Summary of Master.xls
---------------------------------------
Put in B2:
=INDIRECT("[Dept.xls]Report!"&CHAR(ROW()+96)&COLUMN())

Copy B2 down to B6
(this gives all the 5 January figs, viz. figs for :
Expenses, Salaries, Purchase, Sales & Cost)

Select B2:B6, copy across to M6
(this gives all the figs for the 12 months Jan - Dec)

Above requires Dept.xls be open to work.
 
Max,

Thanks for helping me out, actually, what i would want is
the information from Dept.xls will be transferred to
Master.xls but your tips still apply to my problem, and it
works great.

jv
-----Original Message-----
Think your description of the sample tables is a little mixed up.

I'm assuming you have the situation below:

In sheet: Report of Dept.xls
----------------------------------
Col.A B C D E F
Month Expenses Salaries Purchase Sale Cost
January 12,613 14,500 143,330 109,351 78,574
February
March

The data above starts in B2 down/across
(i.e the top left corner value "12613" is in B2)

In sheet: Summary of Master.xls
---------------------------------------
A B C D E H I J K
Jan Feb Mar Apr May Jun Jul Aug
Expenses 12,613 3,879
Salaries 14,500 26,000
Purchase 143,330 167,207
Sales 109,351 135,174
Cost 78,574 152,239

And what you want is to fill in the figs for the table above
with corresponding data from sheet: Report of Dept.xls

The figures above also starts in B2 down/across
(i.e the top left corner value "12613" is supposed to be returned in B2)

Try this:

In sheet: Summary of Master.xls
---------------------------------------
Put in B2:
=INDIRECT("[Dept.xls]Report!"&CHAR(ROW()+96)&COLUMN())

Copy B2 down to B6
(this gives all the 5 January figs, viz. figs for :
Expenses, Salaries, Purchase, Sales & Cost)

Select B2:B6, copy across to M6
(this gives all the figs for the 12 months Jan - Dec)

Above requires Dept.xls be open to work.

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
---------------------------------------------
jv said:
Good day to all,

I have 2 different report sheets whereas i have to
fillcopy 8 columns of datas from Dept.xls.

Hereunder are my sample

Master.xls
Col.A B C D E F
Month Expenses Salaries Purchase Sale Cost
January 12,613 14,500 143,330 109,351 78,574
February
March

Dept.xls
A B C D E H I J K
Jan Feb Mar Apr May Jun Jul Aug

Expenses 12,613 3,879
Salaries 14,500 26,000
Purchase 143,330 167,207
Sales 109,351 135,174
Cost 78,574 152,239

From the above example, making all the links for the
January row of Master.xls, i would like to fill copy down
for the remaining months. When I copy this down to the
next row
='[Dept.xls]Report'!B3

it gives me: ='[Dept.xls]Report'!B4

I want the result to be like this:
='[Dept.xls]Report'!C4
='[Dept.xls]Report'!D4

For your kind assistance.

Thanks and regards

jv
Click to expand...


.
Click to expand...
 
Back
Top