Hello,

I have a large organized 'master' workbook that contains certain
pricing information on items at various breaking points (based on
quantity). I also have a large collection of individual workbooks for
each part with detailed information on pricing at specified breaks.
Everytime the master workbook is updated, I would have to manually go
through and update each individual workbook. Due to the sheer number of
them, I am thankful it doesn't get updated terribly often!

I was looking to automate the task of updating. I've gone through a few
methods.

Firstly I considered using pure excel linking. Simply have the cell on
the individual worksheet reference the master worksheet at the right
spot. This could take a long time, and also if anything gets moved
around on the master worksheet, all the work needs to be done again.

Then I considered importing into Access and trying to use a report
output. This seemed somewhat possible except for two things a) I
couldn't dump into an existing file and the design view for reports
didn't seem to be the best thing to work with. b) the IT manager wants
everything eventually moved to SQL

So after more searching I discover an actual use for Excel's 'Get
External Data' function. You can use it to query another workbook or a
SQL table!

I am looking for advice as to where to go from here.

My master sheet is organized like so:


A B C D E F G
Part Start End Price Start End Price
A123 1 3999 0.508 4000 7999 0.427
A124 1 3999 0.508 4000 7999 0.427
....
A225 1 3999 0.518 4000 7999 0.429

When I start working with MS Query, it changes the column names to
Start, End, Price, Start1, End1, Price1, Start2, End2, Price2, etc.


My individual sheet is organized like so:


A B C
Parts 2500 5000
A123 to A224 0.508 0.427
A225 to A333 0.518 0.429

a)
I understand how to get it to filter for the name, is there a way to
have MS Query check whether 5000 (C1) is in between Start, End, if not,
check Start1, End1, if not, check Start2, End2, and if it finds a
match, it takes the price from Price(#) and outputs it?

b)
At the moment, I am using 'Get External Data' to reference the master
workbook, I do not believe the process would be terribly different if I
were to move the master workbook to a SQL table. Which one would be
more advisable to use? Or is it essentially one and the same?


Thanks for your time and consideration!