Incrementing cell in sheet references

[def90]

Hi all,

I"m looking for some assistance in referencing cells in another sheet.

I have inherited a workbook that has a sheet ('original') of approximately 60 measurements in 3 different columns (C, F, H).
I would like to be able to create a new sheet which lists these measurements in one single column.
For example:

A
=original!C1
=original!F1
=original!H1
=original!C2
=original!F2
=original!H2
...
...
=original!C60
=original!F60
=original!H60

Any suggestions for how to do this?

I would really appreciate any assistance for how to achieve this.

Thanks in advance

 

[njimack]

I'm going to suggest you make a slight alteration to your Original sheet - either insert a column before column H, or rearrange the order of your columns so that the 3 columns you wish to reference have the same distance between them. You can then use:
=INDIRECT(ADDRESS(ROUNDUP(ROW()/3,0),IF(MOD(ROW(),3)*3,MOD(ROW(),3)*3,9),,,"Original"))

 

[def90]

I'm going to suggest you make a slight alteration to your Original sheet - either insert a column before column H, or rearrange the order of your columns so that the 3 columns you wish to reference have the same distance between them. You can then use:
=INDIRECT(ADDRESS(ROUNDUP(ROW()/3,0),IF(MOD(ROW(),3)*3,MOD(ROW(),3)*3,9),,,"Original"))

That works a treat!

Thank you very much for your help.

 

[Peter_SSs]

Welcome to the MrExcel board!

If you wanted to avoid rearranging your columns and/or avoid the volatile function INDIRECT, you could also try this, copied down.

I have referenced the first 100 rows of sheet 'Original'. Change the blue number if you need more or less.
I have my first formula in cell A2. Change the red references to match the cell address of your first formula. (Be careful to include the $ signs where I have.)

=INDEX(Original!C$1:H$100,INT((ROWS(A$2:A2)+2)/3),CHOOSE(MOD(ROWS(A$2:A2)-1,3)+1,1,4,6))

 

[def90]

Welcome to the MrExcel board!

If you wanted to avoid rearranging your columns and/or avoid the volatile function INDIRECT, you could also try this, copied down.

I have referenced the first 100 rows of sheet 'Original'. Change the blue number if you need more or less.
I have my first formula in cell A2. Change the red references to match the cell address of your first formula. (Be careful to include the $ signs where I have.)

=INDEX(Original!C$1:H$100,INT((ROWS(A$2:A2)+2)/3),CHOOSE(MOD(ROWS(A$2:A2)-1,3)+1,1,4,6))

Thanks for that suggestion!
I have trialled both techniques and they both seem to do the job.

Peter - thank you for the instructions on what each section is doing. If I could just ask a few more questions just so I understand what is going on.

=INDEX(Original!C$1:H$100,INT((ROWS(A$2:A2)+2)/3),CHOOSE(MOD(ROWS(A$2:A2)-1,3)+1,1,4,6))[/QUOTE]

The bits in green and blue are the range of cells.

The numbers in yellow appear to be the reference to the column reference numbers.

What parts of the formula would I need to change if I wanted to add further columns (e.g. the F column) from the "Original" spreadsheet to the reference?

Thanks again for your patience with me, I am learning lots.
Cheers, Sam

 

[Peter_SSs]

Thanks for that suggestion!
I have trialled both techniques and they both seem to do the job.

Good result!

What parts of the formula would I need to change if I wanted to add further columns (e.g. the F column) from the "Original" spreadsheet to the reference?

I am not sure exactly what you are asking. Your interpretation of the formula was pretty right. But that means column F was already referred to. The overall range covered columns C:H, then the yellow numbers referred to the columns within that overall range, so
1 represents the first column in the range (column C)
4 represents the 4th column in the range (column F)
etc

So, if you still need some modification can you post more specific details like you did in post #1 ?

 

[def90]

Good result!


I am not sure exactly what you are asking. Your interpretation of the formula was pretty right. But that means column F was already referred to. The overall range covered columns C:H, then the yellow numbers referred to the columns within that overall range, so
1 represents the first column in the range (column C)
4 represents the 4th column in the range (column F)
etc

So, if you still need some modification can you post more specific details like you did in post #1 ?

Thanks for that Peter,

Sorry my previous post wasn't clear. I'll try again...

Using your formula, I'm wondering what parts of the formula I would need to change if I wanted to reference more columns e.g. the D column.

e.g.
=original!C1
=original!D1
=original!F1
=original!H1
=original!C2
=original!D2
=original!F2
=original!H2
...
...
=original!C60
=original!D60
=original!F60
=original!H60

Hopefully this makes a bit more sense.
Thanks again for your assistance.

Cheers, Sam

 

[Peter_SSs]

.. what parts of the formula I would need to change if I wanted to reference more columns e.g. the D column.

The yellow numbers would now include an extra one, but nearly everything else als has to increment by one.

=INDEX(Original!C$1:H$100,INT((ROWS(A$2:A2)+3)/4),CHOOSE(MOD(ROWS(A$2:A2)-1,4)+1,1,2,4,6))

 

[def90]

The yellow numbers would now include an extra one, but nearly everything else als has to increment by one.

=INDEX(Original!C$1:H$100,INT((ROWS(A$2:A2)+3)/4),CHOOSE(MOD(ROWS(A$2:A2)-1,4)+1,1,2,4,6))

Perfect - thank you for your help!

 

[Peter_SSs]

Perfect - thank you for your help!

No problem, glad to help.