Another Excel Problem

[JO]

I inherited this spreadsheet from someone who no longer works at my company and now I have to figure it out in order to use/modify it. My problem now is that I have cells that are calculating "stuff" but instead of referencing particular cells or areas (e.g. A1 or A1:A20), they reference names (e.g. STATCashFlows). My problem is that I can't find out what these names are refering to. I've tried :

1. clicking the Name Box.
2. Highlighting what I thought the areas were so that the name would appear in the name Box .
3. Insert, Name, Define.

but I can't find all of them. Is there a better way??

[Pseudolus]

Does "trace precedents" get you anywhere?

Might they be named ranges in other sheets? Check edit/links to see what sheets are being referenced.

(Sorry if these are both obvious things you've already done. Best I could come up with.)

[Obi-Wan Kenobi]

Find a safely blank area of the sheet. Then Insert Paste Paste list.

[urysohn]

Insert Name - Define should do it. If the named variable / function / formula / etc is being used in that worksheet, it should show up on the list. But make sure you're on the same worksheet where it's used when you go to the Insert Name list (note: this might mean you need to go to multiple sheets and go to the list within each sheet. Different named variables sometimes show up). Even if it's a reference to another sheet, it should still show up on the list.

[JO]

Ah, trace precedents actually GET me somewhere, thanks. Hmm, so now I know what it references to but if I try to change the reference to include, say, another cell (expand the name), Insert, Name Define and use the name give me "Invalid Name".

:swear:

OBK, did you mean, insert, name, paste?? If so, I get the same "list" than insert, name, define..

[JO]

urysohn, I thought Insert, Name Define would do it too but I'm missing A LOT of named area. All defined area of the entire workbook will appear when you do this, even if in a different worksheet. If so, the name of the worksheet will appear on the right of the named area/cell..

[Obi-Wan Kenobi]

OBK, did you mean, insert, name, paste?? If so, I get the same "list" than insert, name, define..

There should be a [Paste list] button when you do Insert, Name. That will give you your list in one handy, easy to see location.

[JO]

Uh?! :o

The only thing I have available when I select Insert, Name is :

Define..
Paste..
Create..
Apply..
Label..

:-?

[JO]

All right I love Microsoft. I couldn't redefine the range so I tried to mess around the name in the formula, then it told me that I had an error in my formula, "do you want to accept the corrections?" and I'm like "sure why not, it's so much fun anyway" and the correction had automatically updated the range to what I wanted it to be. Go figure..

[Obi-Wan Kenobi]

Oops. I left out a Paste. :duh:

[Pseudolus]

Hey, I never knew that one.

JO, when I go to Insert/Name and choose Paste, I get a dialog box popping up at me. At the bottom of that box I see a button called "Paste List" which pastes a list of all the named ranges in the sheet right into the document. Handy.

[JO]

Hmm, now I see it. Yeah, pretty handy, but it's still the list that doesn't have the names I was looking for. But it's still good to know. Perhaps there could a justification of hanging out on this discussion board if I ever get caught by my boss, except if I'm in the Non-Actuarial topics of course.. hehehe :D

[urysohn]

All defined area of the entire workbook will appear when you do this, even if in a different worksheet.
That's true. Usually. And other times, not so much. I'm not sure if it's a bug or some pattern I just don't see. I'm sure Microsoft considers it to be a "feature". I usually notice it when the named reference is in another workbook and I'm trying to figure out why my spreadsheet is trying to "update links" even though I can't for the life of me find a linked cell.

[Gates Is Antichrist]

Not to dispute the prior posts, but the most direct way to SEE a named range is F5 (Go to), as I read the original question.

The rest is for urysohn and anyone w/ those infuriating mystery links:

I'm trying to figure out why my spreadsheet is trying to "update links" even though I can't for the life of me find a linked cell.If the following does not solve it, get PUP2000, which handles it as well as other nifty things.

http://j-walk.com/ss/excel/usertips/tip054.htm
(As you'll see here, Walkenback [j-walk] contributes amazingly and prolifically to newsgroups and with this site - even though he undercuts some of the need for his famed PUP2000 - now THAT's confidence)

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q188449

http://www.bmsltd.co.uk/MVP/Default.htm

[Cho Da]

Note also that name don't necessarily refer to absolute ranges.

I often see workbooks where there are names like: int: =7% ...

[Gates Is Antichrist]

Note also that name don't necessarily refer to absolute ranges.

I often see workbooks where there are names like: int: =7% ...That "int" example is okay, but as to using relative ranges for defined names: I would only do it if you want to punish anyone who ever has to maintain it. It may score points on the cuteness scale, but if anyone (very possibly including yourself) ever has to follow along ... fuh-get about it. It generally is a real nightmare.

If you really must have defined names be moving targets, or it gives sufficient benefit - then pay the penalty and document it sufficiently :lolup:

[Cho Da]

I agree that relative names can be a chore to follow, but they can also make it much easier to change functionality for sensitivity analysis.

If everything is named, and you write all your formulas with names, the work sheet actually becomes fairly self documenting. If you do this in conjunction with conditional formatting to help identify where cross checks fail, errors become less frequent and debugging takes less time.

[rafkooch]

JO, sometimes the named range is in a hidden sheet.[/quote]