flower3954
Board Regular
- Joined
- May 5, 2012
- Messages
- 50
I currently import information into an excel tool from a pipeline report...via a Connection method. The pipeline report resides on a SharePoint drive.
Using a connection requires that I flash in the entire set of data, which then effects the size of the file. (too big) Once the data is brought in, I can use various lookups to leverage the data.
I would rather just do a VLOOKUP via Link directly to the referenced pipeline report. The problem is...the larger the range in the VLOOKUP, the larger the file size in the saved tool.
The SharePoint path is causing challenges to established methods of dynamically changing the range. Read many and experimented. (if statement does not work either)
How do I change the range dynamically in this formula?
=VLOOKUP($A$1,'https://portal.lb.hban.us/sites/homelending/Home_Lending_Job_Tool_References/Job_Tool_References/[touchpoint_app.xlsb]EXPORT'!$A$1:$AG$21000,6,FALSE)
A macro in a button will used to import the data, with the range expanding and then contracting during the code run.
Example - The range would need to transition from $A$1:$AG$1 to $A$1:$AG$21000 to $A$1:$AG$1
=VLOOKUP($A$1,'https://portal.lb.hban.us/sites/homelending/Home_Lending_Job_Tool_References/Job_Tool_References/[touchpoint_app.xlsb]EXPORT'!$A$1:$AG$1,6,FALSE)
to
=VLOOKUP($A$1,'https://portal.lb.hban.us/sites/homelending/Home_Lending_Job_Tool_References/Job_Tool_References/[touchpoint_app.xlsb]EXPORT'!$A$1:$AG$21000,6,FALSE)
to
=VLOOKUP($A$1,'https://portal.lb.hban.us/sites/homelending/Home_Lending_Job_Tool_References/Job_Tool_References/[touchpoint_app.xlsb]EXPORT'!$A$1:$AG$1,6,FALSE)
I'd appreciate any help...
Using a connection requires that I flash in the entire set of data, which then effects the size of the file. (too big) Once the data is brought in, I can use various lookups to leverage the data.
I would rather just do a VLOOKUP via Link directly to the referenced pipeline report. The problem is...the larger the range in the VLOOKUP, the larger the file size in the saved tool.
The SharePoint path is causing challenges to established methods of dynamically changing the range. Read many and experimented. (if statement does not work either)
How do I change the range dynamically in this formula?
=VLOOKUP($A$1,'https://portal.lb.hban.us/sites/homelending/Home_Lending_Job_Tool_References/Job_Tool_References/[touchpoint_app.xlsb]EXPORT'!$A$1:$AG$21000,6,FALSE)
A macro in a button will used to import the data, with the range expanding and then contracting during the code run.
Example - The range would need to transition from $A$1:$AG$1 to $A$1:$AG$21000 to $A$1:$AG$1
=VLOOKUP($A$1,'https://portal.lb.hban.us/sites/homelending/Home_Lending_Job_Tool_References/Job_Tool_References/[touchpoint_app.xlsb]EXPORT'!$A$1:$AG$1,6,FALSE)
to
=VLOOKUP($A$1,'https://portal.lb.hban.us/sites/homelending/Home_Lending_Job_Tool_References/Job_Tool_References/[touchpoint_app.xlsb]EXPORT'!$A$1:$AG$21000,6,FALSE)
to
=VLOOKUP($A$1,'https://portal.lb.hban.us/sites/homelending/Home_Lending_Job_Tool_References/Job_Tool_References/[touchpoint_app.xlsb]EXPORT'!$A$1:$AG$1,6,FALSE)
I'd appreciate any help...
