Hi,
I have 2 tabs.
On Tab #2, I need to verify if the information is on Tab #1 and return a Yes/No answer depending if the cells are empty or not.
Thank you in advance for your help!
Hi,
I have 2 tabs.
On Tab #2, I need to verify if the information is on Tab #1 and return a Yes/No answer depending if the cells are empty or not.
Thank you in advance for your help!
Try to put into D5 on ListNames tab:
Formula:Please Login or Register to view this content.
and drag it down
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
It doesn't work for everyone on the tabs
Tell us more - specific detail of "it doesn't work"?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Applying the formula, it says that Steve doesn't live in Canada but yet he does (tab 1) and also, on tab 2, there's a name; Patrick, which isn't on the list on tab 1, can it show NA if the name doesn't appear on tab 1?
Thanks! (sorry, trying to express myself as best as I can)
Misunderstanding. I've thought that only if date is in that means he lives in Canada.
If YES have to be for each from list on RAW please put into D5:
Formula:Please Login or Register to view this content.
and drag it down
Hi All,
another approach
=IF(SUMPRODUCT(('RAW Data'!A$5:A$10=C5)*'RAW Data'!B$5:N$10),"Yes","No")
Hi Kokosek, did not mean to overlap
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
new file updated if it helps!
Thanks, this seems to work!
If I have a name on Tab 2 that isn't on Tab 1, how can I have the cell reflect NA instead of Yes/No ?
Formula:Please Login or Register to view this content.
You put as comments on Steve's cell that he's not exist but he's exists.
Only Joe and Patrick do not exist.
Last edited by KOKOSEK; 04-25-2019 at 11:50 AM.
it works except it doesn't return the "No" if someone does not live in Canada
The 3 scenarios I need the formula to validate is:
I found the name...AND he lives in Canada ---------> Yes
I found the name...AND he doesn't live in Canada ------> No
I did not find the name --------> NA
Thank you once again!
Last edited by exced; 04-25-2019 at 12:29 PM.
Sorry, I think that I don't understand you definition of living in Canada.
I'll ask once more: is date in any of column B:N means that person lives in Canada?
Check attached file.
Last edited by KOKOSEK; 04-25-2019 at 01:38 PM.
Yes, the date in any of column B:N means that the person lives in Canada
When I try to apply the formula in a different excel book, it doesn't work....the Yes and N/A works, but not the "No".
I have people who do not show on my list yet they all appear with a Yes next to their name, why is that ?
If I use this formula outside Book1, must I change anything in it if the tabs are called the same ?
In the case where I am using this formula, if the value is found in column A, I need it to validate of there's any date on same row between M and AO
ex;
If John is in A7, it must validate if there's any entries between M7 and AO7
Sorry, I thought once I had the formula I could edit that aspect but I am not an expert with offset
=IFERROR(IF(SUM(OFFSET(Insurance!$A$4,MATCH(BDP!$C19,Insurance!$A$5:$A$4000,0),0,1,15))>0,"YES","NO"),"N/A")
As I understand BDP!C19 is particular cell with name to check (original List of Names tab) and Insurance!A:A is col. with names to check (original RAW data tab).
If you want to check include col. AO7 to check, you have to change highlighted parameter:
Formula:Please Login or Register to view this content.
Column AO if I am not mistaken will be 41 (put 42 just in case )
Thanks, how can I set it to start verifying from column M if there's a date?
Right now, with the OFFSET, I believe if it finds BDP!C19 in Insurance!A:A, it will start checking for a date 1 column away, correct?
I would like, once it matches BDP!C19 in Insurance!A:A, to start checking from column M until AO on the same row. What must I change for this to happen ?
Whewn I test I have someone with no value from M to AO and the formula keeps returning a "Yes" when it should be a "No".
I uploaded a copy of my original file but filtered some information.
If you look at tab BDP,
Column AZ are accurate results I verified manually.
When I put the formula in column BB, notice I have a bunch of discrepancies.
Formula on tab BDP, column AZ must take ID from tab BDP, column C and verify if present on tab Insurance A:A and if it finds it, verify between M and AO if there's any data.
Wanted outcome on tab BDP, column BB with formula:
If the ID on tab BDP, column C is found on tab insurance A:A AND there's information between column M to AO ---> Tab BDP, column BB should display Yes
If the ID on tab BDP, column C is found on tab insurance A:A but there's NO information between column M to AO ---> Tab BDP, column BB should display No
If the ID on tab BDP, column C is NOT FOUND on tab insurance A:A ---> Tab BDP, column BB should display N/A.
I hope this helps!
Last edited by exced; 04-26-2019 at 09:18 AM.
Try then:
Formula:Please Login or Register to view this content.
accept of course with CSE
Attached file shows that all rows where you put expected results are matching.
A big thank you for your relentless efforts, it works !!!
You welcome.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks