13 tabs, 12 for each month and 1 to total one of the data points in the 12 months tabs.

in each of the 12 tabs for each month there is a column (column I) for a yes or a no, and another column (J) for somebody associated with that yes/no response.

I want the 13th tab to pull everyone that has a "no" beside their name in the 12 months to populate on the last tab. It will have their names and I also need to have the total # of times their name appears in the 12 months with a "no" beside it *because most times it will have "yes")

I'm guessing the last part will have to be concatenate?

how to do and many thanks

3/25/2009 4:25:50 PM

http://en.wikipedia.org/wiki/Pivot_table

3/25/2009 5:47:09 PM

JEBUS PPL COME ON

3/25/2009 5:58:35 PM

are you willing to go through several intermediate steps that may involve superfluous columns and maybe extra worksheets (tabs)?

I was thinking the PiviotTable would be the last step to actually tabulate all your "no" answers per person per month, but it will take some other work to get all of that data into one table.

3/25/2009 6:31:29 PM

this is either going to involve pivot tables or macros. I'd go the pivot table route first.

3/25/2009 9:24:03 PM

couple other questions

- is the list of names and responses arbitrarily long in each month's worksheet?
- can names be repeated within each month?
- is there a "master list" of names somewhere?

can you upload a copy of a sample file that you can share (or PM me for my email address)

3/25/2009 9:29:18 PM

sounds like a simple vlookup

3/25/2009 10:22:51 PM

^ it depends entirely on how the data is set up and how the questions I asked above are answered.

but if you think it's a "simple vlookup", then cough up the formulas

3/25/2009 10:32:24 PM

Quote :

" if you think it's a "simple vlookup", then cough up the formulas"

3/25/2009 10:35:59 PM

after further examination I change my answer to "summary page and pivot table".

3/26/2009 7:14:19 AM

- is the list of names and responses arbitrarily long in each month's worksheet?

no, very short. 30-40 total rows of data, all columns are very basic short responses.

- can names be repeated within each month?

yes, very likely to be the case.

- is there a "master list" of names somewhere?

there is.

are you willing to go through several intermediate steps that may involve superfluous columns and maybe extra worksheets (tabs)?

sure, so long it only needs to be done once or require very little maintenance.

I am familiar with vlookup, I just dont see how you tell it to look in all 12 and then combine all "no" responses on one sheet as a sum.

[Edited on March 26, 2009 at 12:32 PM. Reason : egr]

3/26/2009 12:31:33 PM

basically, every time an employee opens an account they are required to help the "client" do something.. if they do it, they have a "yes" by their name in another column, if they dont, they get a "no" (same column as where the yes would be).

I need to be able to add names every day an account is opened with the rep, go back in and update it with a yes or no....

then, that data needs to go to the 13th worksheet if its a NO, only. Since employees over the year will open many accounts I dont want their name to appear many times, but rather just once with a total # they had a "no" beside their name... keeping in mind that there will be the possibility their name gets a no in april, may, june, etc..... no telling how many rows of data will be in each month as I dont know how many accounts will be opened.

Thanks for offering suggestions

[Edited on March 26, 2009 at 12:36 PM. Reason : aerg]

3/26/2009 12:36:19 PM

ok, here's a good start.

First I create a table in a the 13th worksheet, "Summary". It has all the employees on the left side, then all the months on the top.

Then to fill in the table, you only need one formula. The guts of the formula is SUMPRODUCT, which basically counts the number of an occurrence in one column and multiples by another set of matching occurrences in another column.
The rest of the formula is "dirtied" up by the INDIRECT function, which uses the Month headers to figure out which worksheet to look into.

Using one formula for all cells from B3: D10, it creates a table like this:


here is the formula

=SUMPRODUCT((INDIRECT("" & B$2 & "!" & "I$2:$I$100")="no")*(INDIRECT("" & B$2 & "!" & "J$2:$J$100")=Summary!$A3))

3/26/2009 1:18:26 PM

wow i have an excel question too and how convenient theres a recent thread...


i have a column of data (dollars) which is the =sum(box1*box2). i want to copy the resultant product into another spreadsheet, but it is keeping that formula. how can i fix the numbers and not have the formula still?



nvm i figured it outski

[Edited on March 26, 2009 at 2:40 PM. Reason : .]

3/26/2009 2:35:18 PM

^^ that seems absolutely perfect. Going to mess with it now and see if I can repeat what you have done.

THANK YOU.

3/26/2009 3:21:56 PM

Okay, one more question.

lets assume there are 200 employees, but only 50-100 end up on this list over the course of a year... I dont want the other employees to show up with 0 in the summary tab. How can I make the summary tab only pull the names from the 12 months that actually had a "no"... most of the employees should have a yes, I'm trying to pick out the few with a "no"....

seriously, the excel d/load was tremendous help. Any chance you could zero out one of your employees and do the aforementioned??

if that is not easy, I suppose just a simple sort would work.... huge kudos to your work.

[Edited on March 26, 2009 at 3:34 PM. Reason : erg]

3/26/2009 3:27:15 PM

you could just add an if statement

it would look like this

=if(formula = 0, "", formula)

=if((SUMPRODUCT((INDIRECT("" & B$2 & "!" & "I$2:$I$100")="no")*(INDIRECT("" & B$2 & "!" & "J$2:$J$100")=Summary!$A3)))=0,"",(SUMPRODUCT((INDIRECT("" & B$2 & "!" & "I$2:$I$100")="no")*(INDIRECT("" & B$2 & "!" & "J$2:$J$100")=Summary!$A3)))

3/26/2009 9:03:38 PM

^ that would just show blanks, instead of 0's (which is what it does now). If I understand ^^ correctly, he doesn't want the employees to show up at all if they don't have any "no" responses.

The easiest thing to do would be to turn on AutoFilter in the Summary sheet (Data > Filter > AutoFilter). That should turn on AutoFilter in the Summary table header row. Then in the "Total" cell, select the AutoFilter drop-down and select "(Custom)". In the next window, select "is greater than" and "0". That way, all the rows with 0 will simply be hidden from view.

The only drawback to this method is that it is not dynamic. That is, AutoFilter is only calculated once, but if the data in the table changes (like if an employee that previously had 0 no's gets a 'no' response) then the row will still be hidden because the AutoFilter is not reevaluated.
Two ways to deal with this:
1) just re-apply the AutoFilter settings again
2) use a macro to make sure the AutoFilter settings are automatically updated whenever data on the Summary page is changed. I have done this in my sample file, and have re-uploaded it.

If you want to move the macro to your own sheet, just right click on the Summary sheet in my file and select View Code. Copy all of the code, then go to your own file and go to View Code in your own Summary sheet, and paste it there.

http://joelion.com/temp/Calli-lookup.xls

3/26/2009 10:50:37 PM

^ is the man. Thanks to everyone.

3/27/2009 9:33:08 AM