Add a cell with itself

English
#1

Hello,

Is it possible for a cell to add its old values with a new one?

For example, I have set cell A1 to automatically show the value of B1.

I now want Cell A1 to add whatever value it has with a new value, the value of C1, not replace B1 with C1, but add both the numbers.

Subsequently A1 should then add whatever new number it has now with the value of D1, and so on and so forth.

Thank you.

#2

Linux script storing a number in a text file and adding entered values. Default value is 1 when you just hit the Enter key. No office suite required.

#!/bin/bash
COUNTFILE=~/counter.txt
COUNTVAR=0

function finish {
    echo $COUNTVAR > $COUNTFILE
    echo -e "\nCaught EXIT and saved $COUNTVAR to $COUNTFILE"
}

trap finish EXIT

if [ -f $COUNTFILE ]; then COUNTVAR=`cat  $COUNTFILE`; fi
while true; do
    echo -e "Add integer to $COUNTVAR [default 1]: \c"
    read NUM
    if [ -z $NUM ]; then NUM=1; fi
    let COUNTVAR+=$NUM
done
#3

Thank you for your reply.

I do need Libre Calc because the cell in question takes data linked to other sheets, and it needs to conform to a particular template as laid out by my organization and I do not use Linux.

Is there a solution within Calc?

#4

IMHO it is simpler to have the result cell in A2 with formula =SUM(A1:Z1). And if there is nothing else in the row, you can even use a “full row reference” as =SUM(1:1).

You can also decorate A2 (border, background, …) so that attention is drawn on A2 instead of A1. You can even hide row 1 so that nobody is tempted to tamper with the values (if the values come from other sheets).

#5

I see, but the value of, say B2 is different and is not supposed to be in A1.

Take for example, I have a sheet which I work om daily to enter the product details.

In that Sheet, A1:A3 is product name, A4 is amounts sold, and A5 is total revenue.

I also have a monthly sheet that gets updated accordingly. If I enter, say A4 as 20 in the daily sheet, then the monthly sheet gets updated as well. The problem is that its a monthly sheet, it is supposed to total articles sold those month.

The current method is to either calculate it manually (which will take a long time because there are hundreds of products), or create 30 spread sheets and then sum it all up in the monthly sheet.

If there was a way where, If I enter 20 in A4 of the daily sheet, then the monthly sheet would show 20, but if I then enter 30 in the same A4 after deleting the previous 20, the monthly sheet would add that 30 to the previous 20, not replace it, resulting in a 50.

This would have fixed all my problems, it would have saved me from creating 30 daily sheets, 12 monthly sheets, 4 quarterly sheets and one Yearly sheet.

#6

The moment you entered a new number, a spreadsheet cell does no longer know the old one. Same thing if a cell is updated in a different way.
Playing tricks to circumvent this is possible to some extent, but is complicated and leads to unmaintainable sheets: What if you inadvertently entered something wrong? How to correct the mistake? What “previous value” should any cell now “have in mind”?
Anyway, daily sheets, monthly sheets and everything of the kind isn’t serious data keeping.
Sales and deliveries are sequential.
Every transaction has (say) a stock ID, an amount, a date, a store, …
Then comes the next transaction of the same structure.
Current “units in stock”, and many dependent values can then be retrieved by a query inspecting the data.
Databases can handle sequential transactions. Spreadsheets can’t - at least not reliably.

#7

So… is there a way or do I have to make all those sheets?

#8

According to your description, you need a business management application. You can design something yourself with Base, the LO companion application for database access.
Basically you record your sales and you build queries to sum up in various ways.

To some extent, an application with lower ambitions like GNUCash can keep track of your sales and display various reports. It is a free accounting app.

EDIT: I added the link to GnuCash site. There is a Windows version. Note that the certificate has recently expired and you need to force access to the site on most browsers.

#9

I see, can it run on windows and export to pdf?

#10

I tried that app but it has a steep learning curve and I am unable to change the layout.

I think I have a solution to my problem, instead of trying to add multiple mumbers within the same cell, I will calculate all of those numbers outside a printable area, and then fill the monthly sheet with their sum.

Can you tell me if I can setup IF fumction for a range such as, cell B1 will only fill up if cell A1 is filled, cell C1 will only fill up if cell B1 is filled, so on and so forth and once filled, they will be permenant unless I change the values manually? So for example, if I link D1 with A1, then whenever I enter a number in D1 will automatically show up in A1, but it will not go away if I then deleted that number in D1, unless I manually delete it from A1.

Thank you.

#11

Try my counter.odb

#12

Once again you’re trying to abuse a spreadsheet and use it like a DB. You want to have a transient entry box and a linked permanent storage box. This is not possible with formulas.

It might be possible with macros but this implies big complications for something which is inherently DB-oriented. In addition, I’ve never written a LO macro yet because they obscure the logic of the workflow.

There is a steep learning curve with GnuCas, yes, but too with Calc macros and Base. But I definitely think that your goal is more easily reached with Base.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.