MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pulling data from a column of values


Posted by Brian J on November 03, 2001 6:42 AM

Firstly, I dont know a lot about macros etc.. I have a column of data and i want to pull every 18th value (text) and put it in a new column. Excel wont recognise the pattern in my cell references and the list is too long to do manually. Any ideas?


Posted by Aladin Akyurek on November 03, 2001 7:24 AM

Brian --

Assuming that the values of interest are in column A from A1 on,

in B2 enter: =OFFSET($A$1,ROW()*18-1,0)

Copy down this as far as needed.

Aladin

=======

Posted by Brian J on November 03, 2001 8:06 AM

Hi Aladin
Many Thanks - I have now discovered the offset function. It was not quite right, but after fiddling, I used :
and hey presto.
Thanks again
Brian

Posted by Aladin Akyurek on November 03, 2001 8:31 AM

I should have said: "In B1 enter" instead of "In B2 enter" and the formula should have been:

=OFFSET($A$1,ROW()*18-1,0)

Sorry about that.

=========