Displaying or showing a defined cell name in another cell

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

Is there a way to display a defined cell name in another cell? <br><br>I would like to define a name for A1 to be &quot;_1A1&quot; and have that displayed in a different cell rather than the value of cell A1. I am ranking data based on the value in the cell, but would like to have the defined name returned in an adjacent cell: <br><br>   A     B     C      D        E <br>
1             rank  value  cell name <br>
2  10          1      10      _1A1 <br>
3  2           2      7       _1A2 <br>
4  7           3      2       _1A3
0
cmj21
4/21/2010 4:13:47 PM
mac.office.excel 1146 articles. 0 followers. Follow

1 Replies
675 Views

Similar Articles

[PageSpeed] 7

Not without VBA, no.

But I don't think you need the defined name.  It appears that just the cell
address would do you.  For which you can use the ADDRESS Function.

Cheers

On 22/04/10 2:13 AM, in article 59bb73c2.-1@webcrossing.JaKIaxP2ac0,
"cmj21@officeformac.com" <cmj21@officeformac.com> wrote:

> Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
> Is there a way to display a defined cell name in another cell?
> 
> I would like to define a name for A1 to be "_1A1" and have that displayed in a
> different cell rather than the value of cell A1. I am ranking data based on
> the value in the cell, but would like to have the defined name returned in an
> adjacent cell: 
> 
>    A     B     C      D        E
> 1             rank  value  cell name
> 2  10          1      10      _1A1
> 3  2           2      7       _1A2
> 4  7           3      2       _1A3

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

 -- 

John McGhie, Microsoft MVP (Word, Mac Word); Consultant Technical Writer,
McGhie Information Engineering Pty Ltd, Sydney, Australia.
Ph: +61 (0)4 1209 1410; mailto:john@mcghie.name


0
John
4/26/2010 2:43:54 AM
Reply:

Similar Artilces:

how to show the rectangle box position
i have one rectangle box on a sheet. whenever i move the rectangle box to left or right or top or bottom i would like to show the position of the rectangle box in cell("a1") how can i write some code to show that? ...

how do you place a spreadsheet(s) inside another spreadsheet
I am trying to setup a workbook with spreadsheets that contain other spreadsheets. Does excel allow this? if yes, how can I do it. Hi not 100% sure what you're after here ... a workbook can contain multiple worksheets. Each worksheet contains a grid of cells 256 columns wide & 65536 rows deep. This basic structure of excel can not be altered. Please post back with additional details on what you're trying to achieve and we might be able to suggest a solution. Cheers JulieD "ucastores" <ucastores@discussions.microsoft.com> wrote in message news:DDE44C32-...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

How to forward emails (unchanged) from one account to another
Assume I manage two different profiles and eMail accounts with my Outlook. How can I adjust in one of them that all arriving eMails should automatically be forwarded (unchanged !!) to the other account ? Sven "Sven Claasen" wrote: > Assume I manage two different profiles and eMail accounts with my Outlook. > > How can I adjust in one of them that all arriving eMails should automatically be forwarded (unchanged !!) > to the other account ? > > Sven > Automatically forward messages to another e-mail account - Outlook - Microsoft Office Online: http://of...

Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...

Insert an autofiltered range into another tab
I'm am having an issue when trying to insert a range from an autofilter on one sheet to another. The range has to be inserted above rows that have data in them. When I use the insert.shift:xldown, it only moves down the first column. TIA, Sub B_CreateTabs() Dim rngE As Range Dim lngLastRow As Long Dim mgrval, lobval, shtval As String mgrval = "myself" lobval = "dept" shtval = mgrval & "-" & lobval Windows("Mybook.xls").Activate Sheets(shtval).Select Sheets(shtval).Copy After:=Workbooks("Mybook.xls&quo...

hyperlinking to an item in Global Address List Options
I have had success linking via html from web page to opening a specific contact record from Microsoft Outlook. I have done it like this: outlook://Big%20Contacts/Contacts/~Peter%20Crawford However, my very large contact database also has people with basically the same name, like John Smith. In the current "Address Card" view inside Microsoft Outlook, one of the John Smith's is always sorted first. If I enter outlook://Big%20Contacts/Contacts/~John%20Smith the first John Smith in the sort order is always opened. However, I want to specify one of the other John Smith's....

Intermittent display
Hi, recently i encountered this problem on my PC. When i press the ON button, the monitor shows the normal starting up image for only 1 second and then it went black. Then, because my computer has a CPU FAN error (probably because the starting speed of my fan is a little slow), it would beep 3times and prompt me to press F1. During this time, the image would appear on the monitor for 1sec and then, becomes black again. Then, as it loads and enters window, it will flash yet again and i can see the cursor of my mouse at the center of the screen for that 1 second. Everything is running normall...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

