Real-world application of OFFSET

[Indystick]

I teach a college class on decision making through spreadsheet modeling and I am currently putting together a lesson on using OFFSET. I'm looking for real-world examples where OFFSET is used. I don't need the specifics (e.g., the formula text, data, etc.) rather simply examples (e.g., I use OFFSET to calculate a six month rolling average).

Thanks in advance!

 

[Joe4]

Are you talking about the Excel function "OFFSET" or the VBA function "OFFSET"?

Some Excel VBA examples:
- In a Worksheet_Change procedure, when column A is changed, your want to update some other column in the same row
- You want to find the first blank cell in a column after the last populated cell in that column

You may want to take a look here too: https://www.ablebits.com/office-addins-blog/2015/02/11/excel-offset-function/

 

[Indystick]

Hi, sorry I wasn't clear. OFFSET Excel function. I'll check out that link! I'm also hoping others may have uses that aren't listed there.

 

[Intermedius]

Hi, sorry I wasn't clear. OFFSET Excel function. I'll check out that link! I'm also hoping others may have uses that aren't listed there.

My current application is very similar to Joe4's 2nd example.

I pull a particular forecast report from SAP ECC and the data is not in perfect table format, i.e. the unique identifier appears on a different row than and within the same column as it's description. I use offset to pull the data into an adjacent column on the same row.

My 2nd application is to manipulate volatile data with a scrollbar from the Developers tab.