Range name confusion

I'm using Excel XP.  I have a general question regarding 
range names.

Suppose I have the following in a spreadsheet:

Qty     Price     Sales
5        .75      =Quantity*Price
7        .80      =Quantity*Price
3        .50      =Quantity*Price
..
..
etc.

The first column is named "Quantity" and the second is 
named "Price".  Column 'C' contains the 
formula "Quantity*Price".  When I type the formula in 'C1' 
and copy it down, the formula works correctly, multiplying 
each value in column 'A' with the corresponding value in 
column 'B'.  My question is:  Why?  Or rather, how?

If you click on "Insert/Name/Define" from the menu, and 
click on either 'Quantity' or 'Price', the formula 
referenced is absolute.  I would think the row part of the 
formula would have to be relative for the formula to 
work.  How does Excel know, for instance, to multiply the 
value in 'A7' with the value in 'B7', and so forth all the 
way down the list?

Thanks for any and all help.
0
5/16/2004 4:23:03 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
360 Views

Similar Articles

[PageSpeed] 37

Craig,

This isn't much of an answer, but "that's just the way it works."  It only
works when your reference to the range name is in any row of the range.  YOu
get the element from the same row as the formula referring to it.  Same
with:

=A5:A15

This only works in rows 5 - 15.  In row 6, it yields an error.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Craig" <cafennellNOSPAM@hotmail.com> wrote in message
news:d9d901c43afd$7aac8500$a501280a@phx.gbl...
> I'm using Excel XP.  I have a general question regarding
> range names.
>
> Suppose I have the following in a spreadsheet:
>
> Qty     Price     Sales
> 5        .75      =Quantity*Price
> 7        .80      =Quantity*Price
> 3        .50      =Quantity*Price
> .
> .
> etc.
>
> The first column is named "Quantity" and the second is
> named "Price".  Column 'C' contains the
> formula "Quantity*Price".  When I type the formula in 'C1'
> and copy it down, the formula works correctly, multiplying
> each value in column 'A' with the corresponding value in
> column 'B'.  My question is:  Why?  Or rather, how?
>
> If you click on "Insert/Name/Define" from the menu, and
> click on either 'Quantity' or 'Price', the formula
> referenced is absolute.  I would think the row part of the
> formula would have to be relative for the formula to
> work.  How does Excel know, for instance, to multiply the
> value in 'A7' with the value in 'B7', and so forth all the
> way down the list?
>
> Thanks for any and all help.


0
nowhere1083 (630)
5/16/2004 5:00:11 AM
Craig,

Well, damn.  That didn't make sense. "In row 6, it yields an error" is
wrong.  In row 6 it works.  In any row outside of 5 - 15 it yields an error.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" <nowhere@nowhere.com> wrote in message
news:ONAbJKwOEHA.3348@TK2MSFTNGP09.phx.gbl...
> Craig,
>
> This isn't much of an answer, but "that's just the way it works."  It only
> works when your reference to the range name is in any row of the range.
YOu
> get the element from the same row as the formula referring to it.  Same
> with:
>
> =A5:A15
>
> This only works in rows 5 - 15.  In row 6, it yields an error.
>
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Craig" <cafennellNOSPAM@hotmail.com> wrote in message
> news:d9d901c43afd$7aac8500$a501280a@phx.gbl...
> > I'm using Excel XP.  I have a general question regarding
> > range names.
> >
> > Suppose I have the following in a spreadsheet:
> >
> > Qty     Price     Sales
> > 5        .75      =Quantity*Price
> > 7        .80      =Quantity*Price
> > 3        .50      =Quantity*Price
> > .
> > .
> > etc.
> >
> > The first column is named "Quantity" and the second is
> > named "Price".  Column 'C' contains the
> > formula "Quantity*Price".  When I type the formula in 'C1'
> > and copy it down, the formula works correctly, multiplying
> > each value in column 'A' with the corresponding value in
> > column 'B'.  My question is:  Why?  Or rather, how?
> >
> > If you click on "Insert/Name/Define" from the menu, and
> > click on either 'Quantity' or 'Price', the formula
> > referenced is absolute.  I would think the row part of the
> > formula would have to be relative for the formula to
> > work.  How does Excel know, for instance, to multiply the
> > value in 'A7' with the value in 'B7', and so forth all the
> > way down the list?
> >
> > Thanks for any and all help.
>
>


0
nowhere1083 (630)
5/16/2004 3:53:29 PM
In my Excel manual for Version 5.0 its called 'Implicit Intersection' and is
covered on page 145/146 ; referring to Intersecting Ranges.

