Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I correct a range 'reference'?
Using Excel XP.
===================== I've had a frustrating time with DSUM on a workbook I'm building from someone else's model - I keep getting #VALUE! errors. After lots of looking "underneath the hood" I think I see the problem, but don't know how to fix it. One of the formulas is: =DSUM(Revenue,'Revenue Projects'!$D$3,B1:B3) The problem comes up with "Revenue". When I use the Evaluate Formula tool, it italicizes "Revenue", and when I click on Evaluate, I see 'Revenue Projects'$A$1:$J$14 in it's place - italicized. I click Evaluate again and get the #VALUE! error. When I took a hard lok at this, I think the problem is that the worksheet ('Revenue Projects') refered to by the database (Revenue), has the WRONG range of cells. It should be $A$1:$O$19. How do I change this "inside" Excel? (This is the only thing I can find that might be causing this error.) -- Mike Webb Platte River Whooping Crane Maintenance Trust, Inc. a 501 (c)(3) conservation non-profit organization |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I correct a range 'reference'?
If the menus are similar to XL2000 - try clicking Insert/Names/Define, and
select Revenue from the list. Click in the RefersTo Box and hit F2 to enter edit mode. Then change the range reference. "Mike Webb" wrote: Using Excel XP. ===================== I've had a frustrating time with DSUM on a workbook I'm building from someone else's model - I keep getting #VALUE! errors. After lots of looking "underneath the hood" I think I see the problem, but don't know how to fix it. One of the formulas is: =DSUM(Revenue,'Revenue Projects'!$D$3,B1:B3) The problem comes up with "Revenue". When I use the Evaluate Formula tool, it italicizes "Revenue", and when I click on Evaluate, I see 'Revenue Projects'$A$1:$J$14 in it's place - italicized. I click Evaluate again and get the #VALUE! error. When I took a hard lok at this, I think the problem is that the worksheet ('Revenue Projects') refered to by the database (Revenue), has the WRONG range of cells. It should be $A$1:$O$19. How do I change this "inside" Excel? (This is the only thing I can find that might be causing this error.) -- Mike Webb Platte River Whooping Crane Maintenance Trust, Inc. a 501 (c)(3) conservation non-profit organization |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I correct a range 'reference'?
Tried that - but no change. However, I made one change that seemed to do
the trick. Rows 1 and 2 are empty so I changed the range reference to start with A3 vice A1. Went back to the worksheet with the DSUM errors and almost all are gone! I'll do some digging to see why they didn't all get fixed, but I feel I'm closer. Mike "JMB" wrote in message ... If the menus are similar to XL2000 - try clicking Insert/Names/Define, and select Revenue from the list. Click in the RefersTo Box and hit F2 to enter edit mode. Then change the range reference. "Mike Webb" wrote: Using Excel XP. ===================== I've had a frustrating time with DSUM on a workbook I'm building from someone else's model - I keep getting #VALUE! errors. After lots of looking "underneath the hood" I think I see the problem, but don't know how to fix it. One of the formulas is: =DSUM(Revenue,'Revenue Projects'!$D$3,B1:B3) The problem comes up with "Revenue". When I use the Evaluate Formula tool, it italicizes "Revenue", and when I click on Evaluate, I see 'Revenue Projects'$A$1:$J$14 in it's place - italicized. I click Evaluate again and get the #VALUE! error. When I took a hard lok at this, I think the problem is that the worksheet ('Revenue Projects') refered to by the database (Revenue), has the WRONG range of cells. It should be $A$1:$O$19. How do I change this "inside" Excel? (This is the only thing I can find that might be causing this error.) -- Mike Webb Platte River Whooping Crane Maintenance Trust, Inc. a 501 (c)(3) conservation non-profit organization |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How do I correct a range 'reference'?
First, my aplogies for untimely response. Been at a friends since Thursday
and his internet would not connect to MS's newsgroups. I've not used DSUM extensively, but it seemed fine with having empty rows in the middle of the data. Is the named range a dynamic named range? Empty rows/columns would cause problems w/ these types of range references. See http://www.cpearson.com/excel/named.htm#Dynamic for details and a downloadable example. "Mike Webb" wrote: Tried that - but no change. However, I made one change that seemed to do the trick. Rows 1 and 2 are empty so I changed the range reference to start with A3 vice A1. Went back to the worksheet with the DSUM errors and almost all are gone! I'll do some digging to see why they didn't all get fixed, but I feel I'm closer. Mike "JMB" wrote in message ... If the menus are similar to XL2000 - try clicking Insert/Names/Define, and select Revenue from the list. Click in the RefersTo Box and hit F2 to enter edit mode. Then change the range reference. "Mike Webb" wrote: Using Excel XP. ===================== I've had a frustrating time with DSUM on a workbook I'm building from someone else's model - I keep getting #VALUE! errors. After lots of looking "underneath the hood" I think I see the problem, but don't know how to fix it. One of the formulas is: =DSUM(Revenue,'Revenue Projects'!$D$3,B1:B3) The problem comes up with "Revenue". When I use the Evaluate Formula tool, it italicizes "Revenue", and when I click on Evaluate, I see 'Revenue Projects'$A$1:$J$14 in it's place - italicized. I click Evaluate again and get the #VALUE! error. When I took a hard lok at this, I think the problem is that the worksheet ('Revenue Projects') refered to by the database (Revenue), has the WRONG range of cells. It should be $A$1:$O$19. How do I change this "inside" Excel? (This is the only thing I can find that might be causing this error.) -- Mike Webb Platte River Whooping Crane Maintenance Trust, Inc. a 501 (c)(3) conservation non-profit organization |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference Data Range based on cell contents | Charts and Charting in Excel | |||
Reference range of cells and perform subtraction or nothing depending on contents | Excel Worksheet Functions | |||
Using a NAME as a reference in another range | Excel Discussion (Misc queries) | |||
Using a formula to create named range reference | Excel Worksheet Functions | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions |