Absolute Reference from another Worksheet

michaelg1

New Member
Joined
May 18, 2018
Messages
6
How do I use an absolute reference when linking data from a different worksheet? For example when I enter =+'Electric 2018'!$C7/'Use per Tenant'!C$15 it doesn't work.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,057
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

How is it not working?
Can you explain exactly what you are doing, what your expected behavior is, and what is actually happening?
 

michaelg1

New Member
Joined
May 18, 2018
Messages
6
I am trying to link data from worksheet Electric 2018, using an absolute reference and dividing it by data from the a cell on the 2nd worksheet. However only the data from the 2nd worksheet stays absolute but the data from the first worksheet does not.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,057
Office Version
  1. 365
Platform
  1. Windows
Neither of your range references are actually absolute. They are both mixed references.
$C7 locks down column C, but allows the row reference to move.
C$15 locks down row 15, but allows the column reference to move.

If you want them to both be absolute, you would need to use $C$7 and $C$15
See: http://www.cpearson.com/EXCEL/relative.aspx
 
Last edited:

michaelg1

New Member
Joined
May 18, 2018
Messages
6

ADVERTISEMENT

Correct. I only want C to be absolute but not 7. That still doesn't work.
 

pwnyadav007

New Member
Joined
Aug 9, 2013
Messages
25
I think you are dragging the formula downwords. In that case your 'Electric 2018'!$C7 will change because you didn't fixed row number if you want it as absolute then you need to fixed row number as well, like this 'Electric 2018'!$C$7.


 

pwnyadav007

New Member
Joined
Aug 9, 2013
Messages
25

ADVERTISEMENT

can you share the file with dummy data for better understanding...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,664
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-ge...nce-with-data-from-a-different-worksheet.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,057
Office Version
  1. 365
Platform
  1. Windows
Correct. I only want C to be absolute but not 7. That still doesn't work.
It seems to work fine for me.

Please explain EXACTLY what you are doing that leads to it not working the way you expect.
If I want to re-create what is happening to you on your side, I need to know the exact steps you are doing that causes this to happen (simply saying "it is not working" is not enough for us to help you!).
 

Forum statistics

Threads
1,147,635
Messages
5,742,248
Members
423,717
Latest member
rubthenut

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
Top