Linking

Excelled

New Member
Joined
Aug 15, 2002
Messages
31
I have got three sheets arranged like:
Sheet1
A1=Vicki B1=1 C1=1
A2=Eric B2=4 C2=2
Sheet2
A1=Vicki B1=2 C1=2
A2=Eric B2=10 C2=1
The third sheet references values in sheet 1 and 2 and calculates a total
A1=Vicki B1='Sheet1!B$1'+'Sheet2!B$1' (results in 2+1 =3)
A2=Eric B2='Sheet1!B$2'+'Sheet2!B$2'
(results in 4+10=14)
If I was to drag B1 to B2 to get the result of B2, the formula 'Sheet1!B$1' gets copied and not 'Sheet1!B$2'. I have a referencing problem here. But it works the way I want it when dragging B1 to C1. The rows and columns get updated necessarily. I was wondering if anyone knew of a way so that if was to drag B1 to C1 the formula would calculate Sheet1C1+Sheet2C1 in C1.
and when I drag B1 to B2 at the same time, the formula in B2 would calculate Sheet1B2+Sheet2B2
This message was edited by excelled on 2002-08-26 15:09
This message was edited by Excelled on 2002-08-26 15:10
This message was edited by Excelled on 2002-08-26 15:11
This message was edited by Excelled on 2002-08-26 15:13
This message was edited by excelled on 2002-08-26 15:49
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
So, when you drag it down, you want the column to reference C ? Is that correct?

Try something like this:

=OFFSET(Sheet1!B$1,0,ROW()-1)

This is assuming the first formula is written into row 1.
 
Upvote 0
I wanna be able to drag it down to B2 from B1, get a value for B2 accordingly. Also when I drag it sideways from B1 to C1, get the value for C1 accordingly
 
Upvote 0
But where should this OFFSET function go in..B2 and C2 already have formulas in them
This message was edited by Excelled on 2002-08-26 16:41
 
Upvote 0
I think that it's as simple as using relative references instead of absolute. That means, change

=Sheet1!$B$1+Sheet2!$B$1

to

=Sheet1!B1+Sheet2!B1
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top