• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation in a cell to only allow Array Formulas?

PP3321

PP3321

Active Member
Thanking you in advance for your helpful advice as always...

Is it possible to allow only certain type of Formula (i.e. Array Formula) in a cell?

I looked at Data Validation but I do not know how to write a formula,
which will allow only formulas that contain {}

screenshot.png
 
I don't believe it is possible using Data Validation alone
The {} of an array formula are not in the cell they are effectively a Custom Number format showing that the cell is an Array Formula just like 0.75 is displayed as 75%

You maybe able to write a UDF to check and return True/False
 
Update on the above

Yes, It can be done

But it needs a helper cell

First add a simple UDF to a Code Module in VBA
Code: 
Function Check_Array(Target As Range) As Boolean
  Check_Array = Target.HasArray
End Function

Assume you want to put an Array Function in G7
In a helper cell say G8 put =Check_Array(G7)

Then add a data validation to G7:
Use a Custom Data Validation with a Formula =G8

See the attached file

Enjoy
 

Attachments

  • CheckArrayForumula.xlsm
    15.5 KB · Views: 9
PP3321

Only Excel 2016 following example, I have helped

Using Custom Data Validation

=IF(MID(FÓRMULATEXTO(A1),1,1)="{",1,0)

In Portuguese, Brazil, formula FÓRMULATEXTO, do not know in English sorry

Decio
 

Attachments

  • Exemplo Decio.xlsx
    9.5 KB · Views: 6
Last edited:
A non-VBA and in Formula way.

Similar to Hui's in Post #.3, but using Define Name with Get.Cell function instead of UDF.

Go to Formulas | Define Name .

Name : CellHasArray

Refers to : =GET.CELL(49,INDIRECT("R[-1]C",FALSE))

Use it in G8, enter:

=CellHasArray

Then add a data validation to G7 >> Custom >> Formula : =G8

See attached file

Regards
Bosco
 

Attachments

  • CellHasArray.xlsm
    9.2 KB · Views: 8
Hey Bosco,

Could you please share the list of all Get. function usage or formulas or please share the url if any.

No, we can not find {=formula()}

PP3321 said:
Thanking you in advance for your helpful advice as always...

Is it possible to allow only certain type of Formula (i.e. Array Formula) in a cell?

I looked at Data Validation but I do not know how to write a formula,
which will allow only formulas that contain {}

View attachment 33804
Click to expand...
bosco_yip said:
A non-VBA and in Formula way.

Similar to Hui's in Post #.3, but using Define Name with Get.Cell function instead of UDF.

Go to Formulas | Define Name .

Name : CellHasArray

Refers to : =GET.CELL(49,INDIRECT("R[-1]C",FALSE))

Use it in G8, enter:

=CellHasArray

Then add a data validation to G7 >> Custom >> Formula : =G8

See attached file

Regards
Bosco
Click to expand...
 
xlstime said:
Hey Bosco,....
No, we can not find {=formula()}
Click to expand...

Get.Cell function unable to return : {=formula()}

Or.....try to change the define name formula to
=GET.CELL(41,INDIRECT("R[-1]C",FALSE))

You can obtain the Formula Content and return : =formula()

but exclude { }, because the { } array sign are not in the cell.

Maybe the { } array sign can be taken out in the new Excel 2016 FORMULATEXT function as stated in the above deciog's Post #.4 formula (but I cannot test it in my Excel 2007).

Regards
Bosco
 
Last edited:
You must log in or register to reply here.
Back
Top