display name #2
hi i have this problem: when i send a mail massage from an application by exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and not display name (Customer Care) how can i resolve it? Thanks An application should allow you to specify display name for sender. Does it send via SMTP or MAPI? Matrix_pd wrote: > hi > i have this problem: when i send a mail massage from an application by > exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and > not display name (Customer Care) > > how can i resolve it? > > Thanks -- Please re...

Error- Project is used by another user -Please select another proj
Please see the subject line, when im trying to open a particular project. it gives me following error. I checked the current users, there are no users. Apart from me. There are some non PA users,i tried to remove them from activity. inspite of me removing them from activity, im geting the same error. Help !!! Ramakrishnan Hello Ram, There is a stranded user in PA000001 table. You need to clear this User/Project combination. Ajay "Ram" wrote: > Please see the subject line, when im trying to open a particular project. > it gives me following error. > > I che...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

User Defined Required Fields
I have set several field on sub window Sales User-Defined Fields Entry of Sales Transaction Entry as "Required". If the user remembers to click User-Defined, then they become required. But if the user never clicks on User-Defined from Sales Transaction Entry, then they can still save the new document without the required fields entered. Does anyone know what I can do to fix this asside from continual user training? Your answer is VBA. You own Modifer, so you also have VBA enabled. You'll need to write VBA code to open the window (literally, push the button) then set th...

automatically naming files for savingb
Hi, for delivery notes i would like excel to look in the relative directory for a given customer, ie if C4 contains superspecs, files relating to this account would be in c:\work\superspecs\ files are named superspecsdel0001,superspecsdel0002.......etc superspecsinv0001...etc for delivery notes and invoices. what i would like is a line (or more) to add to a macro which will look in the appropriate directory, add 1 to the last four digits of the last file, enter this number into a cell on the sheet and save the new file with the appropriate name, then close the file. is this possible, or alte...

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

Excel not displaying German characters correctly.
This is a real puzzler. Has our entire office and IT department stumped. We have two machines (M1 / M2) which are equal spec/ operating system, applications, versions, etc... The only difference that we can see between the two machines is that one (M1) has Internet Explorer encoding set to "UTF-8" with "Automatic" selected. The other (M2) has "Western European ISO" with no automatic selected. Now, when the machines log into a web application and choose to download a German Excel file, M1 and M2 get the file correctly if they have their encoding settings...

Outlook 2003 not displaying multipart/mixed messages properly
This is a multi-part message in MIME format. ------=_NextPart_000_0017_01C49BFC.AB34E1D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I know that Outlook is known for having problems displaying = multipart/mixed messages but this is just weird. I am using Outlook 2003 with a POP email account and occasionally with = an email that has been exchanged a few times already and I will send out = a reply and when the person replies back to me what they wrote no longer = appears in the email. I basically get back a copy of the email I sent =...

formula to get sheet name
hi, can a formula retreive a worksheet name or does it need to be macro based? thanks, N.S. Hi Nigel, This formula that takes care of the possible situation whereby you have only one sheet in the workbook and its name is the same as the workbook's. Note: CELL("filename") will only work if the file has been saved at least once and if the file is opened in a different language system, the argument "filename" will need to be changed manually to the corresponding word (e.g. in Spanish "nombrearchivo"): =LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...

Concatenating cells but excluding blanks
Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line Any help gratefully received. I am working in Excel 2007 Many thanks. Bob Try this: http://img690.imageshack.us/img690/5826/nonamee.png Micky "Bob Fr...

Show only extended description on receipt
I have RMS set up in such a way that it shows the extended description of the items at the POS along with the regular description. When i print a receipt both descriptions print. Is it possible to print only the extended description, if so, how?? Hi Terry, You might downloaded the receipt format or done yourself customization. Just try to find first Entry.Item.ExtendedDescription and if you see another variable called Entry.Description Just remove the row or column with name "Entry.Description" It will work, let me know or send me email address I have already the customized o...

changing a name in mulit parts of a document
I am setting up a word document. This will be used as a template In this document I wish to insert the same "name" throughtout this document. Is it possible to change the name in one spot and all of the others change automatically thanks See http://gregmaxey.mvps.org/Repeating_Data.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>...

OWA Password page not showing up.
I installed the hotfix from kb 833734. I've also tried to follow the steps found at kb 327843. However, when I set an account to "change password at next logon" and go to that account in OWA on a FE that's using forms based auth, it doesn't redirect to the page for changing an expired password. However if i direct my browser to that page manually and fill in the form, it unexpires my password just fine. Is this how it's supposed to work? Shouldn't it detect that it's expired and thus redirect me to the page for resetting my password? Thanks. This ...