Lookup Excel values to print values in second spreadsheet?

I will try to explain what I am looking for.

I use Excel to import data files every day. These files are always broken up into the same columns, but with updated daily values.
I want to know if Access can refresh this data, and use my formulas to insert specific values into a default "Order"

I have two spreadsheet files that I go between.

OrderForm1.xls
I have default spreadsheets, per category, that are blank order forms.

Ex:
An order form will have a cell with a SKU, and an empty cell beside it where I can write in how much of that SKU I need.
For sake of argument,
CELL C5 is '4450'
CELL D5 would be blank, and I need to enter an amount
CELL E5 would have an order quantity (ex. '10')
If I need to order 50 pieces of 4450, CELL D5 needs to have '5' entered into it. (5 units of 10 = 50 total pieces)
etc.. C6, D6, E6 may be different, but all relating to the SKU in column C, and the order quantity in column E

- My Order forms are all in excel format, and do not change. The data in the (C)SKU and (E)ORDER QTY columns remain the same. I only ever change the amount in the D column.

Report1.xls
These daily reports are what I refresh data into daily to produce different values.
In these,

COLUMN B always reflects a SKU
COLUMN Y is always the amount that I need to order of the particular SKU from that same ROW.
ex.
B2 is SKU '4450'
Y2 is '50'

B45 is '4450'
Y45 is '67'

B78 is '4450'
Y78 is '23'


Result
This is what I want to accomplish.
Everytime '4450' is found on Report1.xls , I want to take the Y value, and divide it by the value in E5 on OrderForm1.xls
and enter the result in D5 on OrderForm1.xls

My hope is to run a program that will enter these results into OrderForm1.xls

Three different sheets, since '4450' appeared 3 times in Report1.xls
Sheet 1 - '5' in cell D5 (50 needed, divided by 10[value in E5] = 5)
Sheet 2 - '7' in cell D5 (67 needed, divided by 10[value in E5] = 7 [rounded up to nearest number])
Sheet 3 - '3' in cell D5 (23 needed, divided by 10[value in E5] = 3 [rounded up to nearest number])

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

---

We only help on certain problems not to create a worksheet for you..
You should start first then if you encounter a problem on the worksheet it's the the time to ask a question to fellow members.

---

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Suggest you post a sample spreadsheet. I believe that what you want can be achieved in Access, but would like to analyze it to be sure.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Attached are your Report1 modified to a normalized state so that it can be linked to Access. This means no hidden columns, no empty rows, no merged cells.

I then imported a table similar to your OrderForm1 that has pertinent info.

I joined the two tables in Access on the SKU. Set the calculated field for the Units and then created an aggregate query to sum by location.

See attached folder. Because I did this in Access 2002, I changed your excel files to .xls files. If you use a later version of Access then you can set this up with the .xlsx files.

I think thread 6 explains exactly what I did in the attached file. I've modified the Queries to provide you with what I believe that you asked for in thread 8.

If you are unfamiliar with Access at this time, I urge you to get a book on Access development. It is a steep learning curve, but well worth the experience once you find out how to do the simple things you are asking. To start, here is an on line basic tutorial to get you started. http://www.accessmvp.com/strive4peace/