OFFSET function to look at separate sheet

P

poontz

New Member
Joined
Aug 25, 2008
Messages
1
I'm trying to use the offset function on one sheet to return a value from a separate sheet called Master Data.

The formula =OFFSET('Master Data'!$P$32,0,1,1,1) works just fine, the problem is that in this case $P$32 is returned from another function. How do I combine the 'Master Data'! and my function INDIRECT(ADDRESS(MATCH etc. etc)), which returns $P$32, and put that into the OFFSET function?

Side question, the INDIRECT formula returns an absolute reference, is there a way to make it not be absolute?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Can you explain what you are trying to do with your OFFSET? If you are generating a reference with INDIRECT and ADDRESS, then adjusting that would be more efficient than using OFFSET as well.

To include a sheet within an INDIRECT, use the format =INDIRECT("'Master Data'!"&cell_address_string)

As for you side question, INDIRECT does not return an absolute reference, it calculates a reference from the string provided by the ADDRESS ( in your case ). It is the ADDRESS function that can be tweaked to provide either absolute or relative references.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Problem using ADDRESS function to specify a cell range in another sheet
Replies
3
Views
410
Tetra201
Tetra201
J
  • Solved
So strange! Why is my INDIRECT function returning 0 even though the formula should be 100% correct?
Replies
6
Views
967
Peter_SSs
Peter_SSs
A
  • Question
Indirect Function with a separate Workbook
Replies
6
Views
975
AntMac
A
A
  • Solved
Finding a unique value in a range and returning the value in another column of the same row, without offset / slow formulae.
Replies
8
Views
962
AaronPMH
A
L
  • Question
Excel VBA - Return Specific Data from Master Sheet Based on Date Search + Duplicate Certain Items
Replies
1
Views
437
LookingForHelp1
L

Forum statistics

Threads
1,214,827
Messages
6,121,821
Members
449,049
Latest member
cybersurfer5000

Share this page

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