Select data from range and copy to other cells

Jorinho

New Member
Joined
Mar 10, 2005
Messages
18
Hello All

I want to make a list of names with for example 40 people.
Each name is in a cell.

I want to complete a time table (month) with 3 people in each day, and for that i will need about 90.

I want to select those people from that list and from the beggining to the end, and the start over again till i have those 90 names.

Then I want to copy those 90 names to other cells so i can complete the time table.

I prefer to work with formulas if possible, but if it has to be with a macro...


This is just the beggining!!! :wink:


Thanks in advance.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hello, Jorinho,
would this do ?

B1: =IF(MOD(ROW()-1,COUNTA(data))=0,INDEX(data,COUNTA(data)),INDEX(data,MOD(ROW()-1,COUNTA(data))))
data is defined as a dynamic range in the namebox (menu insert, name, define) as =sheet1!$A$1:INDEX(sheet1!$A:$A,MATCH(REPT("z",255),sheet1!$A:$A))
(adding names will update the range: your repeated list will update automatically then)
Map1.xls
ABCD
1namesrepeated
2a1a1
3a2a2
4a3a3
5a4a4
6a5a5
7a1
8a2
9a3
10a4
11a5
12a1
13a2
14a3
sheet1


kind regards,
Erik
 
Upvote 0
Hello erik.van.geit

I'm sorry, but i cant make it work.
Excel says that the formulas contain errors.
I think that the problems are the ","
I substitute the with ";" but i cant make it work.
I tried to understand your formulas but...!!! :-?

Did you understand well wath i need? (I dont know if time table is the word...)

Thanks again
 
Upvote 0
Hi Erik.

My Excel is in English.
The formula translator is to translate Excel formulas to other languages, right?
The formula that you gave me, in my excel doesnt work, becausa excel says that the formula contains errors.
I think the problem are the "," and changed the to ";", but doesnt work too. :cry:

To explain better what i need, i show you the time table.
http://www.oa.pt/Uploads/{07BA712F-AD35-4BF8-8749-9CA99DE0AEC1}.xls

Every day needs 3 people.
This month needs 90 people.
I have 40 names, and i need to select 90 people, so I will need all the names 2 times except the 10 first that will be 3 times ((3x10)+(2x30)=90).

Sorry, but I cant correct or apply your formula (my problem!!!). :roll:

P.S.: Thanks for the link

Best Regards

Jorinho
 
Upvote 0
I tried again: copied the formulas displayed in my post, translated automatically and everything works for me.
ATTENTION: Perhaps you don't have a "sheet1" ??

if still no result you can email me: I'll post you a sample.
(I didn't look to your sample: was my sample OK? if not please tell what was not OK or post a sample here on the board)

best regards,
Erik
 
Upvote 0
Hi Erik

Thanks for the email. :wink:

It works perfectly.

Here the formulas are =IF(MOD(ROW()-1;COUNTA(data))=0;INDEX(data;COUNTA(data));INDEX(data;MOD(ROW()-1;COUNTA(data))))

I was making wrong the dynamic range. :roll:

Now it's time to complicate a little more.

Sometimes people in A column (a1,a2,etc) wont participate in a month, so they will be jumped.

Example:

a1 wont participate, so in the first day it will start from a2 and when it its the end, it will start again from a2.

The other point is that one month will start from a1 but other months will start by any other.
If starts from another, when it hits the end, it will restart from a1 and so on.

I have put checkboxes so I can choose which names (a1,a2,etc.) will participate in the month, and if not it will return me a 0.


THANK YOU VERY MUCH!!! :-D
 
Upvote 0
a1 wont participate, so in the first day it will start from a2 and when it its the end, it will start again from a2.
in fact you need to make another list which contains the "filtered data"
see this thread for how to do that (I was stuck using to much helpcolumns, but Paddy and Aladein providd a formula...
http://www.mrexcel.com/board2/viewtopic.php?p=816057
the "filtered datalist" would be the range "data" as we defined in the dynamic-range-formula: you can hide this column

if this is OK we can continue with the second question
ready to help if you have any problems implementing the other thread to your situation

kind regards,
Erik
 
Upvote 0
Thanks Erik

I have already asked them to come and see this post.

To clarify, I have a sheet with many names, and for each name I have 2 check boxes.

Each os those check boxes for each name, selects the names for 2 diferent sheets.
If the check box is on, the name will be displayed in the respective sheet.

I have already made a way so the C column (in each of those 2 sheets) will return a 0 if the name is not displayed, and returns a 1 if the name is displayed.

Jorinho said:
Sometimes people in A column (a1,a2,etc) wont participate in a month, so they will be jumped.

Example:

a1 wont participate, so in the first day it will start from a2 and when it its the end, it will start again from a2.

The other point is that one month will start from a1 but other months will start by any other.
If starts from another, when it hits the end, it will restart from a1 and so on.


Thanks All

Best Regards

Jorinho
 
Upvote 0
I have already made a way so the C column (in each of those 2 sheets) will return a 0 if the name is not displayed, and returns a 1 if the name is displayed.
do yo mean the list is entirely copied on both sheets followed by a 1 or 0 ?
can you provide a sample on how your sheets will look like ?
this woul dmake help easier
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top