replacing data

[Guest]

Here is my spreadsheet:

0001 0002 Last First
0002 0005 Last First
0005 0040 Last First
0006
0007
0008
0009
0010
0011
0012
0013
0014
0015
0016
0017
0018
I want to replace the numbers from the new sheet with the information into
a master sheet that is numbered in Column A from 0000-10000. I have made a
master sheet and different people are going to give me "mini" master sheets
with info. and I want to put everything on the master sheet and not loose the
numbers in Column A, but replace those numbers with the numbers from the mini
sheets.

Thanks.

 

[Guest]

I think you need to reword your paragraphs below because I don't find it
clear what you are saying. Please try again and be more precise.

 

[Guest]

I was afraid of that... here goes...

I am doing a big spreadsheet where an organization is selling 10,000 tickets
for an event. We are trying to keep up with the tickets. I have set up a
master sheet and numbered the sheet. The ticket numbers are in column A. I
have 6 different people doing spreadsheets with the information. EX: ticket
number, names, address etc. Each week they are going to email me their
spreadsheets and I want to enter the information on the master sheet. So I
was going to copy the info into the master sheet. But, I need to keep the
numbers in Column A, so I can keep track of what number tickets have not been
sold. I plan to format in color each persons sheets so I will know who
entered what info.

Does this explain it better??
Thanks.

 

[Guest]

Okay, if I understand you, I hope I can explain MYSELF now. I think the
easiest way to do it is that the six people work with a master spreadsheet,
the same as yours. The tickets they sell will be recorded in the appropriate
row corresponding with the ticket number. If the 1000 tickets are divided up
among the six people, each person will have approximately 166 tickets to sell
and they will be filled in on the master sheet here and there, as they
correspond with the ticket number. Each one will obviously have a lot of
empty rows because they will fill in only the information corresponding with
the ticket number they sold. Now, when you receive their worksheet, you will
copy from column B over by clicking on the column heading, and then in your
worksheet click in B1 and select Paste/Special/Skip Blanks. You do that with
each of the six worksheets, exactly the same. I just did one and it works
wonderfully. The only thing is that everyone must have the identical
workseet. Hope this helps.

Connie Martin

 

[Dave Peterson]

How about a slightly different approach???

Keep your numbers (1-10,000) in column A of a worksheet. I'm gonna call that
worksheet "Numbers".

But put your sold tickets on a different worksheet. Merge all the data into
that worksheet--but don't use colors for the indicator. Add another column and
add a real indicator--name??? Make sure the numbers of the tickets is in
column A.

I'm gonna call this worksheet "Sold".

Then in cell B2 of the Numbers worksheet, put this formula:
=IF(ISERROR(MATCH(A2,Sold!A:A,0)),"",MATCH(A2,Sold!A:A,0))
(and copy down)
(assuming headers in row 1 of the Numbers tab.)

Then in cell C2, put a formula like:
=IF($B2="","",INDEX(Sold!B:B,$B2))
(and copy down)

And in D2, a similar formula:
=IF($B2="","",INDEX(Sold!C:C,$B2))
(and copy down)

And drag to the right for as many columns as you want to return. (and drag
down).

You can apply Data|filter|autofilter to show/hide the data you want.