asked on

auto updating a quantity cell

Hi,

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

ltlbearand3

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?

-Bear

keneso

ASKER

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:
http:Q_23132180.html

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

keneso

ASKER

Another related qyestion, if you'd like to take a look: http:Q_23148550.html

ltlbearand3

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.

keneso

ASKER

Here is the file
http://www.internetetc.it/ee_stuff/keneso/barbara.html

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

keneso

ASKER

Forgot the formula translation:

SE = IF
CERCA.VERT = VLOOKUP

ltlbearand3

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.

https://filedb.experts-exchange.com/incoming/ee-stuff/6782-manager_01.rev.ods.zip

Thanks for the translation - that helped.

keneso

ASKER

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: com.sun.star.lang.IllegalArgumentException <<<<< 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())

ltlbearand3

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)

https://filedb.experts-exchange.com/incoming/ee-stuff/6789-manager_01.rev.ods.zip

-Bear

keneso

ASKER

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?

ASKER CERTIFIED SOLUTION

ltlbearand3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keneso

ASKER

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.
http:Q_23156854.html

keneso

ASKER

>>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.

keneso

ASKER

I opened this if you'd like to take a look
http:Q_23171602.html

Thank you.