How to look up data from multiple rows and columns from a single cell reference

Hi All,

There is a worksheet I am working on which is about Training and competency register. I have bumped into a few roadblocks which I need help with. I have attached the file and will reference from it. On the 'Competency Assessment' sheet, the cells C5:C99(highlighted yellow) should automatically show the data(the color blue or green) relevant to 'Position Requirement' sheet from organisational positions(Director, Manager etc.) as per the reference to the position mentioned in C3(Director, Manager etc. highlighted Yellow), if blue then adjacent cell to be automatically green. I have tried Vlookup, but shows an error.

Also how to make the 'training plan' sheet self populate color based on the data from the other two tabs. Ex. Based on the employee name and his/her position the cells should automatically update for the training required.

Cheers,
SB

Welcome to the forum.

I think you are probably using Office 365 - please update your forum profile accordingly. Thanks.

Please manually add some results to show what you want.

Please don't take this as coming across rudely, most certainly not my intention - but you have a LOT to sift through on this workbook, and your description of what you want to do is escaping me.

The one thing I did figure out is your "Lookup" function on 'Competency Assessment"

Don't use Vlookup, and do an index/match instead.

In cell C5 on "COMPETENCY ASSEMENT" put this formula

Please Login or Register  to view this content.

You'll need to change up your ranges in the above formula to match up the correct columns across the two tabs. For example, column "C" on "Competency Assessment" is for "Manager"

But in the lookup tab being used (POSITION REQUIREMENT) "Manager" is in Column "E"

If we go to the right of "C", one column, in "Competency Assessment" we see "Company Secretary"

But, on the POSITION REQUIREMENT tab, if we move one column right, we see "Contracts Manager"

My recommendation, to avoid mass confusion on yourself, make sure your columns start w/ the same, and remain the same, left to right. This will allow you to write variables that will scale as you drag them to the right for other columns

(I hope that made sense?)

If you dont want to change things up - just be careful and make sure you are referencing the correct column for each new position as you move "right".




I might be completely misunderstanding what you are trying to do, so if it doesn't make sense, please try to explain with a little more clarity on what you're after... still, im going to take a stab at it...
*IF* I am understanding the green boxes portion on "COMPETENCY ASSESSMENT" tab, correctly, then you simply referenced the incorrect cell in your EDATE formula:

Please Login or Register  to view this content.

The above formula is looking on the "COMPTENCY ASSESSMENT" tab to determine the date of training complete. It's looking to see it needs to use "NA" and "Competent" to determine date & number of months.

You want B10 of competency assessment, but you want B8 on the TRAINING PLAN tab. Why B8?

Your tables have identical values in the "A" column, but one range starts on A3, the other starts on A5, and your formulas are written as though both ranges are starting on the same point.



Let's use "Fire Warden" as an example.

On "competency assessment" tab, Fire Warden is in Row 10
On "Training Plan" tab, Fire Warden is in Row 8

The way you have your formula written, it's not going to pull the correct information, so we should rewrite the formula that goes into D10, on "Competency Assessment" tab as:

Please Login or Register  to view this content.

Let me know if this helps and I'm on the right track... if I am, ill take a stab at the remaining issues

Hi,
Thanks for looking into it.

To answer your questions the position row(highlighted orange) in Competency Assessment sheet would not necessarily be in the same order as the organisational positions row in Position Requirement sheet. The reason for that being the Competency Assessment will be referencing the Employee Names instead of positions, hence the drop down in position row in Competency Assessment. I was looking for something that say like if I select from the drop down in position in Competency Assessment sheet, it should automatically add the reference from Position Requirement sheet, lets say I choose 'Director' from drop down list, then the D5 cell should automatically add 'Training Required' to the cell, and if I choose 'Manager from drop down list, D5 cell should automatically add 'Competent' referencing from the Position Requirement sheet. I tried using the IF function to complement your formula as below but it comes out to be False, if the IF statement works I will be using nested if for referencing all the columns(suggest me if there is alternative simpler option).

Formula:

Please Login or Register  to view this content.

BTW I added a new column after the 'TD' in Competency Assessment sheet for Position requirement(highlighted yellow), because the Training date needs to be entered in the TD column.(sheet attached)

Also the Green Boxes, Red Boxes, and Orange boxes portion in the Competency Assessment Tab are referencing from the Training Date(TD) and Frequency in the same tab and not from the Training Plan Tab.

Also in The training plan tab, no data needs to be entered, it should self populate based on the data from Competency Assessment Tab.

I know I made this too complex and too many links going on, and I myself am getting headaches coz of this. If you have any suggestions to simplify it, that would be lovely.

Cheers,
SB

Try changing the formula in cell D5 to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

If I am understanding the layout of the COMPETENCY ASSESSMENT sheet correctly, that there will be a Position Requirement column between the TD and TE columns for each position, then try the following:

Formula:

Please Login or Register  to view this content.

The above formula is modeled in the Director, Contracts Manager and Construction Manager areas.
Let us know if you have any questions.

If they are related to the original query then I feel it will be alright to continue in this thread. On the other hand if they are not, and if you are satisfied with the solution to the original, then I suggest marking this thread as 'Solved' using the thread tools menu above your first post and open a new thread for the new queries. I hope that you have a blessed day.