Excel Range Names

I am trouble shooting a worksheet for a colleague. She receives a work sheet 
with formulas that are created by using range names. She wants me to delete 
the range names and supplement the cell addresses. One of the formulas is a 
SUMIF. The value in the criteria field changes with each cell. When I try to 
copy the formula using the cell addresses it will not work. Is it necessary 
to do each cell by hand or is it possible to recalculate a range at a time. I 
have tried the manual calculation as well and it is not working.

Any help would be appreciated.
-- 
Tech Trainer
0
5/20/2005 7:13:36 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
597 Views

Similar Articles

[PageSpeed] 17

Tech Trainer,

IF the SUMIF formula is something like

=SUMIF(RangeName1,A1,RangeName2)

then you need to replace the RangeNames with their range addresses using
absolute addressing, along the lines of:

=SUMIF($A$1:$A$10,A1,$B$1:$B$10)

-- 
HTH,
Bernie
MS Excel MVP


"trainer2000" <trainer2000@discussions.microsoft.com> wrote in message
news:0E61CA33-EFA9-4CD6-80D2-911D38A5E2B4@microsoft.com...
> I am trouble shooting a worksheet for a colleague. She receives a work
sheet
> with formulas that are created by using range names. She wants me to
delete
> the range names and supplement the cell addresses. One of the formulas is
a
> SUMIF. The value in the criteria field changes with each cell. When I try
to
> copy the formula using the cell addresses it will not work. Is it
necessary
> to do each cell by hand or is it possible to recalculate a range at a
time. I
> have tried the manual calculation as well and it is not working.
>
> Any help would be appreciated.
> -- 
> Tech Trainer


0
Bernie
5/20/2005 7:42:01 PM
Reply:

Similar Artilces:

Excel 2007
When I right-click a cell to edit an existing cell comment in Excel 2007, the comment text box jumps down to row 399. My spreadsheet ends after row 50. I can edit the comment text box, but must then CNTL-Home to go back to the top of the worksheet and scroll to the area where I was working. This doesn't happen when creating a new comment, or when mousing over to view an existing comment--in both cases the comment is just to the right of the cell. Just happens when editing a comment. Does anyone know why this happens? I've made sure there is no formatting or other info in rows 5...

Printing in Excel overlaps previous text
When printing in Excel and in outlook the text appears to be printing however it prints on previous printed text (hence overlaps text). Only occurs in Excel and outlook. Prints normally in Word and other office apps. Have tried re-installing printer driver. Printing to HP Deskjet 1100 printer. So in Excel you have text overlapping other columns? And expanding the column or formatting the cells to wrap text does not work? "Nash" wrote: > When printing in Excel and in outlook the text appears to be printing however > it prints on previous printed text (hence overlaps ...

Locking Named Cells
Lads, I have a lot of named cells in a workbook. Can I lock the name of th cells but allow users to change the data in them? I do not want them to be able to change the cell names becuase o references to them elsewhere in the workbooks. Lia -- wjoc ----------------------------------------------------------------------- wjoc1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1528 View this thread: http://www.excelforum.com/showthread.php?threadid=47473 Are you sure that the users are changing the names? Maybe they're just adding another name for that range...

Open Word Doc From Excel Macro
I have an Excel Sheet that contains Macros. At the end of one of the Macros, I would like it to open a Word Document. Something like Document.open FileName:="C:\Path\name.doc", except it needs to run from the Excel sheet. Is there a way to do this? One way http://support.microsoft.com/kb/q177760/ Regards, Peo Sjoblom "MATT" wrote: > I have an Excel Sheet that contains Macros. At the end of one of the Macros, > I would like it to open a Word Document. Something like Document.open > FileName:="C:\Path\name.doc", except it needs to run from ...

Count number of lines of text in a range/value.
To start, 1) Add a form w/ a textbox to your VBA project. 2) Set the textbox's AUTOSIZE property to False. 3) Set the textbox's MULTILINE property to False. In your code... 4) Call on the Form's SHOW method (modal or modeless is irrelevant). 5) Set the textbox's TEXT property to the (trimmed) value you are working with. 6) Set the textbox's AUTOSIZE property to True. 7) Set the textbox's MULTILINE property to True. 8) Save/get the textbox's LINECOUNT property. 9) Call on the form's HIDE method. btw, I assume no responsibility for the code. Use it at your own...

Excel Finding instances of a value in defined range
Can some one help me with a small excel problem.. I have a excel sheet in which I have.... wait... why dont you check th attached file and then tell me how do I find out the repeated instance of a check.. For example , I want column H to say - "Instance Correct" if a chec no. X has been found 2 times in the defined range A1-F7 , and if th amount mentioned under deposit column of Bank is same as amount mentio in Deposit column of company.. If the check number is found 2 times bu the amount is different then column H should say - "Instance Wrong", i the check number is foun...

