Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Big G
 
Posts: n/a
Default 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
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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

  #3   Report Post  
Max
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
List, Data Validation, unlocked cell, protected sheet..... Kane New Users to Excel 6 July 16th 12 09:11 AM
pull data from sheet two, then fill in the data to sheet one (part Jim Excel Worksheet Functions 3 December 11th 04 05:51 AM
How do I input for an automatic increment in a cell on Excel? Rissy Excel Worksheet Functions 1 November 29th 04 10:01 AM
Function to automatically insert a new sheet as a result of data entry? Mark Mulik Excel Worksheet Functions 2 November 28th 04 03:21 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 09:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"