Excel Help Needed

Twentymac · 31 Mar 2009 at 21:36

ok guys dunno if anyone knows how to help me but here it goes....

my problem is for a call logging system i want to try and make.

Sheet 1 - This is where all customer details will be listed
i have managed to make the data validation list from sheet two where the list data is.

Sheet 2 - This is where the drop down list data is

Sheet 3 - This is where the call log results i want to be added up into the numbers section by what ever is resulting in the sheet one drop downs

Andy100 · 31 Mar 2009 at 21:46

You could drop sheet 1 into a pivot table.

I don't know if it matters to you but if I were doing it I'd put date and time columns in too. That way you can track some additional trends.

Twentymac · 31 Mar 2009 at 21:55

oh yeah i've just made a quick example table to show what i needed.

Steve2000 · 31 Mar 2009 at 21:57

Wouldn't it simply be a count case here? I'm struggling to understand what it is you're aiming for.

Twentymac · 31 Mar 2009 at 22:02

Ok say in sheet one if James and Peter were called and no one answered then i will choose "not answer" from the drop downs . then in sheet 3 i want it to show how many of which calls have been made

Tenzen · 31 Mar 2009 at 22:05

use data validation for the cell selection (answer, no answer etc

Then do a countif on the text string for the range of cells you want to count IF they match a string you specify (answer/no answer etc).

however you would need to edit the countif range if the range grows (or set it to as mucha s you think likely)

Twentymac · 31 Mar 2009 at 22:08

=COUNTIF(Sheet1!C2,not answer)

thats not right is it ?

Tenzen · 31 Mar 2009 at 22:10

=COUNTIF(SHeet1!C2:CXXXX, "not answer")

remember text strings sit inside quotation marks. and you want to calcualte on a range - i.e more than one cell.

It will basically look at the range and count how many times(cells) it finds the string you specify.

Do this in 3 columns and if you have 3 choices in the validation drop down you got your 3 stats

Twentymac · 31 Mar 2009 at 22:24

Cheers for that mate its worked, but now im stuck on how you mean do it in 3 columns, i sort of know what you saying but dont know how to lay it out

Explicit · 31 Mar 2009 at 22:25

Sent you an email

Twentymac · 31 Mar 2009 at 22:32

cheers mate just copied the forumla from sheet 3 and pasted into my sheet 3 and it works fine !

Lifesafer !

Tenzen · 31 Mar 2009 at 22:33

For each countif condition just use another column.

So
one column for no answer
one column for answer
one column for something else

Tenzen · 31 Mar 2009 at 22:34

gj glad you got it working!

kaiowas · 31 Mar 2009 at 22:38

You should be able to just use Sheet1!C:C for the range which will cover all cells in column C to save you having to worry about making the range big enough.

Twentymac · 31 Mar 2009 at 22:44

exactly what Explicit did for me

N9ne · 31 Mar 2009 at 22:57

Isn't this easier in Access?