paramnayak
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
how to paste a formula in source in data validation
- Thread starter paramnayak
- Start date
paramnayak
Member
thanx bobhc for your prompt reply. But how is this done. I have tried but this is not happening
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
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
paramnayak
Member
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)
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)
Hmm. The formula overall looks okay, from syntax. I'm not sure about the
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).
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).
paramnayak
Member
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.
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!
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!
paramnayak
Member
in conditional formatting forumla text box it will work. But my question is data validation.
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.
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!
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!
paramnayak
Member
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.
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.
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.