auto updating a quantity cell


I would need to auto update the numeric value in a quantity column, when I select the specific item in another sheet.

Sheet1 being the bill (product, quantity sold, price per unit, total)
Sheet2 having the products info (name, price, quantity)

In Sheet1 I would select a specific item in A1, and will digit the number of pieces in A2, in A3 I'll have the price shown from Sheet2, and in A4 I'll have A2*A3
Please see http:Q_23132180.html

Once I put the quantity sold (Sheet1-A2), I'd like quantity (Sheet2-B1) to be updated consequantly.

Thank you
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Need to understand a little bit more about what you are trying to accomplish.

What do you have a quantity on Sheet2? What is this reference used for to begin with?
What is the purpose of updating the quantity on Sheet 2?
Is the quantity on sheet two vary by name or is constant for all items?

kenesoAuthor Commented:
Sorry for the confusion (BTW I think I messed with the cell example as well).

Let me retry explaining it:

When making the bill, and I put the quantity (3 in the example) in Sheet1-B2, I want the value (100 in the example) in Sheet2-B2, to be updated to 97.

Of course if I put in the bill Item 88 from Sheet2-A89, it should be the Sheet2-B89 to be updated.

The products selection will be made thru this process:

If it may help, I could upload the file, please let me know.
Sheet1 (bill)
A1 Product           B1 Quantity          C1 unit-price          D1 total (B1*C1)
A2 Item 1            B2     3             C2    10.00            D2    30.00
Sheet2 (warehouse)
A1 Product           B1 Quantity          C1 unit-price
A2 Item 1            B2    100             C2   10.00
A3 Item 2            B3     50             C3   20.00
A4 ...

Open in new window

kenesoAuthor Commented:
Another related qyestion, if you'd like to take a look: http:Q_23148550.html
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

This is a little more tricky.  I will have to think about it.  

I may have to resort to a Macro.  Would that be acceptable to run a Macro after all items have been entered on the first tab?

It might help if you do upload the file.
kenesoAuthor Commented:
Here is the file

Can you please check in the "ricevuta", why the "item" prices don't show up?
I inserted them after the grouping test ( http:Q_23148550.html )

Translation of the main terms used as code snippet vvvv
Ricevuta = bill
     Prestazione = Service
     Quantità = quantity
     Costo unitario = unit price
     Importo = amount
Magazzino = warehouse
     Nome prodotto = product name
     Vendita = sale
     Descrizione = description
     Costo = cost
     Quantità = quantity
     Fornitore = supplier
     Categoria = category
Fornitori = suppliers
     Ragion soc. = company name
     Indirizzo = address
     Città = city
     Pr = area
     Cap = zip
Clienti = customers
Trattamenti = treatments

Open in new window

kenesoAuthor Commented:
Forgot the formula translation:

Ok download this and give it a try.  It now has a Macro that you can run to update your quantities.  Just make sure the data on your Magazzino tab is expanded when the Macro is run.

Thanks for the translation - that helped.
kenesoAuthor Commented:
Thank you.

I encountered a couple of problems:

The blank selection in Ricevuta-A13:A16 is not working anymore.

The macro gives this error:

"runtime basic error                                                                             <<<<< Translated
an exception happened                                                                       <<<<< Translated
type:                              <<<<< Original message
message: sequence element is not assignable by given value!"         <<<<< Original message

When displaying the message the macro would open up, with the following line highlighted

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())
Sorry on the blank line.  I missed setting that back in getting other things to work.

Try the attached file (Has some fixes in the Macro that should work better)

kenesoAuthor Commented:
Thanks again, but I get the same error.

Could it be a language problem?
If that could be it, can you guide me thru the code to see if we can figure it out?
Very well could be a language problem.  It must be having a problem with the search.  I used the default code generated by the OOo Macro.  I found some better code on the web and have updated with that.  Give it a try

Summary of Macro:
What the macro does is grab the values in the cells A13 to A16 and B13 to B16 in Ricevuta

Then it does a For Loop to Loop through the array created.
If there if value in the first cells (A13 . .) (code is If oData(intLoop,0) <> "" Then) then it will look if a quantity was entered (If oData(intLoop,1) <> "" Then).  If it finds a quantity it does a search of the Magazzino tab.  The new search returns a cell value.  Then do some calculations to find the Qty column.  Grab that value and subtract the value in the array.

If you want to troubleshoot the old code, Here is what you can try.

Comment out these lines of code (Just put an apostrophe ' or a REM in front of them)

args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false

args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255

args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280

Try the code again.  If you still get an error, the put in:

print args1(10).value

In front of
dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())

This will pop up a message box that should show the item code it is looking for.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenesoAuthor Commented:
Thank you, the last update did it.

400 > 500

Please take a look here, it is both because I'd like to know, and because I'd like to give you some extra points.
kenesoAuthor Commented:
>>Thank you, the last update did it.

Well, almost ... I just noticed a problem.

The updating is fine, the problem is with the "totale iva inclusa" in the "ricevuta", the "=SUM(D5:D16)"
on cell D17 returns an error: "N/D", which is due to the D13:D16 cells, 'cause when I put =SUM(D5:D12), it works fine.

Could you please look into it again, thanks.
kenesoAuthor Commented:
I opened this if you'd like to take a look

Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.