# Thread: Data Consolidation From One Worksheet To another (2000)

1. ## Data Consolidation From One Worksheet To another (2000)

Greetings!

My problem: On a daily basis, I record several (3) different prices for three different products I normally purchase. On some days, I may purchase more than one (1) "package" of each product at different prices (for example, on day 1 I may buy 300 units of product "A" @ \$3.00, 400 of "A" @ \$3.25, and 400 of "A" @ \$3.50, for a weighted price of the total (1100 units of "A") of \$3.182). In order to compute that day's weighted average price per product line, I have to enter a "long-hand" calculation.

I would like a simple way of recording this data only once (each day) on one worksheet (and have thet worksheet contain all the historical data-a database of sorts!), and letting a "program" then compute a single weighted average cost per product per day on another worksheet.

Stated another way, if I buy 3 packages product "A" on day 1 at three different prices, I would like to record the "raw" data on worksheet 1 (i. e., the quantity of each package, and the respective price per unit of each such package), and have the program built into my file that will compute the weighted average price of that day's purchase of product "A". (Yes, it is possible that I may buy multiple packages of each of the three products on each day all a t different prices!) In addition, I would like to keep running weighted averages for both month-to-date and year-to date purchases.

Any help?

Thanks,

Jeff

2. ## Re: Data Consolidation From One Worksheet To another (2000)

See if the attached works for you. I did not prove the month and year calcs, if you wouldn't mind checking them.

3. ## Re: Data Consolidation From One Worksheet To another (2000)

Thanks, John, I'll check it.

BTW, can you explain what you did-it looks like you accomplished what I want with arrays, and I am totally unfamiliar with working with them!

Thanks again,

Jeff

4. ## Re: Data Consolidation From One Worksheet To another (2000)

John,

Please see attached, wherein I added a worksheet that presents the data in a different format. If at all possible, I would like to keep it in this format.

Ideally, I would like the "program" to prompt me every time I want to add to my inventory by asking the following questions:

1. Product A, B, or C or End Daily Update?

2. Date (Once entered, this would not prompt again until "next" update-i. e., until the current "session" had ended and another begun)

2. Quantity

3. Price

4. If "End Daily Update", then prompt "Review Daily Data"? If yes, then it would automatically go to the "Purchases (Revised)" worksheet

I hope this isn't too complicated.

5. ## Re: Data Consolidation From One Worksheet To another (2000)

Jeff, your revised layout shows only one month and has totals for the month. (Are you sure you wouldn't prefer my original layout? Nudge, nudge, wink, wink, hint, hint?) In your long term layout should I assume that you want to do without monthly totals and you will continue to fill the WS down through the year. Are you sure you'll exceed one years data per WS?

6. ## Re: Data Consolidation From One Worksheet To another (2000)

Jeff, see if this gets you there. I added month and a little bit of another years' data to test the layout. Had to delete the old sheets due to WB size. Also, I
1. used a custom number format to hide zeroes in the Total Cost column, as "" messes up the To-Date Array Formulas
2. down-filled all the dates so there are no date gaps; alternatively you could have a hidden column which contains =max(\$A\$3:\$A3) in row 3, and is copied down; then formulas would need to refer to the hidden column instead of column A.

7. ## Re: Data Consolidation From One Worksheet To another (2000)

John,
I will check it out tonight-thanks a ton. BTW, can you help me out on how an array works, and how they work in your month-to-date and year-to-date average price columns?
Thanks,
Jeff

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•