Apache OpenOffice Community Forum

So I have a spreadsheet that i get from my eCommerce platform and it sends my inventory , I sell items in singles,5packs and 25packs . I need to reset my inventory often when the singles or 5 packs get

low I am sent an email from my ecommerce warning me when my 5 packs or my singles get low.

right now what i have done is
1. I copied into "simple macro.ods" Columns/rows A1-F121 from my ecommerce.csv export (light yellow highlighting)
2. The output is somewhat correct as you can see in Columns/rows R1-T121 (brighter yellow highlight) compare the qty in colum "S" vs Column "B"
3. how it works is it takes all the qtys of single, 5 ,and 25's coming in from my ecommerce export and compiles it into H4 to be a total qty example 435 = I4*25+I3*5+I2
4. Then J4 Divides the total qty by 25 if the total qty larger than 76 IF(H4>=76;QUOTIENT(H4;25);"too small") This column isnt necesary it just so you can see numbers.
5. Then K4 gets the remainder after being divided by 25 IF(H4>=76;MOD(H4;25);"too small")
6. Then L4 Looks to see if the remainder is less than 10 it then decides to deduce either 2 or 3 from my 25 pack totals by doing IF(K4>10;QUOTIENT(H4; 25)-2;QUOTIENT(H4; 25)-3)
7. Then in M2 it looks at the remainder and says if it is K4<10 then leave it otherwise add 25 to it IF(K4>10;K4;K4+25)
8. Then N3 looks at H4 ato see if it is bigger than 76 if so it resets the 5 packs to the qty of 10 previously deduced from 25packs in step 6 by either 2 or 3 IF(H4>=76;10;"")
9. is where O2,O3,O4 simply take the data from O2=M2 O3=N3 O4=L4

So as you can see i am runing into problems when the qty is lower than 76

I think a different approach is needed when the total qty gets below 76 then i think we only need stock 5 packs and singles and 0 the 25packs something like
the Quotent and the Mod of Total QTY become divisible by 5 instead of 25 similar to what i have done with values over 75 and if the remainder is less than say 4 we add 10 too it by deducing 2 of the

5packs

I am by no means skilled but i know there is someone who can help me , maybe this needs to be writen in basicor VB or something any help or reccomendations please reply !!!

Thanks

If the current structure of your file is reasonably tidy, I guess this is best done through the database. (A spreadsheet can be registered as a data source with Base.) Sending you a PM.