I want to use the formulafill function within an execute statement to access an array, part of whose name is derived from the first character of a value in a database. For a record whose value in the field A3 begins with “4”, I want to access the array B4. In a simple example:
field A3 formulafill B4
I want to do this for multiple fields but I am having difficulty coding an execute statement which inserts the correct value of Bn into a statement for multiple fields. This doesn’t work:
If I understand your setup correctly, you do not want to build the array name based on the field name, but of the contents of the field. So I think you have to use something like
That’s precisely what I want to do. But this doesn’t work either - I get the same error message as from Jim’s solutions.
I have to run out at the moment but I’ll look at this again when I return. I woke up at 2 am thinking about using chevrons or the chevronquoted( function so I’ll check that out too.
And I may have inadvertently mislead people by my initial example. I don’t necessarily want to fill A3 with B4, I want to fill it with the array whose name is B followed by the first character of each record in A3. So I don’t want to add 1 to anything.
Just to clarify - if a value in A3 begins with 8, I want to replace that value with the contents of the variable B8. If it begins with 1, I want to replace that value with the contents of the variable B1.
The actual task I’m applying this to is far more complex than this but the problem remains the same - I want to generate a variable name from the first character of every value in the field A3.
You’ve resolved it but one trouble shooting technique that I’ve found useful with Execute is to put the formula result on the Clipboard so that I can look at it and see clearly what is being executed - or attempted. It has saved me a lot of pain and suffering.
I don’t see where execute is giving you anything here. I think you could just write this as
formulafill datavalue("B"+«»[1,1])
The reason Jim and Kurt’s suggestions gave you errors is that they were thinking B3 was a field instead of a variable. Fieldvalue( won’t return the value of a variable.
I suspect that this has to be an execute statement but I’m prepared to stand corrected - I can see that it might be possible but I’m not brave enough to try at this stage.
The execute formula has to be evaluated first, so that the command will know what code to execute. The names of the variables change from one record to the next, so a single calculation at the start isn’t going to be any help there. The execute formula does calculate the name of the current field, which remains constant, but that can be replaced by «».