DearAll,
I am trying to enter employees bank loan interest recover detailes in excel sheet ("3-2 Reimbursment" Sheet). For this purpose i use two sheets, one "3-2 Reimbursment" , other "Loan Details". in Account Details sheet, there are many emloyees details and some amployees may have more Loan numbers. (My Excel version is 2016)
While this purpose, have two issues,
01). I tried to get multiple loan numbers belongs to the emploee (PF No.) in to dropdown list using vlookup function (in to "3-2 Reimbursment" Sheet colum "D" in to dropdown list) can do this?
02. I try to prevent duplicate entries. this purpos i captured Employee number (Colum A), Loan number (ColumnD) & Loan period (Colum G & H). if i want, Loan period can get one colume like this 2023/10/02 - 2024/02/05.
Specialy in this case, should seems as duplicate (red clour) same loan period or part of loan period. as example, first time i enter,
PF No. Loan Number loan period
36224 10292 2022/05/03 - 2022/12/06
Second time While enrering:
36224 10292 2022/05/03 - 2022/12/06 (Same Period - Should Duplicate)
or
36224 10292 2022/01/03 - 2022/12/06 (Part of period - Should Duplicate)
or
36224 10292 2022/05/03 - 2024/03/09 (Part of period - Should Duplicate)
or
36224 10292 2020/01/03 - 2024/02/10 (Part of period - Should Duplicate)
Hope your help.
Thanks,
Priyantha
,
Getting Multiple Vlookup Values & Hightlight Duplicate row
-
- 2StarLounger
- Posts: 116
- Joined: 10 Oct 2022, 02:52
Getting Multiple Vlookup Values & Hightlight Duplicate row
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 257
- Joined: 15 Aug 2016, 11:23
-
- 5StarLounger
- Posts: 683
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Getting Multiple Vlookup Values & Hightlight Duplicate row
I think you missed the link
You do not have the required permissions to view the files attached to this post.
Arnold: How did I get in here?
Taxi driver: The door opened, you got in.
Taxi driver: The door opened, you got in.
-
- 3StarLounger
- Posts: 257
- Joined: 15 Aug 2016, 11:23
-
- Administrator
- Posts: 79560
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Getting Multiple Vlookup Values & Hightlight Duplicate row
@Priyantha: You haven't received replies in either forum. I suspect that nobody really understands your question.
Could you try to explain it more clearly?
Could you try to explain it more clearly?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 169
- Joined: 11 Jun 2012, 20:37
-
- 2StarLounger
- Posts: 116
- Joined: 10 Oct 2022, 02:52
Re: Getting Multiple Vlookup Values & Hightlight Duplicate row
Dear All,
Ok , Thanks I'll explanne, I have two work sheet, one "3-2 Reimbursment" , other "Loan Details". In "Loan Details sheets will be, Colum A _ Employee Number like 37224, 36224 ....... ect, Colum D _ Loan Numes like 10292, 231026 .... ect. Some employees may have multiple loan numbeers. (Eg. EMP No. 37224 have 3 Loan Numbers like 10292, 231026, 102922323)
In "3-2 Reimbursment" sheet will be in Colum A _ Employee Numbee like 37224, 36224 ....... ect, in colum B Employee Name, in colum D _ Loan numbers, in Colum G _ start date (like 2023/10/02), in colum H _ end date (like 2024/02/10) & in colum I _ Amount.
I try to get loan numbers concerned employee in to colum D as drop down list (using vlookup or any methord) from "Loan Details" sheet Colum D (Eg. in A2 _ employee number 37224, his all loan numbers _ 10292, 231026, 102922323 should display in D2 as dropdown list, next A3 _ 36224, his Loan numbers 108024, 22222222, should display in D3 as dropdown list ........... ect)
My other issue is to prevent duplicate entrys in "3-2 Reimbursment" . in This purpos i want to captured Employee number (Colum A), Loan number (Column D) & Loan period (Colum G & H). If i want, Loan period can get one colume like this 2023/10/02 - 2024/02/05.
Specialy in this case, should seems as duplicate (red clour) same loan period or part of loan period Colum G & H) with Employee number (Colum A), Loan number (Column D)
As example, first time I type in "3-2 Reimbursment" sheet, A2 _ EMP No. 37224 B2 _ Name, D2 _ Loan number 10292, G2 _ Start date , 2022/10/02, H2 _ End date, 2023/12/10, I2 _ Amount , 2500.00,
second time i type A3 _ EMP No. 37224 B3 _ Name, D3 _ Loan number 10292, G3 _ Start date , 2021/12/02, H3 _ End date, 2023/12/10, I3 _ Amount 15,000.00 this should seems as dulicate. Because the first scenario is covered by the duration of the second senario. this is in the fist instance, some payment has been made up to 2023/10/01 to 2023/12/10 and in the second instance, the payment should be only up to 2023/12/02 to 2022/10/01.
Please help to solve this issues.
Tanks,
Priyantha
Ok , Thanks I'll explanne, I have two work sheet, one "3-2 Reimbursment" , other "Loan Details". In "Loan Details sheets will be, Colum A _ Employee Number like 37224, 36224 ....... ect, Colum D _ Loan Numes like 10292, 231026 .... ect. Some employees may have multiple loan numbeers. (Eg. EMP No. 37224 have 3 Loan Numbers like 10292, 231026, 102922323)
In "3-2 Reimbursment" sheet will be in Colum A _ Employee Numbee like 37224, 36224 ....... ect, in colum B Employee Name, in colum D _ Loan numbers, in Colum G _ start date (like 2023/10/02), in colum H _ end date (like 2024/02/10) & in colum I _ Amount.
I try to get loan numbers concerned employee in to colum D as drop down list (using vlookup or any methord) from "Loan Details" sheet Colum D (Eg. in A2 _ employee number 37224, his all loan numbers _ 10292, 231026, 102922323 should display in D2 as dropdown list, next A3 _ 36224, his Loan numbers 108024, 22222222, should display in D3 as dropdown list ........... ect)
My other issue is to prevent duplicate entrys in "3-2 Reimbursment" . in This purpos i want to captured Employee number (Colum A), Loan number (Column D) & Loan period (Colum G & H). If i want, Loan period can get one colume like this 2023/10/02 - 2024/02/05.
Specialy in this case, should seems as duplicate (red clour) same loan period or part of loan period Colum G & H) with Employee number (Colum A), Loan number (Column D)
As example, first time I type in "3-2 Reimbursment" sheet, A2 _ EMP No. 37224 B2 _ Name, D2 _ Loan number 10292, G2 _ Start date , 2022/10/02, H2 _ End date, 2023/12/10, I2 _ Amount , 2500.00,
second time i type A3 _ EMP No. 37224 B3 _ Name, D3 _ Loan number 10292, G3 _ Start date , 2021/12/02, H3 _ End date, 2023/12/10, I3 _ Amount 15,000.00 this should seems as dulicate. Because the first scenario is covered by the duration of the second senario. this is in the fist instance, some payment has been made up to 2023/10/01 to 2023/12/10 and in the second instance, the payment should be only up to 2023/12/02 to 2022/10/01.
Please help to solve this issues.
Tanks,
Priyantha
-
- Administrator
- Posts: 79560
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Getting Multiple Vlookup Values & Hightlight Duplicate row
See the attached workbook. The Loan Number column doesn't contain a formula anymore - you cannot have a drop-down list and a formula at the same time.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 116
- Joined: 10 Oct 2022, 02:52
Re: Getting Multiple Vlookup Values & Hightlight Duplicate row
Dear Hans,
It is working correctly, just i expected.Despite many searches for this, no answer was received. Thank You soo much, for helping me .
Br,
Priyantha