Fun with Excel functions 1: OFFSET. By Simon Hurst

Relative positions are very dangerous - especially in the light of the debate about Auditable Excel spreadsheets

The concept is more easily justified in a controlled environment of structured 'traditional programming'. However, a disaster scenario is far more likely where users can provide 'ad hoc' amendments without necessarily understanding the impact of adding or removing row/column; also very few spreadsheets are documented in any way which makes the whole thing more challenging

Finally auditing relative (offset) references is a very difficult task

Thanks JC for your very useful comments. Like many things I think it depends on how the function is used. In a spreadsheet with no documentation, and allowing for uncontrolled ad-hoc user changes, almost any function or formula in Excel is pretty dangerous, but the point you make is an important one, spreadsheets should always be designed with regard for reliability and auditability.

Regards

Simon

offset is indeed most useful, but I have sometimes found it more useful in combination with indirect. If you use offset in the manner described, but rather than values it returns a cell reference from a lookuptable, then you can use indirect to get the data in that cell.

I have seen this one used in several different ways - and it still amuses me.

JC makes an interesting point about the danger of spreadsheets, even if it sounds somewhat luddite. BUT the point about stuff like offset and indirect is that it can be used to make a spreadsheet much easier to audit/debug. Excel contains so much functionality - easier to keep it simple if you know what is available! So I am looking forward to more of this from Simon.

It should work if the other workbook is open I think. If you do work with linked workbooks it is usually safer to make sure they are all open anyway, as Excel has a better chance of updating any reference changes arising from the structure of one of the workbooks being changed. Using range names also helps Excel to keep track of linked cells and areas. You may find Excel's File, Save Workspace option useful when working with mutliple linked workbooks.

If you needed the Offset to work with the linked workbook closed, then you could try linking a sheet in the workbook containing the offset function to the relevant area of the other workbook, and changing the offset function to refer to the linked area, rather than the other workbook directly.

As I'm sure you're aware, you do need to take great care when linking between different workbooks and build in appropriate checks and controls.

Hope this helps

Regards

Simon

I find the offset function invaluable as it saves a lot of time when updating YTD values but I find that it only works within the same workbook. When I try to use it linking to another workbook I get a VALUE# error. Any ideas whether there is a way to link to another workbook?