You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
i’m not very savvy with numbers and trying to build something to use at work.
so here’s my situation… Table 1 f1 drop down menu will be “duplicate” or “individual”.
if I select “duplicate” I want table 3 a1-a10 to automatically duplicate what’s in table 2 a1-a10.
if I select “individual” I want table 3 a1-a10 to allow me to just enter the desired values. But still need to keep formula if I switch it to duplicate it will still work.
is this possible?
Technically it's not possible because a cell can only be empty, or contain a plain value or a formula (of which you see the result as a value). Entering anything erases the previous content.
But all is not lost because you can fake it to give the illusion that it works.
The trick is to create an overlay for Table 3. Here it is shown beside Table 3 but at the end, it will be put over it.
As you can see, the formula for Table 3::A1 is
=IF(Table 1::$F$1="Duplicate",Table 2::A1,Table 3 overlay::A1)
Copy it down to the other cells.
So when Duplicate is selected, Table 3 is filled with the content of Table 2.
But when Individual is selected, Table 3 is filled with the content of the overlay.
Now you set the Opacity of the Text color of the overlay to 0 to make it disappear. You can also set the borders to opacity 0; do not change their width though as the tables will not align anymore.
You hide the title of the overlay table and you move the overlay over Table 3. The illusion is perfect. When you select Individual, you type in the overlay but what you see is actually what is in Table 3.
Make sure that the overlay is in front of Table 3. If you created it by copying Table 3 it will be in front by default, but you can arrange it afterwards with the Arrange panel.
Only place the overlay over Table 3 when all formulas have been entered everywhere because you may need to select cells of Table 3 to build them. The overlay doesn't need to be as big as the table it is covering, it can only be A1:A10 if it fits your purpose.
Technically it's not possible because a cell can only be empty, or contain a plain value or a formula (of which you see the result as a value). Entering anything erases the previous content.
But all is not lost because you can fake it to give the illusion that it works.
The trick is to create an overlay for Table 3. Here it is shown beside Table 3 but at the end, it will be put over it.
As you can see, the formula for Table 3::A1 is
=IF(Table 1::$F$1="Duplicate",Table 2::A1,Table 3 overlay::A1)
Copy it down to the other cells.
So when Duplicate is selected, Table 3 is filled with the content of Table 2.
But when Individual is selected, Table 3 is filled with the content of the overlay.
Now you set the Opacity of the Text color of the overlay to 0 to make it disappear. You can also set the borders to opacity 0; do not change their width though as the tables will not align anymore.
You hide the title of the overlay table and you move the overlay over Table 3. The illusion is perfect. When you select Individual, you type in the overlay but what you see is actually what is in Table 3.
Make sure that the overlay is in front of Table 3. If you created it by copying Table 3 it will be in front by default, but you can arrange it afterwards with the Arrange panel.
Only place the overlay over Table 3 when all formulas have been entered everywhere because you may need to select cells of Table 3 to build them. The overlay doesn't need to be as big as the table it is covering, it can only be A1:A10 if it fits your purpose.
Thank you Ian
A variation of the concept doesn't use the master control and each cell can be overridden individually.
If the cell of the overlay is empty, use the value in Table 2, otherwise, use the value of the overlay. Set conditional formatting (cell not empty) in the overlay to emphasize that the basic value has been overridden. To return to Table 2's value, delete the content of the overlay's cell.
In Table 3::A1
=IF(ISBLANK(Table 3 overlay::A1),Table 2::A1,Table 3 overlay::A1)
Overlay above the "real" table. Setting text opacity to 0 in the overlay is not necessary since the color hides the text below, but it's still useful for the borders.
I think it would be helpful for everyone if you shared the larger picture of what you are trying to do. Will you describe what you need without how to do it? Sometimes why you want something is also helpful.
As Recyleur indicates... a cell cannot contain a formula and take input from a user.
If you have a mock up of what you are trying to do that would be helpful to see.
Excellent Recycleur!
Regards,
Ian.
Thanks for this.
Automatically duplicate a table when needed?