Difficulty with the property .Value of a Range
Hi, Is someone knows why this sub works well Sub test() Dim MyDate As Date, X MyDate = CDate("14/08/2008") '******************* X = Application.Match(CLng(LaDate), Range("A1:A25"), 0) '******************* End Sub And if i add the property ".value" to the object "Range", an error is generated. Sub test() Dim MyDate As Date, X MyDate = CDate("14/08/2008") ' French format -> French Version Excel. '******************* X = Application.Match(CLng(LaDate), Range("A1:A25").Value, 0) '******************* ...

Check boxes and user names in forms 03-03-10
I would like to create a checklist in Word using a form. The in-built Help gives a good description on how to create a form and add check-boxes to it, but I would like also to have fields next to the check boxes which auto-populate with the user name when the check box is set, and another field which auto-fills the time the check box was ticked. Any advice or suggestions gratefully received. -- TobyR If you assign the bookmark names checkname to the checkbox and textname to the formfield in which you want the user name to be displayed and texttime to the formfield in ...

BUG: Excel Date Issue
I think there is a bug in the Excel Date calculations. I am using Office 2003 and here is what I do Under Tools->Options->Calculation, make sure you are *NOT* using 1904 date formats In 3 cells, enter the following numbers 1 32 60 Select the cells and format them as dates. You get: 1/1/1900 Correct 2/1/1900 Correct 2/29/1900 ***WRONG *** ???? 1900 is not a leap year so how can this be? It's a bug that comes from Lotus 123 and to be compatible MS knowingly added this bug to Excel That was of course during the days when Lotus ruled the spreadsheet world <g> R...

How can I e-mail multiple persons whose addresses are in excel?
I have a list of students in one excel file. One column is dedicated for their e-mail addresses. Is there anyway to send an e0mail message to all students on the file at once? When I click the e-mail address, it automatically goes to Outlook with the person's address on the address. But how could I do the same for multiple addresses? Move the list of students to Outlook as a distribution list. In Excel, link to this distribution list. -- Gary's Student "RedCattledogs" wrote: > I have a list of students in one excel file. One column is dedicated for > th...

Cannot make individual name tags
Everytime I create name tags in Publisher I get an entire page of the same name tage when I print. I need one of every individual name tag created. I choose publications for print,labels, bookplate, name tag. I set up each name tag as an individual page and then when I hit print.....I get 8 name tags with the same info on them. Any help would be great. On 27 Apr 2004 18:19, "Bridgette" <anonymous@discussions.microsoft.com> wrote: >Everytime I create name tags in Publisher I get an entire page of the >same name tage when I print. I need one of every individua...

Find Lead by Company Name
We need to find Leads by company name in addition to contact name (lead name). Is there anyway to accomplish this without using Advanced Find? TIA No, You have to use advanced find. "WFD" <anonymous@discussions.microsoft.com> wrote in message news:8e4001c432ca$3f689e60$a001280a@phx.gbl... > We need to find Leads by company name in addition to > contact name (lead name). Is there anyway to accomplish > this without using Advanced Find? > > TIA We customized the preview pane to add the company name. >-----Original Message----- >No, You have to use ad...

Chart names #2
I am producing an Excel sheet with several pages of identical looking charts sourcing different data sets. 6 charts to a page and 4 pages worth of charts. I am using VBA to automatically change the scale of the charts and want to automate it a bit more: The VB code I want to use is similar to: For Sheet_Num = 1 to 4 For Graph_Num = 1 to 6 Set myRange = Worksheets(Sheet_Num).Range(Graph_Num) etc. Next Next However, I currntly need to put a translation table in to convert the Graph_Num into the actual graph name (Chart 1, Chart 14, Chart 16, etc.) I just want to rename the actu...

Excel Filter/Query
Hi, I have a table of data laid out in a spreadsheet. I would some wa of filtering out the rows that have a zero in one of the columns. filter works, but I need something different.. because I am exportin this data and it includes the filtered out rows as well. And it need to be somewhat automated, if possible... maybe it flows into anothe sheet. Any ideas? I put query in the title because it would retur results similar to an access query.. basically create a new table on new sheet. Thanks in advance -- proben93 ----------------------------------------------------------------------- p...

I would like to extract email addresses from an Excel document
I have a spreadsheet of names and addresses. I would like to extract the email addresses to put in an email to send out to all. Can this be done See http://www.ozgrid.com/VBA/HyperlinkAddress.htm -- Regards Dave Hawley www.ozgrid.com "ladyblue214" <ladyblue214@discussions.microsoft.com> wrote in message news:2BE45F54-5EA3-479D-A471-95EDAB000ACF@microsoft.com... >I have a spreadsheet of names and addresses. I would like to extract the > email addresses to put in an email to send out to all. Can this be done ...