But I have to admit I cannot find it in help!

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Earl Kiosterud" <nowhere@nowhere.com> wrote in message
news:%23z8VN31OEHA.1960@TK2MSFTNGP10.phx.gbl...
> Craig,
>
> Well, damn.  That didn't make sense. "In row 6, it yields an error" is
> wrong.  In row 6 it works.  In any row outside of 5 - 15 it yields an
error.
>
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Earl Kiosterud" <nowhere@nowhere.com> wrote in message
> news:ONAbJKwOEHA.3348@TK2MSFTNGP09.phx.gbl...
> > Craig,
> >
> > This isn't much of an answer, but "that's just the way it works."  It
only
> > works when your reference to the range name is in any row of the range.
> YOu
> > get the element from the same row as the formula referring to it.  Same
> > with:
> >
> > =A5:A15
> >
> > This only works in rows 5 - 15.  In row 6, it yields an error.
> >
> > -- 
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "Craig" <cafennellNOSPAM@hotmail.com> wrote in message
> > news:d9d901c43afd$7aac8500$a501280a@phx.gbl...
> > > I'm using Excel XP.  I have a general question regarding
> > > range names.
> > >
> > > Suppose I have the following in a spreadsheet:
> > >
> > > Qty     Price     Sales
> > > 5        .75      =Quantity*Price
> > > 7        .80      =Quantity*Price
> > > 3        .50      =Quantity*Price
> > > .
> > > .
> > > etc.
> > >
> > > The first column is named "Quantity" and the second is
> > > named "Price".  Column 'C' contains the
> > > formula "Quantity*Price".  When I type the formula in 'C1'
> > > and copy it down, the formula works correctly, multiplying
> > > each value in column 'A' with the corresponding value in
> > > column 'B'.  My question is:  Why?  Or rather, how?
> > >
> > > If you click on "Insert/Name/Define" from the menu, and
> > > click on either 'Quantity' or 'Price', the formula
> > > referenced is absolute.  I would think the row part of the
> > > formula would have to be relative for the formula to
> > > work.  How does Excel know, for instance, to multiply the
> > > value in 'A7' with the value in 'B7', and so forth all the
> > > way down the list?
> > >
> > > Thanks for any and all help.
> >
> >
>
>


0
Charles740 (233)
5/16/2004 5:33:17 PM
harles.

Right.  My statement about getting the value from the same row isn't
necessarily the case.  If the range referred to in the formula is a row,
instead of a column, you'll get the value from the same column.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Charles Williams" <Charles@DecisionModels.com> wrote in message
news:uwhQYv2OEHA.3944@tk2msftngp13.phx.gbl...
> In my Excel manual for Version 5.0 its called 'Implicit Intersection' and
is
> covered on page 145/146 ; referring to Intersecting Ranges.
>
> But I have to admit I cannot find it in help!
>
> Charles
> ______________________
> Decision Models
> FastExcel Version 2 now available.
> www.DecisionModels.com/FxlV2WhatsNew.htm
>
> "Earl Kiosterud" <nowhere@nowhere.com> wrote in message
> news:%23z8VN31OEHA.1960@TK2MSFTNGP10.phx.gbl...
> > Craig,
> >
> > Well, damn.  That didn't make sense. "In row 6, it yields an error" is
> > wrong.  In row 6 it works.  In any row outside of 5 - 15 it yields an
> error.
> >
> > -- 
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "Earl Kiosterud" <nowhere@nowhere.com> wrote in message
> > news:ONAbJKwOEHA.3348@TK2MSFTNGP09.phx.gbl...
> > > Craig,
> > >
> > > This isn't much of an answer, but "that's just the way it works."  It
> only
> > > works when your reference to the range name is in any row of the
range.
> > YOu
> > > get the element from the same row as the formula referring to it.
Same
> > > with:
> > >
> > > =A5:A15
> > >
> > > This only works in rows 5 - 15.  In row 6, it yields an error.
> > >
> > > -- 
> > > Earl Kiosterud
> > > mvpearl omitthisword at verizon period net
> > > -------------------------------------------
> > >
> > > "Craig" <cafennellNOSPAM@hotmail.com> wrote in message
> > > news:d9d901c43afd$7aac8500$a501280a@phx.gbl...
> > > > I'm using Excel XP.  I have a general question regarding
> > > > range names.
> > > >
> > > > Suppose I have the following in a spreadsheet:
> > > >
> > > > Qty     Price     Sales
> > > > 5        .75      =Quantity*Price
> > > > 7        .80      =Quantity*Price
> > > > 3        .50      =Quantity*Price
> > > > .
> > > > .
> > > > etc.
> > > >
> > > > The first column is named "Quantity" and the second is
> > > > named "Price".  Column 'C' contains the
> > > > formula "Quantity*Price".  When I type the formula in 'C1'
> > > > and copy it down, the formula works correctly, multiplying
> > > > each value in column 'A' with the corresponding value in
> > > > column 'B'.  My question is:  Why?  Or rather, how?
> > > >
> > > > If you click on "Insert/Name/Define" from the menu, and
> > > > click on either 'Quantity' or 'Price', the formula
> > > > referenced is absolute.  I would think the row part of the
> > > > formula would have to be relative for the formula to
> > > > work.  How does Excel know, for instance, to multiply the
> > > > value in 'A7' with the value in 'B7', and so forth all the
> > > > way down the list?
> > > >
> > > > Thanks for any and all help.
> > >
> > >
> >
> >
>
>


