how can i make a cell unlimited

M

Moh

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

Gord Dibben

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
 
M

Moh

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
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
M

Moh

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
G

Gord Dibben

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
 
G

Gord Dibben

See the reply from Dave Peterson.

Use a VLOOKUP formula.

Dave's link shows you how.


Gord
 
J

JLatham

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

JLatham

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
 
J

JLatham

Found the problem .... the earlier link includes a space after html making it
a link that can't be followed.
 
D

Dave Peterson

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

JLatham

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>
 
Top