#VALUE! when using defined name

HELP!!!
Problem: receive #VALUE! Using a defined NAME range 
In workbook MonthlyBudget09Mstr.xlsx are sheets 1209Sch and 1209
1209Sch contains all monthly entries, 1209 is the summary sheet. 

Defined name: HousingItems, ='1209Sch'!$L$83:$L$88, Values: 
{"1";"2";"3";"4";"5";"15"} Workbook <<from Name Manager  
L88 is a SUM function but I have remove L88 from range and still have problem

Placing =HousingItems in cell B4 of sheet 1209 results in #VALUE! Error
B4:B9 are empty

Can copy and then paste 1209Sch L83:L88 into B4 which fills B4:B9 with 
correct values.

Please advise what I am doing wrong
Thanks
Rick
0
Utf
12/2/2009 3:25:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
559 Views

Similar Articles

[PageSpeed] 37

HousingItems is an *array* so you'd have to select a range of cells the same 
size as HousingItems then array enter.

If you're just doing a simple cell link try this in B4 then copy down as 
needed:

=INDEX(HousingItems,ROWS(B$4:B4))

-- 
Biff
Microsoft Excel MVP


"WAMTC" <WAMTC@discussions.microsoft.com> wrote in message 
news:AB9ED899-D497-425E-95B6-2582C698F669@microsoft.com...
> HELP!!!
> Problem: receive #VALUE! Using a defined NAME range
> In workbook MonthlyBudget09Mstr.xlsx are sheets 1209Sch and 1209
> 1209Sch contains all monthly entries, 1209 is the summary sheet.
>
> Defined name: HousingItems, ='1209Sch'!$L$83:$L$88, Values:
> {"1";"2";"3";"4";"5";"15"} Workbook <<from Name Manager
> L88 is a SUM function but I have remove L88 from range and still have 
> problem
>
> Placing =HousingItems in cell B4 of sheet 1209 results in #VALUE! Error
> B4:B9 are empty
>
> Can copy and then paste 1209Sch L83:L88 into B4 which fills B4:B9 with
> correct values.
>
> Please advise what I am doing wrong
> Thanks
> Rick 


0
T
12/2/2009 4:29:46 AM
Biff
Thxs it worked and I understand to way it works.
Rick

"T. Valko" wrote:

> HousingItems is an *array* so you'd have to select a range of cells the same 
> size as HousingItems then array enter.
> 
> If you're just doing a simple cell link try this in B4 then copy down as 
> needed:
> 
> =INDEX(HousingItems,ROWS(B$4:B4))
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "WAMTC" <WAMTC@discussions.microsoft.com> wrote in message 
> news:AB9ED899-D497-425E-95B6-2582C698F669@microsoft.com...
> > HELP!!!
> > Problem: receive #VALUE! Using a defined NAME range
> > In workbook MonthlyBudget09Mstr.xlsx are sheets 1209Sch and 1209
> > 1209Sch contains all monthly entries, 1209 is the summary sheet.
> >
> > Defined name: HousingItems, ='1209Sch'!$L$83:$L$88, Values:
> > {"1";"2";"3";"4";"5";"15"} Workbook <<from Name Manager
> > L88 is a SUM function but I have remove L88 from range and still have 
> > problem
> >
> > Placing =HousingItems in cell B4 of sheet 1209 results in #VALUE! Error
> > B4:B9 are empty
> >
> > Can copy and then paste 1209Sch L83:L88 into B4 which fills B4:B9 with
> > correct values.
> >
> > Please advise what I am doing wrong
> > Thanks
> > Rick 
> 
> 
> .
> 
0
Utf
12/2/2009 2:33:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"WAMTC" <WAMTC@discussions.microsoft.com> wrote in message 
news:8F18571B-E26C-4DBD-9CA7-EA3E6A9710FC@microsoft.com...
> Biff
> Thxs it worked and I understand to way it works.
> Rick
>
> "T. Valko" wrote:
>
>> HousingItems is an *array* so you'd have to select a range of cells the 
>> same
>> size as HousingItems then array enter.
>>
>> If you're just doing a simple cell link try this in B4 then copy down as
>> needed:
>>
>> =INDEX(HousingItems,ROWS(B$4:B4))
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "WAMTC" <WAMTC@discussions.microsoft.com> wrote in message
>> news:AB9ED899-D497-425E-95B6-2582C698F669@microsoft.com...
>> > HELP!!!
>> > Problem: receive #VALUE! Using a defined NAME range
>> > In workbook MonthlyBudget09Mstr.xlsx are sheets 1209Sch and 1209
>> > 1209Sch contains all monthly entries, 1209 is the summary sheet.
>> >
>> > Defined name: HousingItems, ='1209Sch'!$L$83:$L$88, Values:
>> > {"1";"2";"3";"4";"5";"15"} Workbook <<from Name Manager
>> > L88 is a SUM function but I have remove L88 from range and still have
>> > problem
>> >
>> > Placing =HousingItems in cell B4 of sheet 1209 results in #VALUE! Error
>> > B4:B9 are empty
>> >
>> > Can copy and then paste 1209Sch L83:L88 into B4 which fills B4:B9 with
>> > correct values.
>> >
>> > Please advise what I am doing wrong
>> > Thanks
>> > Rick
>>
>>
>> .
>> 


0
T
12/2/2009 4:42:46 PM
Reply:

Similar Artilces:

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Indirect Method for Name Box Variable? #2
DARN The reason I want to do this is that I have to collect data on a dail basis but I have to display it on a graph on a weekly basis. I select the entire week of cells and give it a name like week52. The following week I have to select the entire week and name i week53. Everything is set up that I can just copy and the numbers will progres for whatever length of time I need to graph (months, years, etc) excep this. Maybe I'll just go back to using the range of cell numbers. Thanks guys -- Chris Brenna ----------------------------------------------------------------------- Chris Bre...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

If a worksheet name is = to test then a msgbox appears
I'm looking for a macro that will display a msgbox if a worksheet is = to test. For example, if the name of a sheet in a workbook is equal to test then display msgbox saying sheet already exists. Thanks Vick dim ws as worksheet set ws = nothing on error resume next set ws = worksheets("test") on error goto 0 if ws is nothing then msgbox "doesn't exist" else msgbox "already exists" end if Vick wrote: > > I'm looking for a macro that will display a msgbox if a worksheet is = to test. > > For example, if the name of a sheet in a w...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

y value vs x
In an xy scatter plot one can choose the type of line connecting the data points (smooth, straight, etc.). Once this is done, Is there a simple way of determining the y value of graph for a specific x value without doing successive approximations with 0 shifts. I'd rather not purchase a graphing program just for this simple task. You could find an equation that fits the data (see trendline) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ralph" <ralph@discussions.microsoft.com> wrote in message news:284B39DE-20C6-40CB-AB60-39B...

Combo Box initial values question
Does anyone know how to make a combo box show a value when a sheet opens? Mine are always blank when I open them until I select a value. thanks tp Hi Teepee, Try something like: Me.ComboBox1.ListIndex = 0 --- Regards, Norman "teepee" <teepee@noemail.com> wrote in message news:4645ed29$1@newsgate.x-privat.org... > Does anyone know how to make a combo box show a value when a sheet opens? > Mine are always blank when I open them until I select a value. > > thanks > > tp > > thanks for trying. says 'invalid use of me keyword.&...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...