Filling blanl spaces

[Lawnman]

I am working on worksheet that will show service dates on customer sheet. I want to pull dates from input sheet and place only dates that belong on ea customer.
The following allows me to get the right date to the correct customer.
Sheet1(input sheet) Is set up:
B=Date C=Cust. name
B=2/5/02 C=Avant

Sheet2 cell==if(sheet1!c2="Avant",sheet1!b2,"")

This does get the date over, however it is 2 weeks between some service dates so there is a large gap in sheet 2 colume, can anyone help??
Thanks Dennis

 

[Yogi Anand]

Hi Lawnman:

Welcome to the Board!

Your formula is conceptually right. I just added the third argument in the formula to provide for if the name Avant is not in the source list in sheet1. So, my formula now reads:

=IF(sheet1!C2="Avant",sheet1!B2,"Not Available")

See worksheet simulation

y021012h1.xls
	B	C	D	E	F
1	Date	CustName
2	2/5/02	Avant
3	2/8/02	Dennis
4	2/11/02	Yogi			5-Feb-02
5	datainSheet1
6
Sheet2

</SPAN>

Please post back if it works for you -- otherwise explain a little further and let us take it from there.

Regards!

Yogi

 

[Lawnman]

Yogi
Thats not what i'm looking for.
Each customer will have there own sheet, Identified by their name on the tab.
I want one of the fields on THEIR sheet to Be
Service dates, and list all dates during the year they were serviced, but with both formulas, as I list the other customers on the input sheet (sheet 1), the formulas on the sheet Avant looks by row but doesn't find it's name again for maybe 2 weeks and by then there are about 60 other names above it,(that should go to their respective pages)
so that leaves 60 blank spaces on the cust. sheets between serv dates.
So I guess I am looking for something to check the above cell if it is blank then move up and up until it gets to the top cell that has a date then use next blank cell.
I hope this helps you to understand better,because I am getting confused.
Thanks for any help
Dennis

 

[Yogi Anand]

Hi Lawnman:

I think I follow what you are saying -- but it will help if you can post some sample data, what you are trying to find, what formula you have used, what result did you get, and what result you want to get.

Regards!

Yogi

 

[Lawnman]

Ok I will try
Sheet1(input sheet) will have:
B C D
Date Cust. name Disc. of job

Sheet 2-45 will have:
Misc. info of cust. address, lawn size,type etc.
sheets are named for each cust.

so each cust sheet will have a formula such as:

=if(sheet1!c2"avant",sheet1!b2,"")
this formula will be copied down approx 32 rows, as there are about 32 cuts per season.
the name Avant will be changed on all other sheets to represent all the other customers, I want ONLY Avant serv dates on his sheet.
the prob. is I can get the dates over to each custmer but there are hugh blank rows where the formula isn't finding that cust. name.
I hope this helps you understand better!
Thanks for the replys keep them comming
Dennis

 

[Yogi Anand]

Hi Lawnman:

Have you used Colo's utility to post data on the Board? In any event, it appears your transaction (Lawn Cut operations) are recorded in sheet1 -- then you have a separate worksheet for each customer. What is not clear is how you record the Lawn Transaction in that sheet.

I don't want to muddy the water, so bear with me -- look at my worksheet simulation posted -- therein I am suggesting that if my assumption is correct that your transaction database records datewise what lawn service you have provided on date basis -- then

to extract customer data -- what date the customer was serviced, what service was provided, is then best handled by using Advanced Filter.

y021012h1.xls
	B	C	D	E	F	G	H
1					CustName
2					Avant
3
4					AvantSheet extracted usingAdvancedFilter
5
6	Date	CustName	Description		Date	CustName	Description
7	2/5/02	Avant	Cut		2/5/02	Avant	Cut
8	2/12/02	Dennis	raked		2/12/02	Avant	cut
9	2/12/02	Yogi	cut
10	2/12/02	Avant	cut
11	2/19/02	Yogi	raked
12	2/19/02	Dennis	raked
13	2/19/02	Yogi	cut
14	datainSheet1
15
Sheet2

</SPAN>

Look at the simulation, and see if this way will work for you.

Regards!

Yogi

 

[Lawnman]

Yogi
YES!!! that looks like what I need.
Now please tell me it will not take a week to enter for 60+ customers.
THANKS
Dennis

 

[Yogi Anand]

How about ONE whole minute in total!

You don't even have to create 60 sheets. Your transaction data is the source of all the information.

Just use one sheet, change the name of the customer in the yellow box, and run Advanced Filter, read or print your results, change to the next customer's name.

Later you can automate the process; also do the report for a given time-frame, and so on. But let us not rush things, digest what you have so far, and determine if this will do for you.

Regards!

Yogi

 

[Lawnman]

Yogi
I can't figure out how to set up the list and criteria ranges, keep getting error messages.
Can you break it down in simple terms?
And I used to be a Mechanical Engineer!!
Thanks for the help s far, I'm gonna get it soon.
Dennis

 

[Yogi Anand]

On 2002-10-13 10:44, Lawnman wrote:
Yogi
I can't figure out how to set up the list and criteria ranges, keep getting error messages.
Can you break it down in simple terms?
And I used to be a Mechanical Engineer!!
Thanks for the help s far, I'm gonna get it soon.
Dennis

Hi Dennis:

It appears the problem is your being 'Mechanical Engineer' -- only if you were 'Civil Engineer' like myself -- you would have been more civil then and get it right away ... (just kidding!)

See my worksheet simulation showing step by step description on using the Advanced Filter

y021012h1.xls
	A	B	C	D	E	F	G	H
1		Date	CustName	Description		CustName
2		2/5/02	Avant	Cut		Avant
3		2/12/02	Dennis	raked
4		2/12/02	Yogi	cut		AvantSheet extracted usingAdvancedFilter
5		2/12/02	Avant	cut
6		2/19/02	Yogi	raked		Date	CustName	Description
7		2/19/02	Dennis	raked		2/5/02	Avant	Cut
8		2/19/02	Yogi	cut		2/12/02	Avant	cut
9		datainSheet1
10
11
12
13		StepsforusingAdvancedFilterinSheet2
14		1.DATA|FILTER|ADVANCED_FILTER
15		2.IncellF1enterCustName
16		3.IncellF2entervalueofCustNameofinterest
17		4.Inthedialogboxthatpopsupfillininformation
18		a.List_rangeSheet1!$B$1:$D$10
19		b.Criteria_range$F1:$F2
20		c.selectCopy_to_another_location$F$6
21		5.ClickOK
22		6.WatchresultspostedincellsF1througHwhatever
23		7.IfresultsareOKcelebrate!
24		8.IfresultsarenotOK,backtostep1andredo
25		tillyougettherightresults
Sheet2

</SPAN>

Regards!

Yogi