ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Fill cells from non-adjacent cells (https://www.excelbanter.com/excel-discussion-misc-queries/19209-fill-cells-non-adjacent-cells.html)

Abes

Fill cells from non-adjacent cells
 
I am populating a worksheet with values from another worksheet.
For example the cells D6:Y6 on Sheet1, need results from various cells on
Sheet2. The problem is that the cells on Sheet2 are not contiguous, that is
they will be (for example) Sheet2!E15 (to populate Sheet1!D6), Sheet2!J15 (to
populate Sheet1!E6), Sheet2!O15 (to populate Sheet1!F6) and so on. (Note:
There is data in the cells between cell E15 and J15 and so on)
Is there a simpler way to 'auto-fill' these cells, or is there some quicker
way of accomplishing this rather than by cut and paste, or swapping between
worksheets for each cell.
--
Cheers

Abes

Domenic

Try...

D6, copied across:

=OFFSET(Sheet2!$E$15,0,(COLUMN()-COLUMN($D$6)+1)*5-5)

Hope this helps!

In article ,
"Abes" wrote:

I am populating a worksheet with values from another worksheet.
For example the cells D6:Y6 on Sheet1, need results from various cells on
Sheet2. The problem is that the cells on Sheet2 are not contiguous, that is
they will be (for example) Sheet2!E15 (to populate Sheet1!D6), Sheet2!J15 (to
populate Sheet1!E6), Sheet2!O15 (to populate Sheet1!F6) and so on. (Note:
There is data in the cells between cell E15 and J15 and so on)
Is there a simpler way to 'auto-fill' these cells, or is there some quicker
way of accomplishing this rather than by cut and paste, or swapping between
worksheets for each cell.


Abes

Thanks Domenic - seems to work a treat.
Cheers


"Domenic" wrote:

Try...

D6, copied across:

=OFFSET(Sheet2!$E$15,0,(COLUMN()-COLUMN($D$6)+1)*5-5)

Hope this helps!

In article ,
"Abes" wrote:

I am populating a worksheet with values from another worksheet.
For example the cells D6:Y6 on Sheet1, need results from various cells on
Sheet2. The problem is that the cells on Sheet2 are not contiguous, that is
they will be (for example) Sheet2!E15 (to populate Sheet1!D6), Sheet2!J15 (to
populate Sheet1!E6), Sheet2!O15 (to populate Sheet1!F6) and so on. (Note:
There is data in the cells between cell E15 and J15 and so on)
Is there a simpler way to 'auto-fill' these cells, or is there some quicker
way of accomplishing this rather than by cut and paste, or swapping between
worksheets for each cell.




All times are GMT +1. The time now is 08:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com