#### Excelled

##### New Member
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.

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

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

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

Replies
2
Views
158
Replies
0
Views
261
Replies
1
Views
219
Replies
3
Views
247
Replies
4
Views
116

1,219,770
Messages
6,150,167
Members
450,937
Latest member
kattyg261

### 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.

### Which adblocker are you using?

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

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