Referencing a cell in Excel using values from other cells in different worksheet (tab)

I'm trying to set up a Excel sheet for a running log.

What I'm trying to do is to get info from a cell that is 7 rows dowm from cell H8 on worksheet Log. This is so that I can just drag the forumla over to other cells, not having to manually do the "= --> mark cell you want --> press enter" for each cell. That would be 365 cells to mark up! o.O

On to the problem:

My initial idea was Log!H8+7 and the like, but since I'm here that obviously did not work. I did some searching online and found a few posts talking abot =INDEX, RC[n] and the like, which I have been playing around with for the past hour or so, but I got none of them to work.

So basically I want cell D6 in worksheet 'Plan' to refer to the cell 7 rows below H8 in worksheet "Log"

Anyone that can point me in the right direction?:)

Answer

Use the INDIRECT function to calculate or build a cell reference. It converts something that is a string that looks like a cell reference to an actual cell reference, and you can build that string by combining text and calculations. You can use it with either "standard" format cell referencing (like A5), or row-column (RC), format.

In standard format, you could do something like:

=INDIRECT("Log!H"&8+7)

The RC format can be handy for relative addresses, but the benefit is mainly to express a reference relative to the current cell. For example, if you were on the Log sheet in cell H8, you could use this to reference the cell 7 rows lower:

=INDIRECT("R[7]C",0)

You need the 0 in the optional last parameter to indicate that the string should be interpreted as an RC style address. The general format is R8C8 (row 8 column 8, which would be H8). If the reference is to the current row or column, you can leave off that number. You can do relative addresses by putting a positive or negative number in brackets, which is the number away from the current position.

This style of addressing can also be useful if you need to calculate a column, since you can do it with numbers instead of letters. So, for example, say you want to refer to the cell 8 rows down and 3 columns to the left of H8, you could use:

=INDIRECT("Log!R"&8+7&"C"&8-3,0)

Doing this kind of math in the formula can be useful to develop and debug the formula, or to maintain it. However, once you don't need it for that, it can overly complicate your formula. For example, without the need for this math, you could simply use:

=Log!H15

If the math is required because the value is entered data or is a formula result, don't hard code the value in the formula. Instead, use a cell reference to where the value is stored.