Ask Your Question

Automating a Cell Value Calculataion Based on a Range [closed]

asked 2020-06-12 08:07:44 +0200

Roux gravatar image

updated 2020-08-05 14:57:15 +0200

Alex Kemp gravatar image

I'm trying to find a way to do this, but I can't seem to find a sample anywhere yet. Maybe I just don't know the terms or the function to do it.

Ok, so I have this table

image description

I want the Stock Quantity to be automaticaly updated.
I might have a bad approach to it, but i could only think about making a "Counter" range.

I'm trying to achieve it by doing something like this :
Set a variable lets say x = 0.
Then for each cell in A column which have "orange" value in it, it will look up the value in its representative D cell.
Then it will just sum the value like this x = x + D cell value.

Thus if the D cell value is +3, then x = 0 + (+3) which results in 3.
After that it look for the next cell in A column with "orange" value in it and found one with D cell value of -1.
Then the calculation starts again and update the last one as x = 3 + (-1) which results in 2.

Is it possible to do that ?
What should i do to achieve that ?
Or maybe there's just another simple way around to achieve it that i couldn't think of.
Any kind help would be appreciated, thank you.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Roux
close date 2020-06-12 16:23:27.904487

2 Answers

Sort by » oldest newest most voted

answered 2020-06-12 13:09:17 +0200

Earnest Al gravatar image

How about in cell G2 enter =SUMIF(A2:D6,"apple",D2:D6), in G3 =SUMIF(A2:D6,"orange",D2:D6),and G4 =SUMIF(A2:D6,"pear",D2:D6)

For the Counter coloumn you could enter in cell D2 =IF(B2="in",C2,0-C2) and drag/copy down.

It might be worth creating a named range for A2:D6 so that updating the extent of the named range would update the formulae. I don't think that LO calc can do dynamic named ranges like Excel. Or just make it larger than needed to allow for future additions. Cheers, Al

edit flag offensive delete link more


Thank you Earnest for showing me the way ! This simple way solves my problem. God bless !

Roux gravatar imageRoux ( 2020-06-12 16:22:17 +0200 )edit

as mentioned in the other answer by @keme 'sumproduct', even in a simpler form like '=SUMPRODUCT(A$2:A$5=F2;D$2:D$5)' would be my choice? care which references to let relative and which need '$-absolutity' and you can copy one formula to a big range.

newbie-02 gravatar imagenewbie-02 ( 2020-07-02 10:55:48 +0200 )edit

and 'data - more filters - advanced filter' with options 'copy output to' and 'no duplicates' could be a nice help to produce the 'selection list' in 'big data' situations ...

newbie-02 gravatar imagenewbie-02 ( 2020-07-02 11:35:06 +0200 )edit

answered 2020-06-12 13:56:34 +0200

keme gravatar image

updated 2020-06-12 14:02:42 +0200

See the suggested solution attached.

As I see it, you don't really need the counter column. I included it anyway, but empty in the attached file. If you need that value, Earnest Al's suggested formula will do the job nicely.

Added data validation to "journaling" columns A and B. You can't enter an item name before it is in place in the summary table.

It may be better to use a pivot table, but that will not auto-update. Editing item names, inserting rows or copy/paste from other spreadsheet cells can easily mess up references, formulas and validation. and so should be avoided. Protection may improve the behavior, but spreadsheets are generally not very good at preserving data integrity. Databases are made for that.

edit flag offensive delete link more


Thank you very much for your reply keme ! This could be a great reference for me for later use. And yes, Earnest's formula is enough for me now, but yours is also a great learning sample for me.

Thank you so much !

Roux gravatar imageRoux ( 2020-06-12 16:19:58 +0200 )edit

Question Tools

1 follower


Asked: 2020-06-12 08:07:44 +0200

Seen: 93 times

Last updated: Jun 12 '20