Excel Question 1

[dwayne94]

I have a spreadsheet in place. I have a coulmn with the field name Event Descirption. I would like to know how do I create a box or a note that will show what type of info will be that particular column. In other words when anyone highlight that coulmn (Event Description)I want a box that will appear with instructions on what will be in the Event Description column.

 

[SkipVought]

hi,

Check out Data > Validation...

There is a place to enter a message.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dwayne94]

Skip,

Another question, I have the data below from the worksheet DETAIL LIST

Technician Name - Debra Moak
Week Beginning - 5/18/2009
Region - SW Georgia
Agency - GDC
Metro Manager - Jeff Myers

Date Event TimeTask DriveTime Total Miles
5/18/2009 Ticket 1.00 1.25 51.00
5/18/2009 Ticket 1.00 0.00 0.00
5/18/2009 Admin 3.50 1.25 51.00
5/19/2009 Ticket 0.75 0.50 19.00
5/19/2009 Ticket 1.00 0.75 37.00
5/19/2009 Ticket 1.00 0.00 0.00
5/19/2009 Admin 2.75 1.25 56.00


On the next worksheet called SUMMARY I have the this set up below

Date TotalHours #ofTickets TimeTask DriveTime Miles Expenses

I want to be able to sum the data for TICKETS only from the worksheet DETAILED LIST into the SUMMARY worksheet as follows:
Date TotalHours #ofTickets TimeTask DriveTime Miles Expenses
5/18/2009 3.25 2 2.00 1.25 51.00 $0.00
5/19/2009 4.00 3 2.75 1.25 56.00 $0.00
ADMIN
PBA
OVERTIME
DOUBLE TIME

I also want to sum of the data by ADMIN, PBA, OVERTIME, DOUBLETIME.

Thanks!!

 

[JPaules]

Have you tried using a Pivot Table?

--
JP

 

[dwayne94]

No, I have not tried using a pivot table. Could I not create formulas to get the totals from one worksheet to another?

 

[dwayne94]

I have a spreadsheet that a previous employee did withthe same info, but the employee is no longer with the company. The spreadsheet is write-protected. When i tried to unlock it; it asked for a password. Unfortunately that employee refuses to give up the password. How to break the password if at all possible?

 

[JPaules]

Have you tried just doing a copy and paste of the info on the protected worksheet to a new one?

--
JP

 

[SkipVought]

1. Use Named Ranges.

2. Add another column for Event and rename as follows...
[tt]
Date Event TotalHours #ofTickets TimeTask DriveTime Total_Miles Expenses
[/tt]
3. The formula for #ofTickets
[tt]
=sumproduct(--(date=$A2)*(event=$B2))
[/tt]
4. The formula for TimeTask DriveTime Total_Miles
[tt]
=sumproduct((date=$A2)*(event=$B2)*INDIRECT(E$1))
[/tt]
This will work for ALL your events.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry, no help here for breaking passwords.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dwayne94]

I was able to cut and paste but I need the worksheets to link up as it was before the spreadsheet was protected. Based on my info below how can I re create the formulas

I have the data below from the worksheet DETAIL LIST

Technician Name - Debra Moak
Week Beginning - 5/18/2009
Region - SW Georgia
Agency - GDC
Metro Manager - Jeff Myers

Date Event TimeTask DriveTime Total Miles
5/18/2009 Ticket 1.00 1.25 51.00
5/18/2009 Ticket 1.00 0.00 0.00
5/18/2009 Admin 3.50 1.25 51.00
5/19/2009 Ticket 0.75 0.50 19.00
5/19/2009 Ticket 1.00 0.75 37.00
5/19/2009 Ticket 1.00 0.00 0.00
5/19/2009 Admin 2.75 1.25 56.00


On the next worksheet called SUMMARY I have the this set up below

Date TotalHours #ofTickets TimeTask DriveTime Miles Expenses

I want to be able to sum the data for TICKETS only from the worksheet DETAILED LIST into the SUMMARY worksheet as follows:
Date TotalHours #ofTickets TimeTask DriveTime Miles Expenses
5/18/2009 3.25 2 2.00 1.25 51.00 $0.00
5/19/2009 4.00 3 2.75 1.25 56.00 $0.00
ADMIN
PBA
OVERTIME
DOUBLE TIME

I also want to sum of the data by ADMIN, PBA, OVERTIME, DOUBLETIME.

 

[dwayne94]

Skip
I think I am understanding the formula, but I need the formula to pull the numbers from the worksheet called DETAILED LIST. How would the formula look as the data is from the worksheet DETAILED LIST and being calulated in worksheet SUMMARY.

Would I not have to incorporate the name of the worksheet in the formulas?

 

[SkipVought]

Not if you use Named Ranges. faq68-1331



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GSCaupling]

I am new to a job and there is a spreadshet that is write protected. The person who created the spreadsheet refuses to give me the password. Is it possible to break the password?

I have a spreadsheet that a previous employee did withthe same info, but the employee is no longer with the company. The spreadsheet is write-protected. When i tried to unlock it; it asked for a password. Unfortunately that employee refuses to give up the password. How to break the password if at all possible?

No one at Tek-Tips is going to help you break a password. This was made abundantly clear the first time you asked.

GS

[red]******^*******
[small]I[/small] [small]Love[/small] [♥] [small]Redundancy.[/small][/red]