DPChristman
Board Regular
- Joined
- Sep 4, 2012
- Messages
- 177
- Office Version
- 365
- Platform
- Windows
I am currently trying to link data between a large data file and a fill in form that I have created.
The source data is coming from an excel file that is over 34 MB and more that 177,000 lines.
This data deals with inventory shrinkage.
There are 7 columns that I am accessing on the source data file:
What I need to do is RANK the top five items (either Class or Department) for each store, and have them auto-filled into the form I have created.
Example, if the person enters the store number (ex: 123), the section of the form for top 5 shrinkage departments would populate with the following[TABLE="width: 217"]
<tbody>[TR]
[TD="class: xl65, width: 180"][/TD]
[/TR]
</tbody>[/TABLE]
DEPT DEPT NAME UNITS DOLLARS
JLY JEWELRY 318 $41,000
HAC HOME ACC 300 $25,000
GLS GLASSWARE 248 $18,000
WCH WATCHES 168 $15,000
FRG FRAGRANCE 151 $13,400
Since the file with the source data will not be open at the time that the form is filled out, using a sumifs formula really won't work.
Currently, my field people are doing a very awkward copy and paste function, and I am looking to simplify the process.
Anyone have any ideas how to accomplish this?
Thanks,
Dan
The source data is coming from an excel file that is over 34 MB and more that 177,000 lines.
This data deals with inventory shrinkage.
There are 7 columns that I am accessing on the source data file:
- Store Number
- Dept
- Dept Name
- Class
- Class Name
- Shrink Units
- Shrink $
What I need to do is RANK the top five items (either Class or Department) for each store, and have them auto-filled into the form I have created.
Example, if the person enters the store number (ex: 123), the section of the form for top 5 shrinkage departments would populate with the following[TABLE="width: 217"]
<tbody>[TR]
[TD="class: xl65, width: 180"][/TD]
[/TR]
</tbody>[/TABLE]
DEPT DEPT NAME UNITS DOLLARS
JLY JEWELRY 318 $41,000
HAC HOME ACC 300 $25,000
GLS GLASSWARE 248 $18,000
WCH WATCHES 168 $15,000
FRG FRAGRANCE 151 $13,400
Since the file with the source data will not be open at the time that the form is filled out, using a sumifs formula really won't work.
Currently, my field people are doing a very awkward copy and paste function, and I am looking to simplify the process.
Anyone have any ideas how to accomplish this?
Thanks,
Dan