0
nowhere1083 (630)
5/17/2004 1:05:00 AM
Reply:

Similar Artilces:

make the cell hyperlink to any name within it
is it possible to make the hyperlink relevent to the text within a cell. i.e if the cell said paul it would email to pauls email address or if the same cell said mark it would email to marks email address and so on. any ideas regards Hoyt Create a table with two columns, column A would be a name, Paul etc, column B would contain the equivalent email address from column A Select the range of data entered then choose Insert Menu and Name and enter a name for this range. Then use Data Sort (this function wont work unless the data is sorted into order). Highlight the cell you want say C2 ...

I'm confused!!!
I've already posted this problem to this group, but I don't know anything about consolidation, or if that's even what I need. I'm going to try to be as clear as possible on this: I have a workbook with 5 worksheets. All of the worksheets have the same exact format, (rows, columns). The first worksheet is call "Master Inventory List". It is not accessible to any of my purchasers. The next 4 worksheets are inventory lists for each of my 4 purchasers. When they purchase inventory and enter the quantity, part #, purchase price, sales price, etc... into their indivi...

Define a range name using VB
I have a macro setup that imports and copies data from two source files into an excel spreadsheet. What I want to do after I have imported all of the information is to select the data, and then define it as a name for use in a pivot table. The code I'm using at the moment to slect the range (no matter the lenght of data) is: ******************** Sub RangeSelect() ' This will select all the current cells no matter the size ' of the data that has been imported Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select End Sub ******************** The number of fields is ...

