Need help with a custom formula(s)

As long as you are using the same spelling in the Header Rows and Difficulty Level in both tables (it appears that you are) then the formula can handle hidden columns.

So, in troubleshooting, you might try to track down an error in entering the formula.

This is my formula (when copied from A2 to M2):

IFERROR(OFFSET('PRE-FILL DATA'::$A$1,XMATCH($C2,'PRE-FILL DATA'::$C),XMATCH(M$1,'PRE-FILL DATA'::$1:$1)−1),"")

Compare that to yours in your latest screenshot. You will see immediately that they are not the same. Hint: look within the second XMATCH. What happened to M$1?

To make it easier to compare and to understand what is going on, I recommend that you uncheck 'Use header names as labels' at Numbers > Settings (old name Preferences) in your menu.

Also to remove distractions it can help to click the unneeded optional parameter tokens and delete them (although you can leave them if you wish).

Also, when debugging formulas it can be helpful to click a warning triangle and see what the message says. Sometimes it just says syntax error. Sometimes it gives more specific clues as to what is wrong.

My formula does work. It should take just a minute or two to get it working in your table and fill/copy it into other cells.

SG

You can try using OFFSET, like this:

Enter this formula in cell A2 of the CLIENT DATA table:

=IFERROR(OFFSET('PRE-FILL DATA'::$A$1,XMATCH($C2,'PRE-FILL DATA'::$C),XMATCH(A$1,'PRE-FILL DATA'::$1:$1)−1),"")

The select cell A2, command-c to copy, select B2 and command-v to paste, select C2:E2, and command v to paste, and select M2:Z2 and command v to paste. Then in columns A,B,D,E, M through O fill the formula down their respective columns.

Because of the IFERROR The cells in a row will appear blank until you choose a DIFFICULT LEVEL for that row. When you enter a DIFFICULTY LEVEL for a row using the Pop-Up Menu in that row all the other relevant cells in that row will be filled in automatically with the values from the PRE-FILL DATA table.

(Use ; instead of , in the formula if your region uses , as the decimal separator. From the values in your screenshot it appears that you do not need to make that change, though).

SG

Somewhat easier to follow instructions:

Double-click cell A2 of CLIENT DATA and paste the following into the formula editor and hit return to accept.

IFERROR(OFFSET('PRE-FILL DATA'::$A$1,XMATCH($C2,'PRE-FILL DATA'::$C),XMATCH(A$1,'PRE-FILL DATA'::$1:$1)−1),"")

Fill or copy that right across the table and and then down so it is in all of the cells in the table.

Select the cells in the columns where you don't want the formula (where you won't be filling in data from PRE-FILL DATA) and hit the delete key.

SG

JohnGross wrote:

My goal is to use the PRE-FILL DATA table to set up "templates' for values to be replicated into the CLIENT DATA table.

That's what it does. Choose a DIFFICULTY LEVEL from the Pop-Up Menu in a row in CLIENT DATA and the other cells in that row are filled automatically from the CLIENT DATA table.

Where I wrote C2:E2 I should have writing D2:E2, because the Pop-Up Menu cells are in column C.

I've found it easiest when setting up the CLIENT DATA table to just fill-copy the formula from A2 across and down the entire table, then select the places I don't want it and hit delete. But you can do it the other way too, as I first described.

Either way, it should take only a minute or two to set up the table and get it working, as the formula is written so you can copy it right and down, or down and right.

SG

Glad to hear you got it working. Thanks for the green tick!

SG