Select data from range and copy to other cells

[Jorinho]

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.

 

[Jorinho]

Hello Eric

My HTML Maker returns me an error, so I have emailed you my sample.

Thanks for everithing.

Best Regards

Jorinho

 

[erik.van.geit]

there is enough help on the forum to make the htmlmaker work
I don't know if there is anyone who really persisted without succes !!
did you see all these links ?

On the "Excel Questions" main page there is a link to HTML Maker FAQ. You'll find out how to post correctly.
On the main page of the Forum there is a link to a Test here forum and a link to About this Board - to ask questions about HTLMMaker ...

 

[erik.van.geit]

start with this
C1: =LOOKUP(9.99999999999999E+307,B1:B10)
B3: =IF(A3<>"",IF(A3=TRUE,LOOKUP(9.99999999999999E+307,B$1:$B2)+1
C3: {=IF(ROW()-ROW($B$3)+1<=$C$1,LOOKUP(ROW()-ROW($G$3)+1,$B$3:$B$10,$G$3:$G$10),"")}
(control-shift-enter)
EDIT: checkboxes are not displayed

Lista Escalas Teste.xls
	A	B	C	D	E	F	G
1		0	3		0	3
2	Tribunal	IDX		Urgncia	IDX		Nome Profissional
3	FALSE		a2	TRUE	1	a1	a1
4	TRUE	1	a3	FALSE		a3	a2
5	TRUE	2	a5	TRUE	2	a5	a3
6	FALSE			FALSE			a4
7	TRUE	3		TRUE	3		a5
Nomes

then using the technique you received at first, you can make your lists on the other sheets

kind regards,
Erik

 

[Jorinho]

Hello Eric

I have manage to make the "Nomes" sheet work with you big big help.
Now I have another unexpected problem.
In my "Tribunal" and "Urgência" sheets, there is something wrong that I cannot identify.

In both sheets my range of names is A3:A51

In that range (in this case "Tribunal" sheet)I have put this formula: "=IF(Nomes!C1<1;"";Nomes!C3)"

With this formula, I get the exact list from the "Nomes" sheet and in the unused cells (till A51) I get a blank cell.

In B3:B180 I got this formula : "=IF(MOD(ROW()-1;COUNTA(data))=0;INDEX(data;COUNTA(data));INDEX(data;MOD(ROW()-1;COUNTA(data))))"

And in Insert/Name/Define I have this: "=Tribunal!$A$3:INDEX(Tribunal!$A:$A;MATCH(REPT("z";255);Tribunal!$A:$A))"

What happens is in B3, it should be the same name as in A3, but its the name in A4.
When it gets to the end, it starts from the A3 name.

Another thing is that in B column, when it gets to the end of the list, it should start from the first name again, but it gives the blank cells (till A51) and only then starts from the beginning. (It should miss those blank cells).

Thanks again Eric

God Bless You

 

[erik.van.geit]

Jorinho,

hope to get more time for you today, but it's not sure
if we can succeed making the named range only to "see" the "real values" instead of the formulas, we're done !
is this what you mean ?

kind regards,
Erik

 

[Jorinho]

Hello Eric

I think that is the question!!!

My girlfriend told me that this night in my sleep I talked a lot about this formula...

Im exausted...

Lets see if "we" can work this out.

Best Regards

Jorinho

 

[erik.van.geit]

I've got something already but am searching and asking for some more info on the board: this to avoid not-waterproof-answer as much as possible.

 

[Jorinho]

Ok Eric

Thanks again

 

[erik.van.geit]

CHANGE DATA FORMULA

And in Insert/Name/Define I have this: "=Tribunal!$A$3:INDEX(Tribunal!$A:$A;MATCH(REPT("z";255);Tribunal!$A:$A))"

=Tribunal!$A$3:INDEX(Tribunal!$A:$A;MATCH(REPT("z";255);Tribunal!$A:$A))
this is fine as long as we are seaching for anything in the cells, which includes formulas
to find the last "filled cell" (or better the first blank minus 1)
you have to use
=Nomes!$C$3:INDEX(Nomes!$C:$C, MATCH("*",Nomes!$C$3:$C$65536,-1))
this formula according to the sample-sheet from my previous post

this will get you closer to the final result, I hope
kind regards,
Erik

 

[Jorinho]

Hello Eric

I have made some changes and I think I finnaly made it.
I can now choose the fist name to apear and how many names to apear.
Probably I will need help in other things, but then I will search/open another post.

THANK YOU VERY MUCH

Kind Regards

Jorinho