IF statement - pull data based on criteria

[Nikhil R]

Hi team,

I need some help a little bit. Trying various methods to pull data from one table onto another table in another sheet.

For example, one Column has Interested as a header, and if Yes - to pull candidate name into another table that's on another sheet with the same header under candidate name and vice versa for post code number and email address.

If it's not clear, please let me know

 

[Fluff]

For this to work you will need to convert the 2nd table into a normal range. Also change Table1 to the name of the 1st table

=CHOOSECOLS(FILTER(Table1,Table1[Interested?]="yes"),MATCH(B3:E3,Table1[#Headers],0))

 

[Nikhil R]

For this to work you will need to convert the 2nd table into a normal range. Also change Table1 to the name of the 1st table

=CHOOSECOLS(FILTER(Table1,Table1[Interested?]="yes"),MATCH(B3:E3,Table1[#Headers],0))

Amazing!

Thank you man, and say if i add a new entry in table 1 on interested candidates - normally i put in the $ sign so it automatically adds and separates - where would i put that in?

 

[Nikhil R]

Amazing!

Thank you man, and say if i add a new entry in table 1 on interested candidates - normally i put in the $ sign so it automatically adds and separates - where would i put that in?

So it automatically adds an entry onto the second table with the data

 

[Fluff]

So it automatically adds an entry onto the second table with the data

That's the idea & if you change a Yes to a No that entry will be removed from the formula.

 

[Nikhil R]

That's the idea & if you change a Yes to a No that entry will be removed from the formula.

Yeah that's great, it works. Thank you Fluff

but if i was to add another entry on "interested candidates" table - I'm playing around with it so it automatically adds onto the candidate track but can't seem to find a way for this for multiple entries

 

[Fluff]

Not sure what you are saying. The formula will automatically find any entries in the table that have Yes

 

[Nikhil R]

Not sure what you are saying. The formula will automatically find any entries in the table that have Yes

Yeah apologies - I'm going to add a list so it notifies us to enter a value in that field as as you can tell i have a memory like a fish! Thanks again Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Nikhil R]

I'm wanting to auto populate the A6 through to E6 columns on "Onboarding Track" from the "Candidate Track sheet" - IF the "Candidate Decision" on Candidate Track Sheet is set to "Candidate Accepted" . I've been tinkering and playing around with formula's and the one from previous but i'm assuming because the table from "Candidate Track" has now been turned into normal range - it isn't working. Even tried IF statements too.

 

[Fluff]

Try

=FILTER('Canditate Track'!A6:E100,'Canditate Track'!K6:K100="Candidate Accepted")