Show defined names while entering formula

[Guest]

I have a bunch of names defined on my worksheet. When entering a formula,
I'd like to be able to see a list of the defined names, and select a name
from the dropdown list. For example, when entering SUM(Sales, Commissions),
I'd like to be able to select Sales and Commissions from a dropdown list,
rather than having to remember the names and type them exactly.

Maybe I'm overlooking something obvious, but I can't figure out how to do
this.

Thanks for any suggestions.

 

[Bob Phillips]

When entering the formula, Hit F5, and you get a list of names there.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave Peterson]

F3 works, too.

When entering the formula, Hit F5, and you get a list of names there.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks for the suggestion. It helps, but doesn't solve the problem. It does
show the names. However, when I select a name from the list and hit OK, it
performs a "go to". When I then hit Enter, what Excel puts in the function
is not the name itself (such as Sales), but rather the reference the name
points to (such as Sheet1!$C:$C).

What I want to end up with is a formula that includes the name itself.

(Even if the reference in the formula captures the functionality that I
need, I'd prefer to see the name in the formula because of its
self-documenting aspect. When working with a complicated worksheet, it can
become tedious and confusing to keep track of cell references such as
Sheet1!$C:$C, if there are a lot of them.)

Thanks.

 

[Bob Phillips]

It works exactly as you want for m2, I hit F5, select a name, hit OK, and
whilst it selects that range, it also inserts it into the formula).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Gord Dibben]

Eric

I cannot replicate your problem.

I start entering a formula then hit F5 or F3, select a name and OK.

The name, not the "refers to" cells appear in my formula.

Are the names local or global?

Don't know if that should make a difference though.


Gord Dibben MS Excel MVP

 

[Guest]

I've figured out the problem. It's odd an unexpected. It has to do with
merged cells, plus a reference to another sheet within the same file.

I have to explain by example. I've got a merged cell (in which I have a
descriptive label) consisting of B1:B3. I then define a name Sales by
selecting Col 2 (by clicking on the "2" at the top of the column). This
defines a name Sales as $2:$2.

But then F5 (Goto) operates oddly: I hit F5 and select Sales, and Excel
selects not 2:2, but 1:3! I understand that it's confused by the merged
cell, but I would hope that it could figure it out and select 2:2 only.
After all, it was able to select and highlight 2:2 with no problem when I
clicked on "2" at the top of the column as I defined the name.

Even so, when entering a formula, both F5 and F3 work as expected.

The problem occurs only when I'm in another tab in the same file. I enter
"=SUM(" (without the quotes), then F5, then select Sales from the list, then
OK. Excel selects Sheet1!1:3 instead of 2:2, as I've just described above.
I then hit Enter (or OK - both have the same problem). What I hope to see
in the cell is =SUM(Sales), but what I see instead is =SUM(Sheet1!1:3).

In describing my original problem in my earlier post, I left out the fact
that I was going across sheets, because it didn't seem important. (And it
shouldn't be, should it?)

F3 seems to work as expected and desired in every case. I end up with
=SUM(Sales), which is what I want.

Thanks to you guys for your help!

 

[Guest]

I have to apologize to anyone who bothered to read this all the way through.
I mixed up rows and columns in describing the example.

I was correct in my analysis of the problem, but I did describe it
incorrectly. If anyone cares, here's the corrected explanation

---- BEGINNING OF CORRECTED EXPLANATION ----

I've figured out the problem. It's odd an unexpected. It has to do with
merged cells, plus a reference to another sheet within the same file.

I have to explain by example. I've got a merged cell (in which I have a
descriptive label) consisting of A2:C2. I then define a name Sales by
selecting Col B (by clicking on the "B" at the top of the column). This
defines a name Sales as $B:$B.

But then F5 (Goto) operates oddly: I hit F5 and select Sales, and Excel
selects not B:B, but A:C! I understand that it's confused by the merged
cell, but I would hope that it could figure it out and select B:B only.
After all, it was able to select and highlight B:B with no problem when I
clicked on "B" at the top of the column as I defined the name.

Even so, when entering a formula, both F5 and F3 work as expected.

The problem occurs only when I'm in another tab in the same file. I enter
"=SUM(" (without the quotes), then F5, then select Sales from the list, then
OK. Excel selects Sheet1!A:C instead of B:B, as I've just described above.
I then hit Enter (or OK - both have the same problem). What I hope to see
in the cell is =SUM(Sales), but what I see instead is =SUM(Sheet1!A:C).

In describing my original problem in my earlier post, I left out the fact
that I was going across sheets, because it didn't seem important. (And it
shouldn't be, should it?)

F3 seems to work as expected and desired in every case. I end up with
=SUM(Sales), which is what I want.

Thanks to you guys for your help!

--------- END OF CORRECTED EXPLANATION ---

 

[Gord Dibben]

Eric

Instead of using merged cells B1:B3, use "center across selection".

Select B1 and enter your label.

Select B1:B3 and Format>Cells>Alignment>Horizontal>Center across selection.

Get rid of any merged cells.

They cause no end of problems with sorting, copying, filtering, pasting and a
bunch of others I can't think of at the moment.

And maybe a new one.............as you have described.

See other comments in-line.

I've figured out the problem. It's odd an unexpected. It has to do with
merged cells, plus a reference to another sheet within the same file.

I have to explain by example. I've got a merged cell (in which I have a
descriptive label) consisting of B1:B3. I then define a name Sales by
selecting Col 2 (by clicking on the "2" at the top of the column). This
defines a name Sales as $2:$2.

Why have you got a 2 at the top of the column?

Are you using R1C1 reference style?

In that case selecting the 2 should give you =Sheetname!C2

If you are not using the R1C1 notation then clicking on row 2 will give you
=Sheetname!$2:$2


Gord

But then F5 (Goto) operates oddly: I hit F5 and select Sales, and Excel
selects not 2:2, but 1:3! I understand that it's confused by the merged
cell, but I would hope that it could figure it out and select 2:2 only.
After all, it was able to select and highlight 2:2 with no problem when I
clicked on "2" at the top of the column as I defined the name.

Even so, when entering a formula, both F5 and F3 work as expected.

The problem occurs only when I'm in another tab in the same file. I enter
"=SUM(" (without the quotes), then F5, then select Sales from the list, then
OK. Excel selects Sheet1!1:3 instead of 2:2, as I've just described above.
I then hit Enter (or OK - both have the same problem). What I hope to see
in the cell is =SUM(Sales), but what I see instead is =SUM(Sheet1!1:3).

In describing my original problem in my earlier post, I left out the fact
that I was going across sheets, because it didn't seem important. (And it
shouldn't be, should it?)

F3 seems to work as expected and desired in every case. I end up with
=SUM(Sales), which is what I want.

Thanks to you guys for your help!

Gord Dibben MS Excel MVP

 

[Gord Dibben]

Should read.........select A1 and enter the text label.

Select A1:C1 and CAS

Gord

Eric

Instead of using merged cells B1:B3, use "center across selection".

Select B1 and enter your label.

Select B1:B3 and Format>Cells>Alignment>Horizontal>Center across selection.

Get rid of any merged cells.

They cause no end of problems with sorting, copying, filtering, pasting and a
bunch of others I can't think of at the moment.

And maybe a new one.............as you have described.

See other comments in-line.




Why have you got a 2 at the top of the column?

Are you using R1C1 reference style?

In that case selecting the 2 should give you =Sheetname!C2

If you are not using the R1C1 notation then clicking on row 2 will give you
=Sheetname!$2:$2


Gord


Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP