Displaying named range used in formula :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Displaying named range used in formula
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

dviper
Welcome to the Board


Joined: 20 Sep 2003
Posts: 2

Flag: Blank

Status: Offline

 Reply with quote  

Displaying named range used in formula

I would like to display the named range used in a formula in an adjacent cell. For example, cell A1 has the formula =borrowrate, which is a named range in another worksheet. I would like to display the name "borrowrate" in the adjacent cell. Is there are function that can do this?

Post Sat Sep 20, 2003 5:33 pm 
 View user's profile Send private message AIM Address

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Displaying named range used in formula

Hi dviper:

Welcome to MrExcel Board!

One of the ways I can do what you are intending in this particular case is by using the FORMULATEXT function fro MoreFunc Add-in (available for free) as depicted in ...

Microsoft Excel - Book4___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

B
C
D
E
5
    
6
 9/1/03=borrowedDate 
7
    
Sheet4 

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


I hope this helps. If I have misunderstood your question -- my apologies!
_________________
Regards!
Yogi Anand

Post Sat Sep 20, 2003 6:52 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Displaying named range used in formula

And to show it without the = sign ...

Microsoft Excel - Book4___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

B
C
D
E
5
    
6
 9/1/03=borrowedDateborrowedDate
7
    
Sheet4 

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


The formula in cell D6 is ...

=FORMULATEXT(C6)

and the formula in cell E6 is ...

=MID(TEXT(D6,"@"),2,255)
_________________
Regards!
Yogi Anand

Post Sat Sep 20, 2003 6:57 pm 
 View user's profile Send private message

dviper
Welcome to the Board


Joined: 20 Sep 2003
Posts: 2

Flag: Blank

Status: Offline

 Reply with quote  

Re: Displaying named range used in formula

Thank you very much; it worked great! icon_biggrin.gif

Post Sat Sep 20, 2003 9:47 pm 
 View user's profile Send private message AIM Address

Jane Suban
Board Regular


Joined: 14 Aug 2003
Posts: 48

Flag: Australia

Status: Offline

 Reply with quote  

Re: Displaying named range used in formula

I have a similar problem, and it seemed as though FORMULATEXT was the answer - and it probably would be if I had permission to download the add-on! I don't, so I can't use it.

I have the following formula in a cell:

=SUMPRODUCT(--('G:\Accounting\Journals\Standing Journals\F04\aug03\Raw Material stock rec Aug03.xls'!SP_Integrations=A35),('G:\Accounting\Journals\Standing Journals\F04\aug03\Raw Material stock rec Aug03.xls'!SP_Totals))

Of course, this returns a value. But I want to extract the "Aug03" part of the formula so I can use a macro to replace it with "Sep03". How can I do this without using FORMULATEXT???

Thanks icon_nervous.gif

Post Mon Sep 22, 2003 5:11 am 
 View user's profile Send private message

NateO
MrExcel MVP


Joined: 18 Feb 2002
Posts: 4311
Location: Minneapolis, Mn, USA
Flag: Usa

Status: Offline

 Reply with quote  

Re: Displaying named range used in formula

Hello Jane,

How about ctrl-h, replace? You can record a macro with this approach as well...
_________________
Regards,
Nate Oliver

Post Mon Sep 22, 2003 6:00 am 
 View user's profile Send private message

Jane Suban
Board Regular


Joined: 14 Aug 2003
Posts: 48

Flag: Australia

Status: Offline

 Reply with quote  

Re: Displaying named range used in formula

Hi Nate,

I know I could do an Edit>Replace from a macro, but it would require the correct input from the user, who in this case is the kind of bloke who seems to be a walking calamity as far as the computer is concerned. Murphy's rule of "anything that can go wrong, will go wrong" applies to him!!! I wanted to remove the risk of incorrect input by isolating the part of the formula referring to the month so that I could change it to the correct month whatever it's value.

Without the FORMULATEXT command, is there any way I can efficiently do this?

Thanks icon_wink.gif

Post Mon Sep 22, 2003 6:17 am 
 View user's profile Send private message

NateO
MrExcel MVP


Joined: 18 Feb 2002
Posts: 4311
Location: Minneapolis, Mn, USA
Flag: Usa

Status: Offline

 Reply with quote  

Re: Displaying named range used in formula

Hello agian, see procedure #2.

http://www.mrexcel.com/board2/viewtopic.php?t=7744&start=28
_________________
Regards,
Nate Oliver

Post Mon Sep 22, 2003 11:59 am 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.