I have a populated table on one worksheet/tab1 with multiple rows and columns.
I want to create a new table on tab2 with same columns and one new column that asks Modify "Yes/No" question. If a user answers "Yes" for row 10, he will fill out that row by himself. If he answers "No" that row is auto populated with values from row (with same index#) from tab2, row includes multiple columns. Is that possible with Macro or Excel 365 in general?
Yes, but not in the manner I believe you are expecting.
What you'd do is to fill ALL the appropriate cells with formulas that reference the source data so that you have an entire match of the source data.
No column is needed for the user to enter Yes or No in. However, it could be useful if, perhaps, a supervisor were to look through the data in the secondary table and mark rows for replacement with hand-entered data.
So, say your data is on a sheet called "tab1". In the first column's cell for a row, perhaps column A, you'd have a basic =tab1!A1. That will collect the data from tab1's A1 cell and put it in cell A1 on tab2. You'd copy the formula into B1 and then edit the formula in B1 as follows. Once edited, you'd copy it to the right to fill all the needed columns. After the first row is set up, you'd copy all the used cells and then paste as far down as needed.
The formula in B1 would become:
=IF(ISFORMULA($A1),tab1!B1,"")
This tests tab2's cell A1 for the presence of a formula. If there IS a formula, it looks to tab1, cell B1, and takes that value for its value. If there is NOT a formula, it places a blank in the cell.
Why? Well, if a row does not need hand entry, there will be a formula in column A's cell and its result. All the cells to the right will see there is a formula and pull their own values from tab1.
However, if the user types over the formula in column A's cell, the formula is gone. All the cells to the right will instantly appear blank. That way the user knows to keep going and enter all the data. Even if the user skips a cell, it will still remain blank and will pass that blank on to any formula elsewhere that looks at it.
If there is a need for the Yes/No column other than just to enable this, say the supervisor thing, just make it column A and have the cells holding data formulas test for "Yes" in that column's cell in each row. If TRUE, then they show the blank, and if FALSE, they show the result of the =tab1!D233 (or whatever cell).
Also, once data on the secondary tab is static forever, it will never be changed, you can copy it and Paste|Special|Values to change it from the formulas into plain text just like an edited row. If desired.
