|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
how can i make a cell unlimited.
so that i can enter upto 3000 characters into it.
for e.g so i can put in a long formula)
thank you, i hope someone can reply fast.
--
please can you help... its urgent
|
|
You cannot enter more than 1024 characters in a formula.
No way around it.
What kind of formula would have 3000 characters?
Describe what you hope to achieve, other than stuffing 3000 characters in a
formula that is.
There may other methods or formulas that would apply.
Gord Dibben MS Excel MVP
On Sun, 24 Sep 2006 17:34:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >how can i make a cell unlimited.
>so that i can enter upto 3000 characters into it.
>for e.g so i can put in a long formula)
>
>thank you, i hope someone can reply fast.
|
|
ok i have 2 sheets
in sheet 2 i have a name list of 90 people
in sheet 1 i have 2 cells
the 1st cell is a dropdown list that has all the names from sheet 2
and in the 2nd cell (thiss cell i need unlimited) i have the lookup formula.
so what ever name is chosen in the 1st cell their telephone number will
appear in the 2nd cell.
is there an easier way around pls?
best regards
--
please can you help... its urgent
"Gord Dibben" wrote:
[Quoted Text] > You cannot enter more than 1024 characters in a formula.
>
> No way around it.
>
> What kind of formula would have 3000 characters?
>
> Describe what you hope to achieve, other than stuffing 3000 characters in a
> formula that is.
>
> There may other methods or formulas that would apply.
>
>
> Gord Dibben MS Excel MVP
>
>
> On Sun, 24 Sep 2006 17:34:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
>
> >how can i make a cell unlimited.
> >so that i can enter upto 3000 characters into it.
> >for e.g so i can put in a long formula)
> >
> >thank you, i hope someone can reply fast.
>
>
|
|
On Sun, 24 Sep 2006 17:34:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >how can i make a cell unlimited.
>so that i can enter upto 3000 characters into it.
>for e.g so i can put in a long formula)
>
>thank you, i hope someone can reply fast.
You cannot do that in a cell. But you could write a User Defined Function in
VBA that should be able to accomplish what you wish. Then you just put the UDF
in the cell.
--ron
|
|
gord dibben i'm still waiting for your reply...
sorry ron rosenfeld but i don't know much about excel but i can understand.
is it possible someone can do me a quick template.
for example it needs to have a dropdown list with names in there and once
the user selects a name a telephone number will appear in a cell.
which i can then edit
thank you
--
please can you help... its urgent
"Ron Rosenfeld" wrote:
[Quoted Text] > On Sun, 24 Sep 2006 17:34:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
>
> >how can i make a cell unlimited.
> >so that i can enter upto 3000 characters into it.
> >for e.g so i can put in a long formula)
> >
> >thank you, i hope someone can reply fast.
>
> You cannot do that in a cell. But you could write a User Defined Function in
> VBA that should be able to accomplish what you wish. Then you just put the UDF
> in the cell.
> --ron
>
|
|
p.s my e-mail address is: QM[ at ]HOTMAIL.CO.UK
thank you
|
|
=vlookup(a1,sheet2!a:b,2,false)
If you have a table with names in column A and numbers in column B of sheet2.
And A1 of the current sheet contains the name of a person to match.
Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
Moh wrote:
[Quoted Text] >
> ok i have 2 sheets
>
> in sheet 2 i have a name list of 90 people
> in sheet 1 i have 2 cells
> the 1st cell is a dropdown list that has all the names from sheet 2
> and in the 2nd cell (thiss cell i need unlimited) i have the lookup formula.
> so what ever name is chosen in the 1st cell their telephone number will
> appear in the 2nd cell.
>
> is there an easier way around pls?
>
> best regards
> --
> please can you help... its urgent
>
> "Gord Dibben" wrote:
>
> > You cannot enter more than 1024 characters in a formula.
> >
> > No way around it.
> >
> > What kind of formula would have 3000 characters?
> >
> > Describe what you hope to achieve, other than stuffing 3000 characters in a
> > formula that is.
> >
> > There may other methods or formulas that would apply.
> >
> >
> > Gord Dibben MS Excel MVP
> >
> >
> > On Sun, 24 Sep 2006 17:34:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
> >
> > >how can i make a cell unlimited.
> > >so that i can enter upto 3000 characters into it.
> > >for e.g so i can put in a long formula)
> > >
> > >thank you, i hope someone can reply fast.
> >
> >
--
Dave Peterson
|
|
On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >gord dibben i'm still waiting for your reply...
>
>sorry ron rosenfeld but i don't know much about excel but i can understand.
>
>is it possible someone can do me a quick template.
>for example it needs to have a dropdown list with names in there and once
>the user selects a name a telephone number will appear in a cell.
>
>which i can then edit
>
>thank you
>--
>please can you help... its urgent
Use Data Validation to set up a list; and then use VLOOKUP to get the phone
number.
The link that gord supplied, http://www.contextures.com/tiptech.html has
information under Data Validation for enabling the lists, and also information
on using VLOOKUP to get at the data.
However, if you want to possibly edit the phone number back on the original
source, by making changes where it comes up next to your cell with the dropdown
list, you will need to either write a VBA routine, or possibly you can use a
database program.
--ron
|
|
Ron
Actually it was Dave Peterson who supplied the link to Debra's VLOOKUP info.
I would have done the same thing if he had not beaten me to it.
I believe this is all Moh will need.
Gord
On Sun, 24 Sep 2006 22:02:31 -0400, Ron Rosenfeld <ronrosenfeld[ at ]nospam.org>
wrote:
[Quoted Text] >On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote: > >>gord dibben i'm still waiting for your reply... >> >>sorry ron rosenfeld but i don't know much about excel but i can understand. >> >>is it possible someone can do me a quick template. >>for example it needs to have a dropdown list with names in there and once >>the user selects a name a telephone number will appear in a cell. >> >>which i can then edit >> >>thank you >>-- >>please can you help... its urgent > >Use Data Validation to set up a list; and then use VLOOKUP to get the phone >number. > >The link that gord supplied, http://www.contextures.com/tiptech.html has >information under Data Validation for enabling the lists, and also information >on using VLOOKUP to get at the data. > >However, if you want to possibly edit the phone number back on the original >source, by making changes where it comes up next to your cell with the dropdown >list, you will need to either write a VBA routine, or possibly you can use a >database program. >--ron
|
|
See the reply from Dave Peterson.
Use a VLOOKUP formula.
Dave's link shows you how.
Gord
On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >gord dibben i'm still waiting for your reply...
>
>sorry ron rosenfeld but i don't know much about excel but i can understand.
>
>is it possible someone can do me a quick template.
>for example it needs to have a dropdown list with names in there and once
>the user selects a name a telephone number will appear in a cell.
>
>which i can then edit
>
>thank you
|
|
Moh, Gord and Dave has definitely pointed you in the right direction - a cell
on the first sheet with Data Validation set up to point to the list of names
on the second sheet as its data source, then a cell with a VLOOKUP() into the
whole table on the second sheet to find the phone number for the person whose
name you choose in the first cell.
Easiest way to set up the data validation is to refer to the list of names
on the 2nd sheet as a named range. I'm sending you a quick down-and-dirty
setup with graphics and detailed instructions on how that's done. Probably
much like what is on Dave's site, but perhaps having it in hand will help you
understand and set your workbook up the way you need to.
You won't be able to edit the phone number that appears - that would destroy
the VLOOKUP() formula in it. But there are ways to work around that using a
short VBA routine triggered by making a selection change in the cell with the
names listed in it.
"Moh" wrote:
[Quoted Text] > p.s my e-mail address is: QM[ at ]HOTMAIL.CO.UK
>
> thank you
|
|
Gord,
Dave's linkie no-workie for me ... it's odd. clicking on the link in his
post brings up a Page Not Found notice. But typing the address as displayed
directly into my browser brings it right up. Perhaps Moh couldn't get there
either??
JLatham
"Gord Dibben" wrote:
[Quoted Text] > See the reply from Dave Peterson.
>
> Use a VLOOKUP formula.
>
> Dave's link shows you how.
>
>
> Gord
>
> On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
>
> >gord dibben i'm still waiting for your reply...
> >
> >sorry ron rosenfeld but i don't know much about excel but i can understand.
> >
> >is it possible someone can do me a quick template.
> >for example it needs to have a dropdown list with names in there and once
> >the user selects a name a telephone number will appear in a cell.
> >
> >which i can then edit
> >
> >thank you
>
>
|
|
Found the problem .... the earlier link includes a space after html making it
a link that can't be followed.
"Gord Dibben" wrote:
[Quoted Text] > See the reply from Dave Peterson.
>
> Use a VLOOKUP formula.
>
> Dave's link shows you how.
>
>
> Gord
>
> On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
>
> >gord dibben i'm still waiting for your reply...
> >
> >sorry ron rosenfeld but i don't know much about excel but i can understand.
> >
> >is it possible someone can do me a quick template.
> >for example it needs to have a dropdown list with names in there and once
> >the user selects a name a telephone number will appear in a cell.
> >
> >which i can then edit
> >
> >thank you
>
>
|
|
Good eye<g>
Gord
On Mon, 25 Sep 2006 07:28:01 -0700, JLatham <HelpFrom [ at ]
Jlathamsite.com.(removethis)> wrote:
[Quoted Text] >Found the problem .... the earlier link includes a space after html making it
>a link that can't be followed.
>
>"Gord Dibben" wrote:
>
>> See the reply from Dave Peterson.
>>
>> Use a VLOOKUP formula.
>>
>> Dave's link shows you how.
>>
>>
>> Gord
>>
>> On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
>>
>> >gord dibben i'm still waiting for your reply...
>> >
>> >sorry ron rosenfeld but i don't know much about excel but i can understand.
>> >
>> >is it possible someone can do me a quick template.
>> >for example it needs to have a dropdown list with names in there and once
>> >the user selects a name a telephone number will appear in a cell.
>> >
>> >which i can then edit
>> >
>> >thank you
>>
>>
Gord Dibben MS Excel MVP
|
|
I could click on that link or copy|paste (with the trailing space) and it opened
for me in MSIE.
But glad you got it working.
JLatham wrote:
[Quoted Text] >
> Found the problem .... the earlier link includes a space after html making it
> a link that can't be followed.
>
> "Gord Dibben" wrote:
>
> > See the reply from Dave Peterson.
> >
> > Use a VLOOKUP formula.
> >
> > Dave's link shows you how.
> >
> >
> > Gord
> >
> > On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
> >
> > >gord dibben i'm still waiting for your reply...
> > >
> > >sorry ron rosenfeld but i don't know much about excel but i can understand.
> > >
> > >is it possible someone can do me a quick template.
> > >for example it needs to have a dropdown list with names in there and once
> > >the user selects a name a telephone number will appear in a cell.
> > >
> > >which i can then edit
> > >
> > >thank you
> >
> >
--
Dave Peterson
|
|
IE 6 or 7? I'm running IE 6:
Version: .0.2900.2180.xpsp_sp2_gdr.050301-1519
Only thing I can think of that might be odd is that I do have the VML
vulnerable .dll file disabled, but I don't see that as affecting this.
I click on the link - get to a Page Not Found page and if I highlight
address bar in IE 6, it shows the extra space (I presume it's just a space,
white-space anyhow). If I backup to the ell in .html and click go, then I'm
there.
I guess other oddity might be I'm running Google toolbar.
Just a weird situation - I'm not going to spend any time trying to do
anything about it. That's once that's happened in 2006, and 2006 is almost
over <g>
"Dave Peterson" wrote:
[Quoted Text] > I could click on that link or copy|paste (with the trailing space) and it opened
> for me in MSIE.
>
> But glad you got it working.
>
> JLatham wrote:
> >
> > Found the problem .... the earlier link includes a space after html making it
> > a link that can't be followed.
> >
> > "Gord Dibben" wrote:
> >
> > > See the reply from Dave Peterson.
> > >
> > > Use a VLOOKUP formula.
> > >
> > > Dave's link shows you how.
> > >
> > >
> > > Gord
> > >
> > > On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
> > >
> > > >gord dibben i'm still waiting for your reply...
> > > >
> > > >sorry ron rosenfeld but i don't know much about excel but i can understand.
> > > >
> > > >is it possible someone can do me a quick template.
> > > >for example it needs to have a dropdown list with names in there and once
> > > >the user selects a name a telephone number will appear in a cell.
> > > >
> > > >which i can then edit
> > > >
> > > >thank you
> > >
> > >
>
> --
>
> Dave Peterson
>
|
|
Save version. Same google toolbar.
I'm not worried about it either.
JLatham wrote:
[Quoted Text] >
> IE 6 or 7? I'm running IE 6:
> Version: .0.2900.2180.xpsp_sp2_gdr.050301-1519
> Only thing I can think of that might be odd is that I do have the VML
> vulnerable .dll file disabled, but I don't see that as affecting this.
> I click on the link - get to a Page Not Found page and if I highlight
> address bar in IE 6, it shows the extra space (I presume it's just a space,
> white-space anyhow). If I backup to the ell in .html and click go, then I'm
> there.
> I guess other oddity might be I'm running Google toolbar.
>
> Just a weird situation - I'm not going to spend any time trying to do
> anything about it. That's once that's happened in 2006, and 2006 is almost
> over <g>
>
> "Dave Peterson" wrote:
>
> > I could click on that link or copy|paste (with the trailing space) and it opened
> > for me in MSIE.
> >
> > But glad you got it working.
> >
> > JLatham wrote:
> > >
> > > Found the problem .... the earlier link includes a space after html making it
> > > a link that can't be followed.
> > >
> > > "Gord Dibben" wrote:
> > >
> > > > See the reply from Dave Peterson.
> > > >
> > > > Use a VLOOKUP formula.
> > > >
> > > > Dave's link shows you how.
> > > >
> > > >
> > > > Gord
> > > >
> > > > On Sun, 24 Sep 2006 18:03:01 -0700, Moh <Moh[ at ]discussions.microsoft.com> wrote:
> > > >
> > > > >gord dibben i'm still waiting for your reply...
> > > > >
> > > > >sorry ron rosenfeld but i don't know much about excel but i can understand.
> > > > >
> > > > >is it possible someone can do me a quick template.
> > > > >for example it needs to have a dropdown list with names in there and once
> > > > >the user selects a name a telephone number will appear in a cell.
> > > > >
> > > > >which i can then edit
> > > > >
> > > > >thank you
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
|
|
|