Need a Dynamic vlookup using python
narenchunduri at gmail.com
narenchunduri at gmail.com
Sun Jul 12 11:03:41 EDT 2020
Sorry for not making it clear.
I tried below code
# import modules
from openpyxl import *
from openpyxl.styles import *
import webbrowser
import pandas
from openpyxl.worksheet.datavalidation import DataValidation
# Read all Excels into pandas dataframes
sowexcel = pandas.read_excel('Billing Roster - SOW.xlsx')
#Load the existing Resource Allocation Excel
wb = load_workbook('ACFC_Resource_Allocation.xlsx')
allocationsheet = wb.active
def load():
maxrow = allocationsheet.max_row
sow_list = sowexcel['SOW #'].tolist()
column_sow = ','.join(sow_list)
validator_sow = DataValidation(type='list', formula1='"{}"'.format(column_sow), allow_blank=True)
allocationsheet.add_data_validation(validator_sow)
validator_sow.add('D2:D%s' %maxrow)
# save the file
wb.save('ACFC_Resource_Allocation.xlsx')
wb.close()
# Driver code
if __name__ == "__main__":
load()
file_open = webbrowser.open('ACFC_Resource_Allocation.xlsx')
In Billing Roster - SOW.xlsx I have new column data one is named as SOW and other is named SOW Description (Match value for SOW).
And now when i open ACFC_Resource_Allocation.xlsx excel and for an example if select a value in D2 (SOW) cell from the dropdown i should get a matching value into E2 cell after the selection from dropdown.
I only have an idea than a vlookup from Excel like below should solve my case. Not sure how to achieve in python.
=VLOOKUP(D2,'[Billing Roster - SOW.xlsx]SOW List'!$A$1:$B$14,1,FALSE)
Please let me know if am not still clear.
More information about the Python-list
mailing list