Reading value from "column - X"

kubazabran

New Member
Joined
Feb 22, 2022
Messages
13
Office Version
  1. 365
Platform
  1. MacOS
  2. Web
Hi,

is it possible to do something like this? I have a reference to the source sheet from different sheet and I'd like to retrieve data from a column that is always 12 cols left. E. g.:

A | B
=src_data!O3 | =srcdata!(O3-12) - this would get the value of C3 in the srcdata

When I change the col in column A, the formula in B would get again -12 col from the source file.

I experimented with some formulas but I wasn't able to get the answer. Thanks!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Take a look at the OFFSET function.
You can do a Google search to see explanations and examples of how it is used.
 
Upvote 0
Hi, thanks, OFFSET works, but I'm struggling with a reference. Take a look at image below, please. I'd like to achieve something like this - when I change the value in B column, in a C column it will automatically take the value from "-12 column from a !src_data" - can it be done? I can make it work when I put to offset function ""=src_data!AA10" but then I need to change it in two places. Thanks


Screenshot 2022-03-01 at 16.41.21.png
 
Upvote 0
It looks like you actually need to get the text of the cell reference in column B.
Excel 365 should have the FORMULATEXT function which would allow you to do this.
So, in cell C3, enter this formula:
Excel Formula:
=OFFSET(INDIRECT(MID(FORMULATEXT(B53);2;100));0;-12)
and copy down.
 
Upvote 0
Solution
You are welcome!

In case you are interested, here is the logic behind it.

You can use the FORMULATEXT function to return the formula in cell B53.
However, we want to drop the "=" at the beginning, to get just the sheet/cell reference, hence the MID function.
Lastly, whenever build a range reference, Excel will not recognize it as a range, and views it as literal text. You need to use the INDIRECT function to tell Excel that it is a range and not a string.
Once we have that range reference, we just plug it into our OFFSET formula.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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