• 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.

how to paste a formula in source in data validation

kindly help me in this . I have a big formula to write in the source in data validation. Is it possible to write the formula in a cell and than copy paste it in the source in data validation
 

bobhc

Excel Ninja
Good day paramnayak


Short answer......yes, try it. Lots of excel users write formulas in cells and then copy paste
 

Luke M

Excel Ninja
Agree with BobHC. Now, depending on what the formula does exactly, it might produce some weird results if you try to confirm the formula in a cell, but you can certainly/build the formula there.
 

bobhc

Excel Ninja
paramnayak


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 :)
 

Luke M

Excel Ninja
If you're still having trouble, might be worthwhile to post your formula here so we can check it for syntax/structure errors.
 
this is the formula and it is working fine.

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)
 

Luke M

Excel Ninja
Hmm. The formula overall looks okay, from syntax. I'm not sure about the

Code:
&"col"
portion. Unless you're using named ranges, this won't make a good cell reference.


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).
 
as i have mentioned , the formula is working fine.

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.
 

SirJB7

Excel Rōnin
Hi, paramnayak!

Yes, you can. The only consideration is that if it's an array formula you should remember to enter it with Ctrl-Shift-Enter instead of just Enter in the cell, and then copying it normally into the CF formula textbox, as CF always treats formulas as array formulas if needed.

Regards!
 

bobhc

Excel Ninja
paramnayak


Are you entering the formula as custom, it would help if you uploaded the spread sheet
 

Luke M

Excel Ninja
paramnayak,

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.
 

SirJB7

Excel Rōnin
Hi, paramnayak!

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!
 
dear luke ,bobh7 and sir jb7,

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.
 

Filos

New Member
kindly help me in this . I have a big formula to write in the source in data validation. Is it possible to write the formula in a cell and than copy paste it in the source in data validation
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.
 

Filos

New Member
Filos
... just note
... previous reply was ... the 1st of Nov ... year 2012.
Thank you for your kind reply. I think though, for people who might be interested in the subject, even after so many years, just like me, some informations may still be useful.
 
Top