IF, THEN or ????

[Teach927]

Hello, I am trying to complete a worksheet to record the hours of training by category for a fire department. On one sheet each training is listed with Course _Name, Date, Category, Hours_Offered. Then the trainer lists each attendees ID code in a range of cells in the same row.

Then EACH firefighter has his or her own sheet. I want to automatically populate the individual sheets using the training sheet as reference. Here's my dilemma: How do I tell Excel, If the number in cell B3 appears in the range of cells on the attendee list J1-AM1, then return the value of hours from cell D1, if not, return 0.

I tried using LOOKUP but it returns the hours whether the ID is in the list or not (unless I am doing it wrong….which I may be).

[haripopuri]

Upload a sample file

[Richard Buttrey]

... but before you go too far with this I'd seriously consider why you are using separate sheets for each 'training' and a sheet for each firefighter.

I suspect you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.


A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, I suspect yours may exhibit all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

Upload the workbook and explain what you want to achieve with it and no doubt we can point you in the right direction.

[luv2glyd]

Hey Teach, looks like the match functions is prob the way to go:

=IF(ISNA(MATCH(B3,J1:AM1,0)),0,D1)

Let us know how it works out.

[Teach927]

I have attached a sample file to help me solve an issue.

The goal is to add all trainings to the Data Entry Tab, then using the formulas in the Doe sheet, it automatically populates cells on the Doe sheet. You can see that it worked for the first two rows and then it quits working. Any ideas???

[misrasomendra]

See the file.