Offset cell reference, not cell itself?

Rhodes19

New Member
Joined
Apr 26, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm trying to create a cell reference that will modify the reference in another cell, not that cell itself. I've attached a picture of a super-simplified version of what I'm working on.

There's a data table below, and cells B6-B10 reference the first column of the data table. In cell C6, I'd like to have a formula that says 'One column over from the reference in B6,' which in this case would return C13. I've been playing around with an offset, but of course, that returns 'one column over from B6' itself.

Any help would be appreciated!
 

Attachments

  • Offset Question.JPG
    Offset Question.JPG
    32.2 KB · Views: 12

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi all,

I'm trying to create a cell reference that will modify the reference in another cell, not that cell itself. I've attached a picture of a super-simplified version of what I'm working on.

There's a data table below, and cells B6-B10 reference the first column of the data table. In cell C6, I'd like to have a formula that says 'One column over from the reference in B6,' which in this case would return C13. I've been playing around with an offset, but of course, that returns 'one column over from B6' itself.

Any help would be appreciated!
do you need a formula for a specific reason? the easy way to do it would be to just drag B6 accross which would then fill c6 with the forumla =c13. you could select b6-b10 and drag right and it should then just change the column to c
 
Upvote 0
Unfortunately yeah, this is a very simplified version of the real sheet. In reality, the cells are not adjacent, and the summary we're building essentially transposes horizontally-aligned data in the lower table to a vertically-aligned summary.

What I've been doing as a workaround so far is in a helper column, pasting the formulas in B6-B10 down one row, then showing the formulas, pasting those exact formulas to Notepad, and then re-pasting them back in to the correct spot on the sheet. It works, but it's not really a scalable solution for lots of data.
 
Upvote 0
think this should do what you want, if not its a staring point
=OFFSET(INDIRECT(MID(FORMULATEXT(B6),2,10)),0,1)
 
Upvote 0
Solution
think this should do what you want, if not its a staring point
=OFFSET(INDIRECT(MID(FORMULATEXT(B6),2,10)),0,1)
That works! Thank you so much! I wondered if indirects might factor in somewhere, but I don't use them often enough to have known how to make it happen.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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