Offset from the location of another cell

Is there any way to use the offset formula in reference to the location of another cell?

Here is a simplified version of my problem:

Sheet1!A1 refers to Sheet2!A1.
Sheet1!B1 refers to Sheet2!D1.
Sheet1!C1 refers to Sheet2!E1.

Is there any way that I can say that Sheet1!A1=Sheet2!A1 and then use the offset formula based on the location referenced in Sheet1!A1? The cells being referenced are merged cells one column wide and varying rows long, so there is no real pattern to follow except that the corresponding cells (the ones that I want to use the offset formula on) are all taking up the same amount of rows and are an equal amount of columns over (Sheet1!B1 refers to 3 columns over from Sheet2!A1, Sheet1!B1 refers to 4 columns over from Sheet2!A1). The spreadsheet is a fairly complex one and it would be much cleaner and less likely to have any mistakes if I was not referencing each cell invividually.

Do you mean that you want to have a formula that means "Refer to a cell offset relative to whatever cell Sheet1!A1 is referring to"?

Formula:

Please Login or Register  to view this content.

The 99 is an arbitrarily large number to account for any size string. It actually can't be that long but I use 99 because it's eye-catching and reminds me that I just picked a big number.

So using your first example, this formula will refer to Sheet2!B3, which is 2 rows down and one column to the right of Sheet2!A1

Formula:

Please Login or Register  to view this content.

This formula will give you an error if the contents of the cell is anything other than a formula that refers directly to another cell.