Call List

Peter_SSs

MrExcel MVP, Moderator

Joined

May 28, 2005

Messages

63,880

Office Version

1. 365

Platform

1. Windows

• Jul 27, 2006

• #2

I'm trying to make a call list. I have 2 worksheets, 1 with the database the othe is the call list. I gotten as far as that when you put a "x" in column a in the database it moves the information over to the call list. My problem is that it moves the information to the same row it's in in the database. Is there anyway I can get the information to fill the call list top to bottom without spaces?

Thanks.

Click to expand...

OldandInTheWay

I am not quite sure what you mean. Can you provide some sample data and apply your explanation to that data? If you want to show a sample of your sheet on the board, here's how:
http://www.mrexcel.com/board2/viewtopic.php?t=92622

 

Upvote 0

O

OldandInTheWay

New Member

Joined

Jul 12, 2005

Messages

13

• Jul 27, 2006

• #3

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	CONTACT
2					COMPANYA,TOM,555-444-3333
3
4
5					COMPANYD,HARRY,555-444-3333
6
7					COMPANYF,SAM,555-444-3333
8
9
10
11
12
Sheet1

What I'm trying to do is get rid of the blank rows so the call list is smaller. The data shown is in the same row it's in in the data sheet.

Thanks for your help

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Jul 27, 2006

• #4

Advanced Filter or MSQuery. Either would do this nicely.

lenze

 

Upvote 0

O

OldandInTheWay

New Member

Joined

Jul 12, 2005

Messages

13

• Jul 27, 2006

• #5

Book1.xls
	A	B	C	D	E	F
2	x	COMPANYA	TOM	555-444-3333	555-444-3333	COMPANYA,TOM,555-444-3333
3		COMPANYB	BOB	555-444-3333	555-444-3333
4		COMPANYC	****	555-444-3333	555-444-3333
5	x	COMPANYD	HARRY	555-444-3333	555-444-3333	COMPANYD,HARRY,555-444-3333
6		COMPANYE	JILL	555-444-3333	555-444-3333
7	x	COMPANYF	SAM	555-444-3333	555-444-3333	COMPANYF,SAM,555-444-3333
Sheet2

I may not be explaining my problem correctly. In the same work book, there are 2 worksheets, 1 is a blank call list (1st example), the other is the database(above). What I would like to do is put an "x" in column A of the database if we want to include this person on the call list, then move the info over to the worksheet "call list". I'm doing that with the if function, but the information is copied on the same row it's on in the database. Is there a formula that you can use so the information is copied on the next empty line in the call list. We use these list to make call list for construction bids so they're just select , then print and done.

Hope this is more understandable

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Jul 27, 2006

• #6

Repeat: Advanced Filter or MSQuery and YES there is a tricky formula to do this that I'm too lazy to construct, but I'm sure someone will eventually post it.
But why mess with formulas when there are built-in tools to do this?

lenze

 

Upvote 0

O

OldandInTheWay

New Member

Joined

Jul 12, 2005

Messages

13

• Jul 27, 2006

• #7

I have to admit that even though I know little about Excel, I know even less about advanced filtering & MSQuery, but I'll read up and see what I can do. But thanks for your advise.

And I'll follow your motto

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Jul 27, 2006

• #8

Perhaps this will help
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

lenze

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jul 27, 2006

• #9

A formula approach...

Book31
	A	B	C	D	E	F	G	H	I	J	K	L
1					0			3		Call List
2		Field-1	Field-2	Field-3	Idx			Pos		Field-1	Field-2	Field-3
3	x	COMPANY A	TOM	555-444-3333	1			1		COMPANY A	TOM	555-444-3333
4		COMPANY B	BOB	555-444-3333				4		COMPANY D	HARRY	555-444-3333
5		COMPANY C	****	555-444-3333				6		COMPANY F	SAM	555-444-3333
6	x	COMPANY D	HARRY	555-444-3333	2
7		COMPANY E	JILL	555-444-3333
8	x	COMPANY F	SAM	555-444-3333	3
9
10
Sheet1

E1 must house a 0.

E3, copied down:

=IF(A3="x",LOOKUP(9.99999999999999E+307,$E$1:E2)+1,"")

H1:

=LOOKUP(9.99999999999999E+307,E1:E8)

H3, copied down:

=IF(ROWS($H$3:H3)<=$H$1,MATCH(ROWS($H$3:H3),$E$3:$E$8,1),"")

J3, copied across and down:

=IF(N($H3),INDEX(B$3:B$8,$H3),"")

 

Upvote 0

O

OldandInTheWay

New Member

Joined

Jul 12, 2005

Messages

13

• Jul 27, 2006

• #10

I've been trying the advanced filtering and taking a stab with MSQuery and not having any luck getting what I need. All I want to do is have a list of suppliers w/ phone #s that when you type an "X" into the cell besides it it puts the information from that row into a list that I can printed and hand off the list for someone to call those names. I'll then exit the program without saving and then do the same thing for the next job that comes in. And I just can't seem to get anything to work that simply with filltering or MSQuery.

 

Upvote 0