Reaching Target Total

I have a spread sheet that pulls data from a database. Due to the way the database displays the data I can not figure out how to reach my total dollar amount.


Attached Is a Sample data set that closely resembles my data. Somehow someway I need the number in L8 to magicly appear. Becuase of the data that is pulled in I just cant figure out how to get it there.

Purchase Order Sample.xlsx

For PO 8.3 the total quantity is only 24 at a cost of $1472.64, and for PO39.7 the total quantity ordered is 48 at a cost of $588.48.

It took the vendor 4 different shipments to fullfil my order of 24 screws on PO8.3. If I add Column H where Column I = 8.3 then i get $1472.64.

For PO 39.7 I have only recevied 34 out of 48 washers. If I add column H where I=39.7 I only get $416.84, but my total dollars commited for those washers is $588.48.

$1472.64
+ $588.48
= 2061.12

This works great as far as returning the desired data, however, how can I make it Dynamic? I don't know from day to day how many rows i will have.

Originally Posted by bremen22

This works great as far as returning the desired data, however, how can I make it Dynamic? I don't know from day to day how many rows i will have.

Im Sorry, but I must retract that statement.

That formula causes my spread sheet to stop responding and it crases excel

right now 1864, but that number grows everyday

Originally Posted by BB1972

Okey-doke, well I've just sat through 2 Excel crashes.... perhaps some others can shed some light on why this formula is so resource-hungry? (Apart from the range, of course, but it's "only" 10,000 rows.)

Please Login or Register  to view this content.

This appears to be the one that's the culprit.

Any takers?


EDIT: I suppose I'm just thinking out loud here, but I guess the problem could be that Excel is keeping count of all the (10,000) entries in Column G, in order to count them?

@bremen: Could you do a cheap and cheerful copy from Column G, paste into an empty tab, and remove duplicates, to get your unique list to base calculations on?

Well, the data drives a few piviot tables and the "Total cost is one of the most important. If I can atuomate the process of copying, removing duplicates, and still have the totals in my pivit table I would be ok with it.

I do not see why the formula could not cope with any row. It is more likely that the data may been imported from another application (Like SAP systems), or downloaded from a site.

Yes the actual data is pulled in from an Oracle database, but my sample is exactly houw it will look.

Originally Posted by BB1972

@AB33 - good advice regarding pasting the data values.

@bremen22 - I have acted on AB's suggestion, and redone the workbook (attached) using the data values only, with a list being calculated in Column N for up to 500 distinct unique values. I don't know if you will have more or less than that - adjust the range to suit. It's a little slow with "undo" actions, but otherwise seems fairly stable, and hasn't caused Excel to crash on me yet, unlike yesterday. How does this work for you?

Thanks. I will give this a shot in m actual dataset. Hopefully it works with all of the otehr calculations going on. I am currently up to column AN of calculated data.......

I tried this out and it still causes my sheet to crash.

If I could get the count of unique lines i could calculate it that way

Originally Posted by bremen22

I tried this out and it still causes my sheet to crash.

If I could get the count of unique lines i could calculate it that way

I have been playing with this as i refuse to give up......

If I add the formaula to a column in my table it crashes.

If I add it to a column out side my table it works great, but it doesn't atuo populate for all rows in the sheet. If I just drag it down all the way to the bottom then the data repeats itself and it also causes excel to crash.