VLookup

[thekrunkymonkey]

Hello all,

Ok so I have a problem. I have created an automated excel form which is used as an auditing and tracking document for various things. One of its functions is to track different groups of people through my organisation and then represent their attendance or non attendance to certain tasks in graphical form over a set period of time. So far, all good!

The data currently can be sorted into gender but it has to be done manually. Essentially what I would like the form to do is this:

When I enter the data in the name field, I would like the option of assigning it a gender (M or F) which will then be represented by an appropriate colour in that cell (say blue for M and red for F). Further, alongside each name field are various columns of figures, 4 to be precise, which represent different things. What I need is to be able to link those columns to the gender in order to create a total BASED on gender.

I hope someone can help because currently I have to comb the list of names and then assign each a male or female designation and then create the SUM manually in order to gather the numbers. I can’t simply sort the names before hand into male or female groups because the names have to be ranked based on the figures.

I hope this is clear?

Thanks very much,
Tim

[thekrunkymonkey]

Excellent. This sounds like a reasonable thing. Thanks for the info. I'll be back!

[JBeaucaire]

Humans *love* to color things and we think it perfectly natural to then categorize things later by the colors we assigned. From a programming standpoint that is definitely the long way around that block.

It's a database. Databases have a field for every critical piece of information. Add a GENDER field, put the M / F answer in it and your life becomes simple. You can filter by that column/field just like you could for any. You can then do your "coloring" using normal conditional formatting looking at the contents of that cell/column.

Keep life simple for yourself, add the GENDER column and enjoy easier programming.

[thekrunkymonkey]

Hello.
I have attached the dummy workbook as suggested. I have described the problem in text boxes on that workbook and I hope that it is sufficiently clear.
Thank you so much for your help.
Tim.

[thekrunkymonkey]

Humans *love* to color things and we think it perfectly natural to then categorize things later by the colors we assigned. From a programming standpoint that is definitely the long way around that block.

It's a database. Databases have a field for every critical piece of information. Add a GENDER field, put the M / F answer in it and your life becomes simple. You can filter by that column/field just like you could for any. You can then do your "coloring" using normal conditional formatting looking at the contents of that cell/column.

Keep life simple for yourself, add the GENDER column and enjoy easier programming.

Thanks for the suggestion. The problem arises from the fact that I must sort the data based on one column of figures and not by gender however I still need the gender data for statistical purposes. I hope that makes sense?

The colour thing is not a necessity but as the tables will be used by goobers who become confused easily I thought it one way to reduce the possibility of confusion.

Thanks again.

[JBeaucaire]

I'm not suggesting you stop using your database the way you intend, I'm just suggesting you add another field for gender. Every goober knows what "gender M/F" means so that won't confuse anyone. And any statistics you need to run that include Gender become remarkably simple...because the data is actually there.

[JBeaucaire]

This is what I mean. By just adding the column B for Gender it all is done with simple SUMIF() and COUNTIF() formulas. Changing the M/F drop down in column B immediately updates the stats. Conditional formatting handles the coloring in A:B.

And deleting the M/F does what you'd expect, removes the row from the stats and coloring.

[thekrunkymonkey]

This is brilliant. Thank you ever so much!

3 cheers and a skippy badge!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.