Replace Cells with Column names in functions?

I have a # of fairly long/complex cell functions that get hard to debug 
because there are also a lot of rows.

Is there anyway to change display so it replaces the column name e.g.

If(BT1204="X". BA="Y"

to

If(CustomerName="X", CustomerCode="Y")

?

0
Utf
3/17/2010 2:38:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
524 Views

Similar Articles

[PageSpeed] 16

CustomerName is a defined name range for BT1204

Find & Replace

Find what: BT1204
Replace with: CustomerName


"msnyc07" wrote:

> I have a # of fairly long/complex cell functions that get hard to debug 
> because there are also a lot of rows.
> 
> Is there anyway to change display so it replaces the column name e.g.
> 
> If(BT1204="X". BA="Y"
> 
> to
> 
> If(CustomerName="X", CustomerCode="Y")
> 
> ?
> 
0
Utf
3/17/2010 3:21:02 PM
Hmm, thanks, but i am more looking for some auto or semi automatic way for 
the displayed functions to reference the column names.  Search and replace 
for all formulas might get problematic.

"Teethless mama" wrote:

> CustomerName is a defined name range for BT1204
> 
> Find & Replace
> 
> Find what: BT1204
> Replace with: CustomerName
> 
> 
> "msnyc07" wrote:
> 
> > I have a # of fairly long/complex cell functions that get hard to debug 
> > because there are also a lot of rows.
> > 
> > Is there anyway to change display so it replaces the column name e.g.
> > 
> > If(BT1204="X". BA="Y"
> > 
> > to
> > 
> > If(CustomerName="X", CustomerCode="Y")
> > 
> > ?
> > 
0
Utf
3/17/2010 3:28:02 PM
msnyc07 wrote:
> I have a # of fairly long/complex cell functions that get hard to debug 
> because there are also a lot of rows.
> 
> Is there anyway to change display so it replaces the column name e.g.
> 
> If(BT1204="X". BA="Y"
> 
> to
> 
> If(CustomerName="X", CustomerCode="Y")
> 
> ?
> 


Look at "About labels and names in formulas" in the help file.
0
Glenn
3/17/2010 4:35:29 PM
Hi,

define a name to BT1204 as Customername, BA1204 as customercode.  Now go to 
Apply names, select all the names and click on OK.  All the BT1204 and 
BA1204 on the same sheet will get replaced by customername and customercode

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP

"msnyc07" <msnyc07@discussions.microsoft.com> wrote in message 
news:4BD059F7-D679-43BC-B192-696B64E51E9D@microsoft.com...
> I have a # of fairly long/complex cell functions that get hard to debug
> because there are also a lot of rows.
>
> Is there anyway to change display so it replaces the column name e.g.
>
> If(BT1204="X". BA="Y"
>
> to
>
> If(CustomerName="X", CustomerCode="Y")
>
> ?
> 
0
Ashish
3/17/2010 11:56:44 PM
Reply:

Similar Artilces:

Restricting the reactivationg cases function
In many customer service environments, the reactivation of existing cases is an exceptional procedure, to be performed only by a supervisor. I can't seem to find the right way of assigning a security role to accomplish this. Any ideas? -- Juan Fidalgo Sr. Bus. Analyst SCI If the role has update case, read activity and write activity then you should be able to Reactivate the case. Please also refer to Appendix A: Security Roles and Privileges http://msdn2.microsoft.com/en-us/library/bb954998.aspx This doc has detial privileges that are required in each of SDK message. -- This p...

Outlook 2003
When I type in a name to address an email, unless I have mailed them already and they appear on the recently used list, I then get a Check Names dialogue box and have to then search for additional names, even though the name is in the Address Book. Not sure why this is happening - I do have the Option to Suggest Names checked? Any ideas? Thanks State the steps you are using more clearly. You are talking about two completely different features here, the first is autocompletion, the second autoresolution. The latter will not prompt you for a search unless you have entered an invali...

ClistCtrl > How to auto-adjust columns size ?
Hi all, I have a CListCtrl in report view, two columns, hidden headerCtrl. I populate it with some items (less than 10). I'd like to programmatically auto-adjust the columns width according to their content, exactly like pressing Ctrl+'+' on a ListCtrl. Any idea ? Thanks, Dansk "Dansk" <dansk@laouilest.com> wrote in message news:e1x4VF9wHHA.276@TK2MSFTNGP06.phx.gbl... > Hi all, > > I have a CListCtrl in report view, two columns, hidden headerCtrl. > I populate it with some items (less than 10). > > I'd like to programmatically auto-adjus...

Excel Functions #2
hi guys, just wondered if anyone knew which function i would use to do this, because im fairly novice with excel: Basically if the cell value is above 110 i want the cell to turn green if the cell value is between 110 and 105 i want it to turn yellow if the cell value is below 105 i want it to turn red any help would be seriously appreciated. thanks spotseven wrote: > hi guys, just wondered if anyone knew which function i would use to > do this, because im fairly novice with excel: > > Basically if the cell value is above 110 i want the cell to turn gr...

TRIM FUNCTION in QUERY
Hello, What do I put to trim off the spaces after a value in query? The table is obtained from ODBC link and the values on a field somehow got spaces. Example: My field in the query is: WC: PO_WORKCENTER_ID Not sure WC: TRIM(PO_WORKCENTER_ID) ???? That should work. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Cam" wrote: > Hello, > > What do I put to trim off the spaces after a value in query? The table is > obtained from ODBC link and the values on a field somehow got spaces. Example: > > My...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

How do I alpha my mailing list in Microsoft excel and delete names
To alphabetize -- use sort Sorting, Some notes on sorting in Excel http://www.mvps.org/dmcritchie/excel/sorting.htm To delete rows pick a range of rows by selecting one or more contiguous row number(s) then Edit, Delete rows You might also be interested in Filtering to expose only names in Ohio You can use Filtering to permanently delete rows or just hide them. you can read more about filtering at http://www.contextures.com/tiptech.html If you want to print labels for your mailing list, you do the printing in MS Word with Excel acting as your data bas...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

Help With Problem: Nested IF Function, Office2K v Office2003
Hello Group, I'm looking for some help with a nested IF worksheet function. Shown below is a formula that I'm using in a model. =SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="<90",worksheet!$C$3:$C$54,0),0)) If short, what I'm trying to do is scan the data in two columns and for any cells which meet the TRUE case in both columns, then sum the third numbers for all cases returning TRUE-TRUE. If either cell returns a FALSE, then enter 0. I created the original formulas and model in Office2000 and have used the model for more tha...

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

Are there functions/Or Events that are called automatically when the database file first opens and When it closes?
All, I'm assuming its "Public Function init_something()" but I can't seem to find a reference to the correct syntax. I'm using Access 2007. Basically what I'm trying to do is initialize a list of global variables and I can't seem to figure out how to do it. Option Compare Database Public VariableName As String 'Call Init - Will throw an error 'VariableName = "Hello World" - Will throw an error Option Explicit Public Function Init() VariableName = "Hello World" End Function Private Sub btName_Click() ...

OUTLOOK FIELD NAME
Hi at all!!!I have this problem....I want export my mail from Lotus Notes into Microsoft Outlook 2000. I have the script already available but I don't know the field name of Outlook document. Something can help me??? Bye.... how in the heck did you get that far, i can't figure any of it out ----- giuliano wrote: ----- Hi at all!!!I have this problem....I want export my mail from Lotus Notes into Microsoft Outlook 2000. I have the script already available but I don't know the field name of Outlook document. Something can help me???...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) > =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) > ........etc > how do...

stationary column on a spreadsheet
I have a spreadsheet that i want to make the first column not move when i scroll to the right. And one teach a dummy. Select cell B1 Choose Window > Freeze Panes wc4ew wrote: > I have a spreadsheet that i want to make the first column not move when i > scroll to the right. And one teach a dummy. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

How can I insert a cell reference in a footer (eg for variable foo
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht End Sub -- Regards Frank Kabel Frankfurt, Ger...

How do I chart the same data cell on a range of worksheets?
I have the same row of cells on numerous worksheets that I want to chart or consolidate onto another worksheet ? Keith - You need to create a consolidated data range: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Keith wrote: > I have the same row of cells on numerous worksheets that I want to chart or > consolidate onto another worksheet ? ...

Re: Outlook client installation error (file or assembly name not found) (solved)
"Steve Yates" <steve@teamITS.com> wrote in message news:... > We have one PC that gets the following error and can't get past the system > requirements phase of the desktop client installation: > > "Microsoft CRM Client for Outlook Setup User Input > File or assembly name 9o3db28v.dll, or one of its dependencies, was not > found." > > (the file name is random and changes on every installation attempt). Any > suggestions? The user has tried repairing and reinstalling Office, > installing and uninstalling .NET 2.0, verifying/empytin...

count data in column
Hi, I am using excel97 and trying to create a chart that has 5 columns of data in it a,b,c,d,e. I an trying to make a chart only for certain data in column a and column d. The data that I key off of is in column d and begins with s/ how can I count the number of s/ in column d? how can I create a chart that shows both and only that data that begins with s/ and the data in column a? --- Message posted from http://www.ExcelForum.com/ In cell F2 (I assume row 1 has headers) enter this formula: =LEFT(D2,2) and fill it down as far as you need. select any cell in the table, and apply an au...

Shading cells not working
When I try to shade cells they remain white, but if I go to print preview the color shows. Why won't the cells change color in normal view? If the fill colours aren't appearing, the high contrast setting may be turned on. There's information in the following MSKB article: OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 Jenny wrote: > When I try to shade cells they remain white, but if I go > to print preview the color shows. Why won't the cells > change color in normal view? -- Debra Dalgleish...

Counting the number cells between two dates
Hi guys, Hope someone can help with this, I'm pretty sure it'll be quite a simple one. Column A:A contains a list dates, I want to use a formula to count the number of cells which contain a date between 01/01/05 - 31/01/05. Any ideas, Many thanks, Dave Try: =SUMPRODUCT((A1:A1000>=--"1/1/05")*(A1:A1000<=-- "1/31/05")) BTW - I'm using American date formats in mine. HTH Jason Atlanta, GA >-----Original Message----- >Hi guys, > >Hope someone can help with this, I'm pretty sure it'll be quite a simple one. > >Column A:A con...

How can you get Column graphs to be next to each other instead of spaced apart?
Thanks You are welcome. :-) -- David Biddulph <simonlavender@gmail.com> wrote in message news:1926b4aa-693f-41fa-a986-1aca28eb78b7@j1g2000yqi.googlegroups.com... > Thanks Hi Simon, If you just want the columns closer together, select the series, > Format data series > options, and reduce the the Gap Width. Dave "simonlavender@gmail.com" wrote: > Thanks > ...

Replace the column letters with my own heading
I want to know how i can i replace the column letters so i can put in my heading so that i always know which coumn i'm in and which question i'm answering when i'm entering data further down the page. Or any way that i can get my headings to follow down the page as i'm entering data so i can always see it no matter how many rows down i am. If your headings are in row 1 Click in cell A2 and Goto Window>Freeze Panes. Freeze Panes freezes anything above and to the left of the active cell. HTH Martin "Catter77" <Catter77@discussions.microsoft.com> wr...

How do I limit the number of characters in a cell?
When try to limit the number of characters allowed in cell by going through the data/validation menu, it still allows more than the number of characters than I specified. I don't get it :( ...