Using Excell, how do you alphabetize the completed list?
See above. Use the Sort function. "Kimo" <Kimo@discussions.microsoft.com> wrote in message news:3522CAD5-31B0-4251-9F48-542FE4F44E93@microsoft.com... > See above. ...

Excel corrupts charts. How do I stop this?
After saving my work in an Excel 2007 file (that has multiple charts) and closing it, the chart formatting is corrupted and I have to resize all the fields every time I open up the file. How do I keep the chart from getting resize within the specified size of the chart? ...

Can I split 1 cell into 2 cells in Excel?
I am working in a spreadsheet where I frequently use the filter and sort functions. I want to split one cell into two in one column, I don't want to use two columns and merge the rest of the cells as this upsets the filter/sort functions. Try this.... Highlight the required cells you want to split and DATA > TEXT TO COLUMNS > NEXT then enter the parameter that you want to split by (colon, space, etc) FINISH "Chalky" wrote: > I am working in a spreadsheet where I frequently use the filter and sort > functions. I want to split one cell into two in one column...

Merging from Excel to Word #2
I am using Excel as a data source for mail Merge letters and have multiple sheets in each Excel document. I am not sure how to select a specific sheet when merging to avoid having to use the entire document and then seperating what I do and don't actually need I have also thought of using a new template each time I need to merge and copying the info over but there must be an easier option. Hi AFAIK you have to use the first worksheet of your workbook. Even with defined names Word seems to require the first sheet as datasource (see also http://www.mvps.org/dmcritchie/excel/mailmerg.htm) ...

Renaming columns and rows in Excel
Can we rename columns A, B,C etc. in Excel to user definable names? Similarly for rows? Sib No you can't. It is fixed. The only easy workaround is to put your own headings in and use Window/Freeze Panes so they show wherever you are on the sheet. Andy. "Sib Daspal" <infohorizon@vsnl.com> wrote in message news:DA86B37A-15F8-408F-90B8-BC63CE6EF8B4@microsoft.com... > Can we rename columns A, B,C etc. in Excel to user definable names? Similarly for rows? As Andy suggests you can enter "LABELS" at the top of columns or at the left of rows to give you a "...

change default outlook folder name
can I changed the default outlook folder names? like Sent Items, Deleted Items, Junk E-Mail etc. because I'm connecting to a IMAP server which has it's own set of folders for the exact same functionality. it's very annoy to have two sets of folders, and I can't seem to change these on the IMAP end, so how I can change the Outlook folders to match my IMAP server, if it's possible. thanks! you can't change the default names. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart...

Excel 2010 Worksheet Protect method
I have a workbook developed in Excel 2003. Now in Excel 2010 my Workbook_Open macro is getting error 1004, Application-defined or object-defined error. The line that the debugger points to is Worksheets("Attendance").Protect Contents:=True Was there some change in Excel 2007 or 2010 that invalidated the above? From reading Excel 2010 help it sure looks like the above is still valid. Yes, I really do have a worksheet called "Attendance". (In the immediate window I typed ?worksheets("attendance").name and it responded Attendance.) Macros are enabled...

flash the cell contents in excel to draw attention
how do I do it with excel from office 2003. I am using XP prof Continuous flashing will slow down your Excel application considerably not to mention become quite annoying. But if your stubborn, here's a link to some code from MVP Robert Rosenberg that I found in the google archives: http://tinyurl.com/4oxmp HTH Jason Atlanta, GA >-----Original Message----- >how do I do it with excel from office 2003. I am using XP prof >. > ...

cannot start the source application for this object in excel
An Excel file created in Excel 2000. It has an organizational chart. Replaced computer and running Office 2003 SBE. Can open spreadsheet, but when I double-click the chart object to edit, receive a "cannot start the source application for this object". -- Philip Mollica President ZDT Networks LLC The applets which created such things as org charts in Office were changed for Office 1003, and apparently the new applets aren't clever enough to open the old objects. I imagine it's possible to get back the older applets. I did this to get back Photo Editor, which was remov...

Change excel Open default to "all files"
I have a customer who gets lost when he tries to open a lotus file with excel 2000. Is there any way to change the file->open default file type of "all microsoft excel files" to "all files?" >-----Original Message----- >I have a customer who gets lost when he tries to open a >lotus file with excel 2000. Is there any way to change >the file->open default file type of "all microsoft excel >files" to "all files?" >. > That was the wrong email address...too many. My real email is jvinicky@hotmail.com. Thanks! I do...