Extracting data from a worksheet based on a range of in a column.
Alright, This is my first post to the group!! Here is what I have to get it started: Worksheet(Manuf#) 26025 records (Product#, Description,Buyline, Manuf#) Worksheet(Products) various amount of product ID's in column A(only column of data), this all I want to have in this worksheet, just the list of products I want to find from Manuf#. So what I need is a macro, Module, whatever it will take so I can look at the Products in Worksheet(Products) and extract just those Records from Worksheet(Manuf#) into a new Worksheet named Results? Any help at this time would be greatly appreciated. ...

how do i print a list of names and dates in excel into a two-colu.
i have a list of names and dates comprising three columes in excel. How do i print this list in an array two colums wide on 81/2x11 paper. i.e.: NAME....DATE....DATE NAME...DATE....DATE there is enough data to fill four pages. data needs to be sorted so that new names can be added alphabetically. in page setup on the sheet tab, try selecting "over, then down" as the page order. "Captainbob" wrote: > i have a list of names and dates comprising three columes in excel. How do i > print this list in an array two colums wide on 81/2x11 paper. i.e.: > ...

Changing the name on registration
Hi bought my computer second hang and want to change all the names to my name instead of having the previous owner's name on everything. How do I do that? On 19/11/2007 20:49, in article af25d4a1-5e51-4d95-abbd-837baa545d62@e4g2000hsg.googlegroups.com, "misspbc@gmail.com" <misspbc@gmail.com> wrote: > Hi bought my computer second hang and want to change all the names to > my name instead of having the previous owner's name on everything. How > do I do that? Find the 'remove office' utility in the Microsoft Office folder (Microsoft Office 2004:Addition...

named range problem
I have 5 sheets in a workbook. Each sheet has a column named "FunctCostCenter". I want to create a named range for "FunctCostCenter" on each sheet. I can't seem to get it to accept "FunctCostCenter" as a name for each sheet - if I do it manually or programmatically - it overwrites the previous address for "Notes". Here is my code that creats the named range: Sheets(tabname).Range(thisrange).Name = "FunctCostCenter" When I run it on sheet 1 it creates it for sheet 1. When I run it on sheet 2 it overwrites the previous and sets t...

Can I change the OWA's folder name in Exchange2003 ?
Hi! In Exchange 2000 I can renane the owa folder name. Such as www.myserver.com/exchange change to www.myserver.com/webmail But in Exchange 2003, I can't rename it. And I've try to create a new virtual name, but still don't work. So how can I change the OWA folder name? Thank you very much! Alvin. You can create a new virtual directory under: Exchange Org, Admin Groups, Servers, Protocols, HTTP, and Right-Click on the Exchange Virtual Server object, be sure to select Mailboxes for SMTP domain and choose the appropriate SMTP domain. "Come2" <come2@ms76.url.com...

code to copy range
Hi anyone, I�m having two sheets in my workbook where I have named them as �Receipt� & �Price Range�. I'm searching for a formula that would copy the appropriate price range for the specific age and "gender" in the receipt sheet in the workbook. Suppose if the age is written in the cell "B2" and "gender" as either "Male or "Female" in the cell "E2" of how could the code be modified so that it copies the appropriate price range for the specific age and gender in the receipt sheet. Note: if any figure between the a...

Investment names vs. preferred names
You can set preferred names for payees, but in my portfolio, I'd like to name the investments with the real name and not the abbreviated name. Example, Fidelity has "Fidelity Low-Priced Stock" and that's the name I want to use but when I download the transactions, it comes down as "Fidelity Low Pr Stk" so I do not get any matches. I even changed the symbol "FLPSX" to be the symbol for the investment I want. How can I make them match? Mike, After the download is completed and you click on the items in the account list, you will should get a scree...

Range Names
What distinguishes a named range from being viewed: 1) In the NameBox (theDrop-down box - to left of the formula bar) Versus 2) By doing Control+F3 (equivalent to Insert, Name, Define...) TIA, I'm not exactly sure what your question is, but names that refer to formulas or constants are not displayed in the Names box, but are displayed in the Names dialog. Beyond that, I think the two behave the same. Perhaps you could be a bit more specific? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "JMay" <j...

Error: Invalid Column Name
After successfully migrating 96 records to the Account object, when clicking on an account name, an error: Invalid column name 'CFSExtension_1' appears preventing the record from opening. I can't find any info about this to track down the fix. Any ideas? Thanks ...

Creating a chart using a cell as the range reference
Hi. I have set up a macro where you can type in the name and title of th chart in a cell, and the range, run the macro and it creates th chart. Trouble is that taking the name from a cell is easy: .ChartTitle.Characters.Text = Sheets("Sheetname").Range("Y26") But I cannot do this for a range: ActiveChart.SetSourceData Source:=Sheets("Tables").Range("Y25") PlotBy:= _ xlRows The Range in this formula should point to a variable (for instanc A1:A10) and I want to be able to type that range into the cell (in thi case Y25). Is there a way to define a var...

Excel name list dropdown lost
I have a workbook with 10-15 names that used to appear in a drop down list and I was able to select a name and go directly to the array that it defined. I hit some key by mistake and now I cannot see my dropdown list. I tried getting out of Excel and reloading the worksheet and that did not work. This list was created by clicking Insert/name/define. It was working just fine until I hit some key. Any help on getting my list back would be greatly appreciated. Thanx Jim -- JimBray ------------------------------------------------------------------------ JimBray's Profile: http://www.ex...

Contacts from an xls file with Name & Number
Dear All, I have a spreadsheet which contacts, in column A, a contact name and then in column B a contact number for them. Is there a way I can import these into Outlook (2002 SP- 2) each as a contact? Many Thanks, Adam Yes. You need to define the range name of your contacts first in Excel (include the column heading names). Then go into Outlook. Select the Contacts folder. Then choose File, Import/Export. Choose Import from another program or file and click Next. Scroll down and choose Microsoft Excel. Click next. Browse for the Excel file name. The important step in thi...

exporting specific cell(s)/ range
right now this code exports to another sheet, and only exports the active cell. I would like to export a given cell range in a row (A4:C4, F4,I4:Q4), with a comfirmation based on F4 being "y" vs "n". Sub ClickAdd() Dim rngAvailable As Range, rngCell As Range, bolSuccess As Boolean Set rngAvailable = ThisWorkbook.Worksheets("Sheet1").Range("B18:B40") 'Range Of Cells that needs to change' For Each rngCell In rngAvailable If rngCell.Value = vbNullString Then rngCell.Value = ActiveCell....

