How to copy a cell (value) from one sheet to another but have it go into the row below each time the original is changed?

This iOS version has "forms", I believe, that will do what you want but the Mac version does not.

You could do it with an AppleScript but your user would have to use a keyboard shortcut to "submit" the data. They may forget and use Enter instead, which might mess up your data entry table (might add a new row) and won't submit the values to the other tables.

Here is a different idea. The first screenshot is how it would look when it is all set up. Second is how it is created, with a few entries already made. I did it all in one sheet but you can cut Table 2 and paste it into a different sheet later.

Both tables have a lot of rows. They should have more than you think you'll ever need.

Put a 1 in cell F1

Formula in Table 1::F2 =IF(E2<>TRUE, IF(ISNUMBER(F1),"show",""),ROW())

Fill down to complete the column

There's more than one formula that can do the same thing, maybe a better one is available.

Formula in Table 2:A =XLOOKUP(ROW(),Table 1::F,Table 1::A,"",0)

Formula in Table 2:B =XLOOKUP(ROW(),Table 1::F,Table 1::B,"",0)

All cells in the column have the same formula, except the header row that is text.

When doing the same for the other stocks, the column B formula needs to refer to the correct column in Table 1.

Set up the filter for Table 1 as shown ("1" is column F, named that way because the header has a "1" in it)

Hide column F of Table 1

After clicking "submit", there is no going back in Table 1 except by turning off the filter to access previous rows.

Alternate formulas for Table 2 that do the same thing more simply but require Table 1 and Table 2 to be the same length (or Table 1 to be longer) are:

A =IF(Table 1::E,Table 1::A,"")

B =IF(Table 1::E,Table 1::B,"")

I had a mix of things going on as I was putting this together. These are simpler and I am sure they are faster.

You could have Table 2 contain all the stocks, just like in Table 1, versus them being in separate tables. I wasn't sure what you had in mind and did just the one column.

If you are using the simple formulas for Table 2, here is an alternate formula for column F

cell F2 =IF(AND(E2=FALSE, E1<>FALSE),"show","")

You might think this would be the same as =IF(AND(E2=FALSE, E1=TRUE),"show","") but this version fails in the first row (row 2) when it is comparing the word "submit" in E1 to TRUE. "Submit"="TRUE" is false but "Submit"<>"FALSE" is true.

One other thing of note: if you get down to the last row, when you hit "submit" you won't get a fresh row, you will get no row. That's how you'll know when the table is full.