Problem with Excel pattern equation

[onetwotwelve]

Hi guys I'm trying to return the value for a cell using an equation.
Basically I want it to be like
B5975
B5981
B5987
and so on (a 6 digit increment)

I am using this equation: =INDEX(B:B,(ROWS(1:$5975)-1)*6+1)
But it returns a value of zero, any ideas why??

Thanks!

[dredwolf]

Have the cells in columnB got data in them?
If the cells that the Index formulais pointing to are empty, or if the formula they contain result in a "" or 0, it will return a 0

Hope this helps

[onetwotwelve]

Thanks for your response!
No they are just cells with values in them, I can attach the spreadsheet if that helps?

[dredwolf]

Reread, to get the result of "B1" instead of the contents of Cell B1:
start in first row of any column,then
="B"&(ROWS($1:1)-1)*6+1
drag down

for the contents of B1,B7,B13...
In say C1:
=INDEX(B:B,(ROWS($1:1)-1)*6+1)
Drag down

Hope this helps

[onetwotwelve]

Sorry yes I need the contents, my bad.
But that equation isn't working because I have to start from cell B5975
I use this equation =INDEX(B:B,(ROWS(1:$5975)-1)*6+1)
and it returns the cell contents as 0. Have I done the equation wrong?

Thanks

[dredwolf]

Also, if you want to start the offset calculation at a specific point say 5975 and carry on:

IX (for example) C1:
="B"&(ROWS($1:1)-1)*6+5975
OR
=INDEX(B:B,(ROWS($1:1)-1)*6+5975)

Edit-

Check where the Dollar Sign ($) is placed

[onetwotwelve]

Yes! Brilliant, thanks so much dredwolf!!

[dredwolf]

What row are you starting in?

[dredwolf]

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"