Greetings all,
I was hoping someone could offer their input and/or guide me in the right direction for an experimental data validation macro which could (potentially) either automate or streamline data input for my engagement team.
In essence, I created a data validation scheme which seeks to create a foolproof method for extracting data from unique and static columns and aggregating that information for later input into Word documents, the goal being a reduction in time spent searching through the workbook.
The cell in which I've applied data validation contains source data from a single column that remains common throughout all spreadsheets. The information changes on a case-by-case basis, but said information will always be found in the same column.
Further information such as entity addresses, country location, bank account data, etc. are extracted using an INDEX:MATCH function in which the MATCH lookup value refers to the cell containing the data validation list, which is absolute-referenced. Thus far, the skeleton works perfectly, though my colleague and I have noticed one key obstacle precluding a engagement-wide rollout of such a value enhancing tool.
The problem revolves around unique values: for example, how can we ensure that all instances of, say, "Bank Account #", for any given entity selection in the data validation cell, are captured? After all, on larger cases, an entity may be transacting using multiple account numbers in multiple countries through multiple banks.
I've included a trimmed-down version of the model below. Any thoughts, comments, or workarounds you all can dream up would be sincerely appreciated. Cheers
.
A1 A2
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Entity Name[/TD]
[TD]XYZ Inc.[/TD]
[/TR]
</tbody>[/TABLE]
*Data Validation list is placed in cell A2 using source data from column A:A of a separate worksheet
[TABLE="class: grid, width: 550, align: center"]
<tbody>[TR]
[TD]Address:[/TD]
[TD]=INDEX(Entity_Address_Column,MATCH($A$2,Column w/All_Entity_Names,0))[/TD]
[/TR]
[TR]
[TD]Country Code:[/TD]
[TD]=INDEX(Entity_Country_Code_Column, MATCH($A$2,Column w/ All_Entity_Names,0))[/TD]
[/TR]
[TR]
[TD]Bank Name:[/TD]
[TD]=INDEX(Bank_Name_Column, MATCH($A$2,Column w/ All Entity Names,0))[/TD]
[/TR]
[TR]
[TD]Entity Account Number:[/TD]
[TD]""[/TD]
[/TR]
[TR]
[TD]Debit/Credit Bank Name:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Debit/Credit Bank Account Number:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Debit/Credit Bank Country Code[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I was hoping someone could offer their input and/or guide me in the right direction for an experimental data validation macro which could (potentially) either automate or streamline data input for my engagement team.
In essence, I created a data validation scheme which seeks to create a foolproof method for extracting data from unique and static columns and aggregating that information for later input into Word documents, the goal being a reduction in time spent searching through the workbook.
The cell in which I've applied data validation contains source data from a single column that remains common throughout all spreadsheets. The information changes on a case-by-case basis, but said information will always be found in the same column.
Further information such as entity addresses, country location, bank account data, etc. are extracted using an INDEX:MATCH function in which the MATCH lookup value refers to the cell containing the data validation list, which is absolute-referenced. Thus far, the skeleton works perfectly, though my colleague and I have noticed one key obstacle precluding a engagement-wide rollout of such a value enhancing tool.
The problem revolves around unique values: for example, how can we ensure that all instances of, say, "Bank Account #", for any given entity selection in the data validation cell, are captured? After all, on larger cases, an entity may be transacting using multiple account numbers in multiple countries through multiple banks.
I've included a trimmed-down version of the model below. Any thoughts, comments, or workarounds you all can dream up would be sincerely appreciated. Cheers
.A1 A2
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Entity Name[/TD]
[TD]XYZ Inc.[/TD]
[/TR]
</tbody>[/TABLE]
*Data Validation list is placed in cell A2 using source data from column A:A of a separate worksheet
[TABLE="class: grid, width: 550, align: center"]
<tbody>[TR]
[TD]Address:[/TD]
[TD]=INDEX(Entity_Address_Column,MATCH($A$2,Column w/All_Entity_Names,0))[/TD]
[/TR]
[TR]
[TD]Country Code:[/TD]
[TD]=INDEX(Entity_Country_Code_Column, MATCH($A$2,Column w/ All_Entity_Names,0))[/TD]
[/TR]
[TR]
[TD]Bank Name:[/TD]
[TD]=INDEX(Bank_Name_Column, MATCH($A$2,Column w/ All Entity Names,0))[/TD]
[/TR]
[TR]
[TD]Entity Account Number:[/TD]
[TD]""[/TD]
[/TR]
[TR]
[TD]Debit/Credit Bank Name:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Debit/Credit Bank Account Number:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Debit/Credit Bank Country Code[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Last edited:
