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.
 
Hello Eric

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

Thanks for everithing.

Best Regards

Jorinho
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
ABCDEFG
10303
2TribunalIDXUrgnciaIDXNome Profissional
3FALSE a2TRUE1a1a1
4TRUE1a3FALSE a3a2
5TRUE2a5TRUE2a5a3
6FALSE  FALSE  a4
7TRUE3 TRUE3 a5
Nomes


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

kind regards,
Erik
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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... :-D

Im exausted...

Lets see if "we" can work this out.

Best Regards

Jorinho
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
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