Automatic cell increment with data from sheet 1 to sheet 2
I want to create a secondary excel sheet that combines information from a
primary sheet. The primary sheet contains an extract of data from Pastel which is an asortment of data in rows and columns. However, the data is repeated at regular intervals. Eg. Cell B4, B17, B30 contain same data for different clients. I want a formula that I can drag and drop which increases the cell reference from =sheet1!B4 to =sheet1!B17, to =sheet1!B30 etc. Sheet 2 will have all data in columns, the first row of which I have copied across to sheet 2 as =B4; =B5; =A7;=D4; etc. Hope this makes sense. HELP -- GJC |
I am not sure if it makes sense or not, do you want to increment with 13 per
row you copy down so you will get what's in Sheet1 B4, B17, B30, B43, B56 and so on? If so this formula will do that =OFFSET(Sheet1!$B$4,ROW(1:1)*13-13,) Regards Peo Sjoblom "Big G" wrote: I want to create a secondary excel sheet that combines information from a primary sheet. The primary sheet contains an extract of data from Pastel which is an asortment of data in rows and columns. However, the data is repeated at regular intervals. Eg. Cell B4, B17, B30 contain same data for different clients. I want a formula that I can drag and drop which increases the cell reference from =sheet1!B4 to =sheet1!B17, to =sheet1!B30 etc. Sheet 2 will have all data in columns, the first row of which I have copied across to sheet 2 as =B4; =B5; =A7;=D4; etc. Hope this makes sense. HELP -- GJC |
I want a formula that I can drag and drop which increases
the cell reference from =sheet1!B4 to =sheet1!B17, to =sheet1!B30 etc. One way is to use INDIRECT In Sheet2 -------------- Put in say, A2: =INDIRECT("'"&"Sheet1"&"'!B"&ROW(A1)*13-13+4) Copy down A2, A3, A4, etc will return the equivalents of: =Sheet1!B4, =Sheet1!B17, =Sheet1!B30 (in increments of 13 rows as you copy down) And to return likewise in say, B2, B3, B4 down, but starting with link to B5 in Sheet1 (instead of B4), viz.: =Sheet1!B5, =Sheet1!B18, =Sheet1!B31, etc just change the last number "4" to "5" in the formula, i.e.: Put in B2: =INDIRECT("'"&"Sheet1"&"'!B"&ROW(A1)*13-13+5) Copy down -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Big G wrote in message ... I want to create a secondary excel sheet that combines information from a primary sheet. The primary sheet contains an extract of data from Pastel which is an asortment of data in rows and columns. However, the data is repeated at regular intervals. Eg. Cell B4, B17, B30 contain same data for different clients. I want a formula that I can drag and drop which increases the cell reference from =sheet1!B4 to =sheet1!B17, to =sheet1!B30 etc. Sheet 2 will have all data in columns, the first row of which I have copied across to sheet 2 as =B4; =B5; =A7;=D4; etc. Hope this makes sense. HELP -- GJC |
All times are GMT +1. The time now is 07:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com