- Thread starter paramnayak
- Start date

thanx bobhc for your prompt reply. But how is this done. I have tried but this is not happening

if you comment out the formula it will not work in the cell you wrote it, so you can just copy the formula and not the back tick. If you have formulas that work your way save them in a work sheet for future use with a comment to say what they do, memory fails with the passing of time

since the formula is big and the space in source (in data validation) is small , i just want to know is it possible to copy paste this formula in the source if i write it in a cell

OFFSET(INDIRECT(SUBSTITUTE(A2," ","_")&SUBSTITUTE(B2," ","_")),1,0,COUNTA(INDIRECT(SUBSTITUTE(A2," ","_")&SUBSTITUTE(B2," ","_")&"col"))-1,1)

Code:

`&"col"`

Speaking of named ranges, another idea would be to put your large formula in the Named Range Manager and give it a Name. Then in the Data Validation you can simply write:

=MyName

Advantage is that it's easier to edit your formula later, you can use the Name Manager to make sure the correct range is being calculated by the formula, and you can use the MyName on whatever sheet you want (Data Validation tries to tell you that you can't reference other worksheets).

my only query is --- is it possible if i can write such big formula in cell and than copy paste in source in data validation.

Yes, you can. The only consideration is that if it's an array formula you should remember to enter it with

Regards!

in conditional formatting forumla text box it will work. But my question is data validation.

Do note that when asking for help, giving more information is preferred to less. So far, your posts have been very basic, asking only the simple question of can you copy from a cell to Data Validation? Basic answer: yes.

So far, in response to our answers, you've only said that "this is not happening". What does this mean? You can't copy formula? You can't paste formula? An error box appears? Wrong data appears in drop down?

Along with that, what settings are you using in Data Validation?

Additionally, we're posted several ideas to debug and make sure that thinks are working correctly, to which you've given a vague "it is working fine". This statement is not very helpful as it doesn't tell us what you tried, or how you know it's "fine".

I am not trying to be mean/attacking in this post, but instead trying to point out ways that you can help us, help you better.

Sorry, I misread your question. For DV the answer'd be the same: if it's a valid formula which calculates proper values it should work.

But it'd be better for all of us who're trying to help you if you upload a sample file, with manual entries or commented formulas if required for clearness, so as to explain in detail what do you want. Refer to 2nd green sticky post at this forums main page for guidelines.

Regards!

sorry for troubling you all. actually i have got what i wanted.

What i was doing was ----trying to copy paste the cell in which the formula was there instead of copying the formula and pasting.

i thank you all for your help.

Keep in mind that, if you want to introduce a formula in the field “formula” of the Data Validation window, the maximum number of characters is 255 (perhaps 256) including the equal sign.

Astuce:

I wαs trying to introduce such a long Data Validation formula (with references to cells of the same line) to a cell of a three-digit line of my sheet, but it wasn’t possible because it exceeded the 255 characters limit. Then I introduced the formula in a field of an one-digit line (lines 1 to 9), and I copied it to another cell of an three-digit line and it worked.

The same happens in the Conditional Formatting formula field.