Help With Formula Please

[pure vito]

Hi All,

It may seem like I'm palming off all my work , but every interaction here is really helping me towards a better understanding of excel so thank you for that,

Side note, I'm trying really hard to use Xl2bb but for some reason it shows up on my tool bar but the options are greyed out I can't use it for some reason,

Todays Issue,

I'm hoping for some help in creating a formula or possibly VBA might be a better solution I will leave that to the experts to decide,

First image "Rolecheck", Range B1-F1 this row of data is changing based on userform input,

Second image "Available", displaying licences held and date available,

Sheet "Rolecheck" Range C2 I need this cell to look at whatever data is in C1 (C1 data changes) looking at the name in A2 then search for this name in "Available" and then find the licence and if the persons name has "Yes" on that licence show "Yes" in "Rolecheck" C2,

Sheet "Rolecheck" Range F2, the formula in F1 is simply =Today( ), F2 formula should again look at the name in A2 then search for this name in "Available" and look for "Yes" in column E and then would return a "Yes" in "Rolecheck" F2,

Sheet "Rolecheck" Range G2 if C2 and F2 both say yes then this cell should show Available in text,

I really hope this makes sense the most difficult part for me is the changing data in B1-F1 I'm just not sure how to handle this at all,

As always I'd really appreciate any help or advice given thank you for taking the time to read this over,

 

[Peter_SSs]

I'm trying really hard to use Xl2bb but for some reason it shows up on my tool bar but the options are greyed out

See if this helps: Xl2bb got disable

 

[pure vito]

Thank You Peter, I have just tried the steps here but it seems because I'm on a work computer I need administration permission to locate the file in a trusted folder

 

[cspengel]

Sheet "Rolecheck" Range C2 I need this cell to look at whatever data is in C1 (C1 data changes) looking at the name in A2 then search for this name in "Available" and then find the licence and if the persons name has "Yes" on that licence show "Yes" in "Rolecheck" C2,

Try this: =IF(VLOOKUP(A2,Available!C1:O19,MATCH(Rolecheck!$C$1,Available!C1:O1,0),0)="Yes","Yes","")

 

[cspengel]

Try this for Second part: =IF(VLOOKUP(A2,Available!C1:O19,3) ="Yes","Yes","")

try this for 3rd part(Range G2) : =IF(C2="YES",IF(F2="YES","Available",""))

 

[pure vito]

Hey thank you very much for your response and time, however it doesn't seem to be working I have tried adapting it as best as I can but cannot find the solution

 

[pure vito]

Hope this helps

TEST.xlsm

 

[cspengel]

Sorry try this:
first: =IF(VLOOKUP(A2,Available!$C$1:O19,MATCH(Rolecheck!$C$1,Available!$C$1:$O$1,0),0)="Yes","Yes","")
second part: =IF(VLOOKUP(A2,Available!$C$1:$E$17,3,0)="Yes","Yes","")

 

[pure vito]

That's perfect! thank you so much for taking the time, now I can work towards my next issue

 

[cspengel]

That's perfect! thank you so much for taking the time, now I can work towards my next issue

No Problem! Use this for the G2: =IF(C2="YES",IF(F2="YES","Available",""),"")

I believe the previous one I posted will leave a FALSE if the cells in column C are blank.