Filling blanl spaces

Lawnman

New Member
Joined
Oct 11, 2002
Messages
10
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
BCDEF
1DateCustName
22/5/02Avant
32/8/02Dennis
42/11/02Yogi5-Feb-02
5datainSheet1
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

New Member
Joined
Oct 11, 2002
Messages
10
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

New Member
Joined
Oct 11, 2002
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
BCDEFGH
1CustName
2Avant
3
4AvantSheet extracted usingAdvancedFilter
5
6DateCustNameDescriptionDateCustNameDescription
72/5/02AvantCut2/5/02AvantCut
82/12/02Dennisraked2/12/02Avantcut
92/12/02Yogicut
102/12/02Avantcut
112/19/02Yogiraked
122/19/02Dennisraked
132/19/02Yogicut
14datainSheet1
15
Sheet2
</SPAN>

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

Regards!

Yogi
 

Lawnman

New Member
Joined
Oct 11, 2002
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

New Member
Joined
Oct 11, 2002
Messages
10
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEFGH
1DateCustNameDescriptionCustName
22/5/02AvantCutAvant
32/12/02Dennisraked
42/12/02YogicutAvantSheet extracted usingAdvancedFilter
52/12/02Avantcut
62/19/02YogirakedDateCustNameDescription
72/19/02Dennisraked2/5/02AvantCut
82/19/02Yogicut2/12/02Avantcut
9datainSheet1
10
11
12
13StepsforusingAdvancedFilterinSheet2
141.DATA|FILTER|ADVANCED_FILTER
152.IncellF1enterCustName
163.IncellF2entervalueofCustNameofinterest
174.Inthedialogboxthatpopsupfillininformation
18a.List_rangeSheet1!$B$1:$D$10
19b.Criteria_range$F1:$F2
20c.selectCopy_to_another_location$F$6
215.ClickOK
226.WatchresultspostedincellsF1througHwhatever
237.IfresultsareOKcelebrate!
248.IfresultsarenotOK,backtostep1andredo
25tillyougettherightresults
Sheet2
</SPAN>

Regards!

Yogi
 

Forum statistics

Threads
1,144,312
Messages
5,723,651
Members
422,508
Latest member
Lordkit1

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
Top