OFFSET troubles (multiple sheets)

I am having trouble with this code.. I get a popup window saying that the formula I typed contains an error. Can someone see what I've done wrong?

Sheet1 has a column like this

.......A.........B..
1...Name...Mary
2...Name...Tom
3...Name...Peter
4...Name...John

I would like JUST column B of Sheet1 to be reprinted on Sheet 2, where I am typing the code. But I will occasionally update Sheet 1 so when I add names to Sheet1, I would like Sheet2 to be updated automatically.

I am using this code on Sheet2

Please Login or Register  to view this content.

also on Sheet2 I have filled in column M with numbers from 0 to 100. My logic on this was that it would change the number of rows to offset from Sheet 1 as I dragged the code down through my cells on Sheet2. Similar to the way Setting one cell to =$A1 and dragging that code down would sequentially make it =$A2, =$A3, etc.

BUT... for some reason this doesn't work. I am not a programmer and I am very much a beginner at this (as may or may not be obvious) so problem solving when my reasoning goes awry is a bit beyond my skill set. I am instead left with no idea why what I've written is incorrect. Please help me.

to do what you are describing why not try this :

Formula:

Please Login or Register  to view this content.

Drag down

as to the error you are receiving it looks like you are missing the indirect function to turn the string name of the cell into a reference that the offset can use..

Hope this helps

don't worry, I have fallen in to the same trap myself many times

You are Welcome