Excel Help

[AHarvey]

Hi all,

I have been asked to come up with a simple spreadsheet to track any emails/calls we get about our employees regarding either praise or complaints.

So I have an overview page and a records page.

The records page is 4 columns:

HOST NAME > COMPLAINT > PRAISE > COMMENT

In the overview page, I have :

HOST NAME > TOTAL COMPLAINTS > TOTAL PRAISE

As the compaints come in I will go to the records page and enter the hosts name, put a Y in either the complaints or praise columns and fill the comment fields.

On the overview page I want the complaint field to go down the Host name column in the records page and find all records of that host, then to automatically find all complaints about them in order to show total complaints. I will then do the same for total Praise.

How do I do this?

Obviously, its a formula but other than basic stuff my excel is pants.

If you could explain the formula as well I'd be very grateful

How do I do this?

 

[Dolph]

How many people are involved? Is the host name list likely to change regularly?

If it's not many people and the names are fairly stationary, you could do something with if or and statements to give you numbers you can add up on the overview page. (along the lines of =and(hostname="bill", praise=Yes) in one cell, followed by =if(prev cell=true, 1, 0) and use a countif or sum statement on the overview to add it up)

You could also use a pivot table to sort and display the info, but that's not as dynamic to update.

Other people may have better ideas though, excel's not my strong suit. I'd use a very simple access table for the above and spit the data out for the excel sheet if it was necessary.

 

[TheKnat]

TBH it sounds as if you would be better off using access. That way you could create a custom report and form to display all the information you want to see.

 

[Dolph]

TBH it sounds as if you would be better off using access. That way you could create a custom report and form to display all the information you want to see.

I'd agree, it's relational data he wants to store and sort, so using a relational database is definitely the 'correct' approach.

however, there may be reasons why he's not using one (such as the program isn't available to him) that mean he's limited to excel.

It's possible to do what he describes there, it's just a little longwinded.

 

[House]

Lots of ways of doing this but as said it depends on fixed the host field is.
If its only 3-4 people then If fucntions will work.
Or possibly a Sumif or countif command next to a list of Host names.
If you are talking about a constanly changing list then Id use either Piviot tables of if its big enough head of into database land and do it all in access.

Most data handling jobs are sooo much easier once you do them in the correct tool, a database.

 

[Tomsk]

in excel2007 use the COUNTIFS function

 

[Dolph]

There is always the compromise solution, use the excel sheet as a datafeed for access so it can query it and return the results on the frontpage of the sheet with a refresh button...

That way you get the ease of putting in the data in Excel (which some people do find easier, even though it's not, just from familiarity) and the benefits of the relational data...