Select data from range and copy to other cells

Jorinho · Sep 17, 2005

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.

erik.van.geit · Sep 17, 2005

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

A

B

C

D

1

names

repeated

2

a1

3

a2

4

a3

5

a4

6

a5

7

8

9

10

11

12

13

14

sheet1

kind regards,
Erik

Jorinho · Sep 17, 2005

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

erik.van.geit · Sep 18, 2005

Jorinho,

is my sample OK ?
then you only need to find the correct formulas

there is a formula-translator
find it somewhere among the recommended addins and links
http://www.mrexcel.com/board2/viewtopic.php?t=131224

kind regards,
Erik

Jorinho · Sep 18, 2005

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.

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!!!).

P.S.: Thanks for the link

Best Regards

Jorinho

erik.van.geit · Sep 19, 2005

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

Jorinho · Sep 20, 2005

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.

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!!!

erik.van.geit · Sep 21, 2005

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

Jorinho · Sep 21, 2005

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

erik.van.geit · Sep 21, 2005

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

Select data from range and copy to other cells

Jorinho

New Member

Excel Facts

erik.van.geit

MrExcel MVP

Jorinho

New Member

erik.van.geit

MrExcel MVP

Jorinho

New Member

erik.van.geit

MrExcel MVP

Jorinho

New Member

erik.van.geit

MrExcel MVP

Jorinho

New Member

erik.van.geit

MrExcel MVP

Similar threads

Forum statistics

Share this page

Select data from range and copy to other cells

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock