Office & Productivity Software forum

General discussion

Advanced Excel Formula

Alright, so this one goes out to the advance excel "programmers" out there.

I have a workbook that has multiple sheets within it. One sheet lists various tasks that a person has been working on over a period of time. Another has a list of trainings that have taken place over a period of time.

What I'm wanting to do is populate a cell with a "yes" or "no" value if their employee id is present on the training spreadsheet along with that process name.

Any ideas?

Discussion is locked
You are posting a reply to: Advanced Excel Formula
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Advanced Excel Formula
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Maybe start with a better specification?

In reply to: Advanced Excel Formula

- "that process name" should refer to something. It doesn't. According to most definitions a task is not the same as a process.
- "their" in "their employee id" seems to refer to nothing. Not to "a cell" anyway, because cells don't have employee id's.
- and it's not clear what the attributes (columns) of a certain training (a row in the list of trainings) are.
- somehow the list of participants of a training seems to be missing in your spec.

In stead of a programmer you seem to be in need of an analist first.

By the way, why not try a real database (like MS Access) for this purpose?

Kees

Collapse -
Thanks for the lack of help.

In reply to: Maybe start with a better specification?

I don't need the formula actually written for me, I need an idea of where to begin.

If you call Access a 'real' database, then I'm sure I wouldn't want to rely on you to help with that either.

Collapse -
Sorry.

In reply to: Thanks for the lack of help.

My experience is that advice based on an incomplete understanding of the problem usually is bad advice. So I tried to get a better picture.

You don't really provide that, so all I can say now is, that to look for presence of fields in tables I use the VLOOKUP function. It works on one key only, so you might have to combine columns using string concatination.

Still, I don't consider VLOOKUP or IF (as suggested by Mark below) as 'advanced' use of Excel. Intermediate, at best. So it might well be that you already know of all that and decided that it won't solve your problems.

Kees

Collapse -
Most of info needed at this link.

In reply to: Advanced Excel Formula

Collapse -
Thanks

In reply to: Most of info needed at this link.

Thanks a lot, I'll see where that takes me.

Collapse -
It shouldn't be that difficult.

In reply to: Advanced Excel Formula

Like Kees says, you use phrases like employee ID and Processes but not in what context they are relative.

But if I understand you properly, you need a cell that shows Yes or No if an employee has been on a training course/session, and you want the course/session name.

That data has to have been entered elsewhere in the spreadsheets in the first place, so all you need is IF statements.

So, firstly I would use a flag. A flag is Up or Down and this can be represented in a cell by a 1 or a 0. Whenever an entry is made to show that employee "A" has taken a course, the flag at the end of that spreadsheet row is set to 1. Otherwise it is 0. These are set by a simple IF statement, eg

=IF(B3="",0,1) where B3 is the cell you use to indicate he has taken that course, the "" is an empty string, or an empty cell, so the flag cell is set to 0 if B3 is empty and set to 1 if it is not empty.

Then, in the sheet where you want to list all the employees and what courses they have been on, use the IF statement to check the flag cells.

Does any of that help?

Mark

Collapse -
Thanks

In reply to: It shouldn't be that difficult.

Yeah, that does help out. That's pretty well what I was thinking. I'm not the one creating the spreadsheet, but I am responsible for helping to teach these things to them.

This will work if she does a major overhaul on her current design, which I was hoping to avoid, but I don't think that's going to be completely possible. I'm not sure a database is really needed here, though in the end it would make the job easier, but I'll see what she decides and aid from there. Thanks for the confirmation.

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

SMART HOME

This one tip will help you sleep better tonight

A few seconds are all you need to get a better night's rest.