reference a position in a range

refer to the 4th cell in my range

Does the range include the cell that says "January"? If so

=offset(January,4,0,1,1)

or add 3 cells together in my range say 5th, 6th & 7th cells

=sum(offset(january,5,0,3,1))

Thanks Teylyn,
I am getting a name? error when I use that formua. Is it because the worksheet I am trying to reference is not in the workbook that has the formula. What I did was I had my equal sign and then clicked on the worksheet with the range and when I type in "january" it does light up in blue so what could be wrong?
I also tried the help to understand how the Offset formula works, but can't say I totally understand it.
My other question is: what if I want to add cells 2, 4, 5, 7, how would this change my offset, I can guess the formula may look like this:
sum(offset(january,2,0,X,1) not entirely sure.

Thanks again!

So you want to reference a range name in a different file altogether and then use just individual cells in that larger range for calculations?

Although the Offset formula can be used for that, it's probably not the best option, since it is volatile and you don't want too many Offset formulae in your file.

Can you just post a few bits of sample data and explain what you are actually trying to achieve? Maybe there's a better way if we take a few steps back and start over.

cheers