Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
dewsbury
 
Posts: n/a
Default Dynamic flexible cell address


I have 1000 names in sheet1. (Cells A1 to A1000)

I want to put names 1 to 10 in Sheet2 - Cells A1 to A10
I want to put names 11 to 20 in Sheet3 - Cells A1 to A10
I want to put names 21 to 30 in Sheet4 - Cells A1 to A10

etc.

until all 1000 names used up.

I want to be able to cut & paste from sheet 2 to 3 , 4 etc.
However, after the C&P I do not want to have to change every address.
I.e. In sheet 3. I do not want to be forced to changed to change cell
A1 to =sheet1!A11

I need a form of "dynamic" cell addressing. Something like
=a(x*10+1)..

Do you understand my query? If so well done!


--
dewsbury
------------------------------------------------------------------------
dewsbury's Profile: http://www.excelforum.com/member.php...o&userid=16084
View this thread: http://www.excelforum.com/showthread...hreadid=520297

  #2   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student
 
Posts: n/a
Default Dynamic flexible cell address

Let's assume you have 101 worksheets. Sheet1 with the data and sheets 2-101
all blank. Enter and run this macro:


Sub Macro1()
' gsnu
Dim r1, r2 As Range
For i = 2 To 101
Set r2 = Worksheets(i).Range("A1")
Set r1 = Worksheets(1).Range(Cells((i - 2) * 10 + 1, "A"), Cells((i - 2) *
10 + 10, "A"))
r1.Copy r2
Next
End Sub
--
Gary's Student


"dewsbury" wrote:


I have 1000 names in sheet1. (Cells A1 to A1000)

I want to put names 1 to 10 in Sheet2 - Cells A1 to A10
I want to put names 11 to 20 in Sheet3 - Cells A1 to A10
I want to put names 21 to 30 in Sheet4 - Cells A1 to A10

etc.

until all 1000 names used up.

I want to be able to cut & paste from sheet 2 to 3 , 4 etc.
However, after the C&P I do not want to have to change every address.
I.e. In sheet 3. I do not want to be forced to changed to change cell
A1 to =sheet1!A11

I need a form of "dynamic" cell addressing. Something like
=a(x*10+1)..

Do you understand my query? If so well done!


--
dewsbury
------------------------------------------------------------------------
dewsbury's Profile: http://www.excelforum.com/member.php...o&userid=16084
View this thread: http://www.excelforum.com/showthread...hreadid=520297


  #3   Report Post  
Posted to microsoft.public.excel.misc
dewsbury
 
Posts: n/a
Default Dynamic flexible cell address


Perfect ! Thanks


--
dewsbury
------------------------------------------------------------------------
dewsbury's Profile: http://www.excelforum.com/member.php...o&userid=16084
View this thread: http://www.excelforum.com/showthread...hreadid=520297

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
Cell Address Zambrosio Excel Discussion (Misc queries) 1 February 23rd 06 04:25 PM
cell address rather than range name Angi Bemiss Excel Discussion (Misc queries) 1 December 1st 05 01:46 AM
Can the column index in a cell address be made variable? cyberdude Excel Discussion (Misc queries) 1 November 20th 05 03:47 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
How do I find the contents of a cell using the "ADDRESS" function. sweeney Excel Worksheet Functions 2 April 5th 05 03:23 AM


All times are GMT +1. The time now is 12:53 AM.

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"