Outlook could not recognize one or more names?
I have the below code that creates an outlook e-mail. I get the error message "Outlook could not recogniz one or more names." Now varemailtro is where the error occurse. This variable is equal to the "name@domain.com", and have added a msgbox to show what it is equal to, so I know it exists and with the quotes..... The line in the code for declaring its value is: varemailtrto = Chr(34) & DLookup("[TransmittalEmailAddress]", "tblTransmittalTo", "[TransmittalTo] Like '" & [Forms]![frmTransmittal]![TransmittalTo] &am...

Any way to change the "Company Name" in my license?
Hi, I just installed Office 2004, which I bought with my own money, but when any of the programs launches it has my employer's name, because it got it from my Address Book when I installed it. Is there any way to change my "Company Name" in the license? I suppose I could do it if I trashed the Microsoft Office folder and reinstalled from the CD, but is there an easier way? Jerry Krinock In article <BCEBE843.10A9%jerry@ieee.org>, Jerry Krinock <jerry@ieee.org> wrote: > Hi, > > I just installed Office 2004, which I bought with my own money, but when an...

Funky Function Name
I made a custom function called GetPrice. For some reason, it created a second Userdifined function category on the drop down list and the name shows up as: "testfunction.xls!Module1.GetPrice" How can move it to the other Userdefined list and drop the prefix before the name? Thanks That just means that there is a UDF by that name in another workbook. -- HTH RP (remove nothere from the email address if mailing direct) "T" <T@discussions.microsoft.com> wrote in message news:971C3553-B3E3-4964-95B7-B165167232BE@microsoft.com... > I made a custom function...

Confusion over Microsoft newsgroup participation
After subscribing to this group I visited the two Microsoft Windows Media Player newsgroups. There I read that Microsoft were pulling out of newsgroup participation. Is this fact, and how significant is that announcement to this group? Announcement regarding Microsoft provided newsgroup and server closing occurred quite a few months ago. All nttp groups on this server msnews.com are scheduled for closure with a target date of this fall(October) Once this newsgroup is closed(only a small quantity of all prior nttp groups remain at this time) Windows Live questions, suggestions ...

Cannot find and delete an old named formula
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have recently saved a workbook as a new file, under a new name. Every time I save the file I get the following message: <br><br>&quot;A formula in a cell (Name: Telecom) could not be converted because it contains a function that is not available in the file format to which you are saving.&quot; <br><br>When I first saved the file there were several named cells identified that I was able to find and delete, and this last one (Telecom) has been deleted using the name define list but t...

err: copied formula has name that already exists, but I don't think does
Thanks to all that have given help and that give in the future! I have a macro in a workbook that copies a worksheet from another workbook into the macro running one (code at end of this message). When I run the macro, I get an error that says: "A formula or sheet you want to move or copy contains the name 'blah', which already exists on the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, click Yes. To rename the range ..."...and so on... I checked with Edit>Find for 'blah' but it can&...

Format Telephone Cell Range
I have a contact list with my telephone #'s starting in H9. I have the cells formated in this row with the special telephone format option; however, when my data entry person enterred the data, she didn't realize this so now I have some formatted (###) ###-#### which is the special excel format and some are formatted like she enterred them, ### ### ####. Is there a way to make all of them the same format as the (###) one? Thanks for you help. Select the column and do a bunch of edit|replaces replace: ( ) (spacebar) with: (leave blank) replace all (don't forget hyphens) Vi...

Writing the Sheet Name to a Cell
Hello, Is there a formula that will display the Sheet name in a cell? Thanks Ruan "Ruan" <ruan@aegismed.com> wrote in message news:uc7oM0EnEHA.3992@TK2MSFTNGP15.phx.gbl... > > Hello, > > Is there a formula that will display the Sheet name in a cell? > > Thanks > Ruan > Hi Ruan, Try this: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) HTH, Alan. Thanks Alan. That works great. Ruan "Alan" <alan@alan.alan> wrote in message news:uKw1K7EnEHA.4004@TK2MSFTNGP10.phx.gbl... > "...

CRM 1.2 attachments
Hi there, in MS CRM 1.2 there is a limit of the path/file name length, when you want to upload an attachment. You can only upload an attachment with path/file name length of maximum 185 characters. Is there a way to avoid this limit? Thanks for your replies, Florian ...