make a listing of each cell made up of its row and column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rShivers - 05 Oct 2004 04:07 GMT

I have a grid of x number of rows and y number of column. I want to make a
listing of all the cells in thish grid by using a formula without having to
enter the cell
row and column numbers manually. How do I do this ?

Reply to this Message

Max - 05 Oct 2004 06:53 GMT

If you mean extract all the elements
in a grid into a column elsewhere, one way ..

Suppose your grid is in Sheet1, B2:D6
(a 5R x 3C grid with 15 elements)

In Sheet2
-------------

Put in any starting cell, say in C3:

=OFFSET(Sheet1!$B$2,INT((ROW(A1)-1)/COLUMNS(Sheet1!$B$2:$D$6)),MOD(ROW(A1)-1
,COLUMNS(Sheet1!$B$2:$D$6)))

Copy C3 down to C17 (as there are 15 elements)
or just copy down until zeros appear
signalling exhaustion of data

Signature

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

> I have a grid of x number of rows and y number of column. I want to make a
> listing of all the cells in thish grid by using a formula without having to
> enter the cell
> row and column numbers manually. How do I do this ?

Reply to this Message