Vlookup Formula

[brittanyruth]

I have attached the spreadsheet I'm working with. On the Signup Tab this is the place where people mark if they are going to go to a conference. Sheet 1 is going to be a summary sheet of each conference. I need a way to mark Tom Smith as a director and then on the summary sheet count the number of directors going to a specific conference. Does this make sense? On the signup tab the information has to stay broken up by TA.

If you click on the conference from the signup sheet you will get a nice list of the people attending. I can reference that list... but I need the formula that would count it.

Someone suggested a vlookup formula but how would that work? Thanks!

[modytrane]

I am not really clear on what you need done.
There is no Tom Smith on your sheet.
Where would you want to mark up the person by title?
You may have to add a column.
Please fill out one example of the result you want, then someone will be able to help you.
modytrane

[JBeaucaire]

Hello again, Brittany, expanding on the sheet we created? Very good.

1) I don't see Tom smith on the Signup sheet.
2) I don't see the column where you want to designate director status
3) Once #2 is added, you will draw that info to the individual sheets pretty much the same way we've drawn across the name. All you need do is add another column on the summary sheets that INDEX/MATCHES the name already brought over and draws in the director flag...or not if they are not a director.

Go ahead and insert the column where you want to set this status flag, title it accordingly, assign the status to a few of the people and sign them up for a conference or two, then we'll make that formula work for you.

[JBeaucaire]

Here you go, see if this does all that you need.

[brittanyruth]

Yes! The wonderful, WONDERFUL spreadsheet you created is really working for me... but I need this kind of summary sheet. I attached the wrong spreadsheet to my first post. I like the set up of the sheet you just created. How did you get the gray bar at the top to work?

Here is what I was working with...

Thank you so much for your help. You're a genius!

[JBeaucaire]

Um, let's not go backwards. The sheet I provided was an attempt to shorten the work, not lengthen it. All the conferences have a key column hidden right next to it, so that work's done. The Conference summary sheet samples are designed so copying them and changing the SheetName turns the whole new sheet into a new conference summary.

Adding other jobs: you only mentioned "Director" so I made that a "Director" column. That could just as easily be a "JOB" column. Please list all the jobs you would want to track and decide if you want to enter a Letter (D=Director) or just put the whole job title in cell, perhaps with a drop-box.

We can adjust the template conference summary sheets so they are no longer hard-coded to just watch directors.

The Grouping Bar: This control is part of the DATA > GROUP > Group and Outline menu. I simply click on one column then made it a "group" of its own, which created a +/- for it. Very cool for hiding/unhiding groups of rows/columns that need to be hidden/unhidden with ease and frequency.

Here's an expansion of what I did before, I added the summary page you started. Jobs are now listed in column B made from a drop box of jobs listed in column EQ.

Working from the sheet I provided, ask your questions and we'll make additions.

[brittanyruth]

Okay here is the latest version. I've run into an issue with the first conference tab. The second conference tab functions properly. But the AAD tab doesn't populate the attendees. I'm sure I've deleted something that was critical. I tried recreating the tab but that didn't work.

My other question is... under each conference tab I have listed items that are on the summary sheet (location, date, etc). Is there a formula to look at that information and auto update as sheets are copied and added? Hopefully that makes sense.

Other than that.. I think this project is done... Thanks so much for your help!

[JBeaucaire]

Sure, those are standard INDEX/MATCH function. I've done the first 2 columns for you on the Conf Sheets, you can adapt that to complete the others.

BTW, I love your takes on the JOBs, great stuff! Go, go, go.

Oh, the problem with AAD was when you opened the KEY column, it was empty. Just copied the KEY column from the next one over and it woke it back up.

[brittanyruth]

Reviving this one to get help from JBeaucaire again...

The sheet has been amazing for me to use. I've learned a ton. Problem now is I appear to have broken it.

I needed to add 2 conferences to the list. I started by copying the CRA column and key and renaming it CPA. (I need to add CPA and OPA conferences).

This seems to have broken everything. I'm setting the sheet up for 2010 and it's possible that I broke it doing something else. If you can walk me through what I should be doing to add conferences that would be great. I also will need to delete some in the future... I just don't want to break it again because it works so well for the project. Thanks!

[brittanyruth]

I figured out what I did wrong on the signup sheet... I accidentally deleted the "Key" formulas.

I still need instructions on how to properly add and delete a conference to the sheet. Thanks!

[JBeaucaire]

1) You can simplify the formula on all the conference sheets in cell A1 to this:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

2) To delete a conference

a) Simply delete the individual conference sheet or rename it to something else. The formulas on the sheet all trigger off of the sheet name.
b) On the Signup sheet, unhide the "key" column that goes with the column to delete, then delete them both together.

c) Currently, the list on the Summary page will shorten itself if you do this. I think that's bad since the rows of data are manually entered data. When I deleted a conf from the Signup sheet, the entire Summary screwed up.

If you're going to be adding and subtracting a lot, I would take out the formulas in the Summary sheet column A and just enter them into the list column A manually. The rest of the data on that sheet will then always be accurate. You can add and subtract rows to the Summary when you need them.

3) To add a conference

a) As per 2c above, manually insert the new conference into your Summary sheet
b) Duplicate an existing sheet and change its name to the new conference abbreviation
c) Wherever you're adding on the Signup sheet:
---Unhide the two columns to the right
---Select those two columns and COPY them
---Right-click on the same columns and select INSERT (this should add a pair of grouped/colored columns)
---Change the name of the conference in the first column
---Right-click the new name and EDIT HYPERLINK to change it the new sheet