Programming Cell ID's

[Guest]

I have a very basic issue in excel and I can't for the life of me find the
solution. I have a dense worksheet of data and I want to distill some of the
information into a much shorter list. The data is ordered and the
information I want to pull out from the big sheet and put in the little sheet
occurs in the same column in every 30th cell. In my new sheet, I want my
column of information to refer to every 30th cell. In other words, I want
h28, h58, h88, h118, etc. Unfortunately, I don't know how to make excel
automate this process (there's too much info for me to do it by hand). Does
anyone know how to do this? Thanks.

 

[Guest]

Try the below formula...

=INDIRECT(ADDRESS(ROW()*30,1,1,1,"Sheet2"))

Where Sheet2 is the name of the sheet that you want to reference. Start the
formula in row1 of your new sheet and copy the formula down for as many rows
as you need. If you don't want to start the formula in row 1 of your new
sheet you will have to enter the formula
=INDIRECT(ADDRESS(ROW()*30-X,1,1,1,"Sheet2")) Where X is the number of rows
you need to subtract to get the formula to work right.

Hope this helps.

 

[Guest]

try
in A1 of Sheet2
=indirect("'Sheet1'H"&((Row()-1)*30+28)
and copy down

 

[Niek Otten]

Hi Jeremy,

In row1:

=INDIRECT("sheet1!h"&28+(ROW()-1)*30)

Copy down as far as you need and adjust sheet name and start row if necessary

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have a very basic issue in excel and I can't for the life of me find the
| solution. I have a dense worksheet of data and I want to distill some of the
| information into a much shorter list. The data is ordered and the
| information I want to pull out from the big sheet and put in the little sheet
| occurs in the same column in every 30th cell. In my new sheet, I want my
| column of information to refer to every 30th cell. In other words, I want
| h28, h58, h88, h118, etc. Unfortunately, I don't know how to make excel
| automate this process (there's too much info for me to do it by hand). Does
| anyone know how to do this? Thanks.

 

[Harlan Grove]

occurs in the same column in every 30th cell. In my new sheet, I want my
column of information to refer to every 30th cell. In other words, I want
h28, h58, h88, h118, etc. Unfortunately, I don't know how to make excel

....

Three responses all using INDIRECT. If you like slow recalculation, use
INDIRECT. If you have any interest in faster recalc, and if the first such
formula would be in cell B2 in newsheet, try

newsheet!B2:
=INDEX(othersheet!$H:$H,ROWS(B$2:B2)*30-2)

and fill down.