Dynamic cell references in cube functions

What is the syntax to make for instance the cubevalue function pick up 
parameter values from the spreadsheet so my report becomes dynamic? 

I have the following statement: 
=CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[2007].[200710]")

I want to make a dynamic report so that the year [2007] and the month 
[200710] changes according to dates I put into the spreadsheet (for instance 
today()) so the report automatically updates the query to current time 
periode.

Thanks in advance!
0
Gunder (3)
11/2/2007 11:40:01 PM
excel 39879 articles. 2 followers. Follow

3 Replies
1019 Views

Similar Articles

[PageSpeed] 25

I don't know cubevalue and it isn't in Excel help on my machine. The normal 
way to would be to refer to a value in a cell.

Assuming =TODAY() is in A1.
You may be able to replace 2007 with YEAR(A1) or you may have to put 
=YEAR(A1) in eg B1 and replace 2007 with B1.
Similarly you may be able to use CONCATENATE(YEAR(A1),MONTH(A1)) in place of 
200710 or you may have to put =CONCATENATE(YEAR(A1),MONTH(A1)) in eg C1 and 
replace 200710 with C1.

Ian

"Gunder" <Gunder@discussions.microsoft.com> wrote in message 
news:D7CE4A3A-DB77-478E-8C05-6034C9A63077@microsoft.com...
> What is the syntax to make for instance the cubevalue function pick up
> parameter values from the spreadsheet so my report becomes dynamic?
>
> I have the following statement:
> =CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[2007].[200710]")
>
> I want to make a dynamic report so that the year [2007] and the month
> [200710] changes according to dates I put into the spreadsheet (for 
> instance
> today()) so the report automatically updates the query to current time
> periode.
>
> Thanks in advance! 


0
ic2 (12)
11/3/2007 2:38:36 PM
Thanks for your input.

The problem is that the cube statement is within " " so excel see it as 
text. I have to make it dynamic and at the same time convert it to text for 
the cubevalue function to understand it. That is what I do not manage.

Clearer now?

Gunder 
------------

"Ian" wrote:

> I don't know cubevalue and it isn't in Excel help on my machine. The normal 
> way to would be to refer to a value in a cell.
> 
> Assuming =TODAY() is in A1.
> You may be able to replace 2007 with YEAR(A1) or you may have to put 
> =YEAR(A1) in eg B1 and replace 2007 with B1.
> Similarly you may be able to use CONCATENATE(YEAR(A1),MONTH(A1)) in place of 
> 200710 or you may have to put =CONCATENATE(YEAR(A1),MONTH(A1)) in eg C1 and 
> replace 200710 with C1.
> 
> Ian
> 
> "Gunder" <Gunder@discussions.microsoft.com> wrote in message 
> news:D7CE4A3A-DB77-478E-8C05-6034C9A63077@microsoft.com...
> > What is the syntax to make for instance the cubevalue function pick up
> > parameter values from the spreadsheet so my report becomes dynamic?
> >
> > I have the following statement:
> > =CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[2007].[200710]")
> >
> > I want to make a dynamic report so that the year [2007] and the month
> > [200710] changes according to dates I put into the spreadsheet (for 
> > instance
> > today()) so the report automatically updates the query to current time
> > periode.
> >
> > Thanks in advance! 
> 
> 
> 
0
Gunder (3)
11/3/2007 3:53:00 PM
Perhaps something like

=CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[" & B1 & 
"].[" & C1 & "]")

based on yyyy in B1 and yyyymm in C1 as in my last post.

If this (or something very similar) doesn't work, then I'm afraid I don't 
know. As I said, cubevalue is an unknown function to me.

Ian

"Gunder" <Gunder@discussions.microsoft.com> wrote in message 
news:BF167E5C-2C3F-46F3-8F8D-286A616CEC09@microsoft.com...
> Thanks for your input.
>
> The problem is that the cube statement is within " " so excel see it as
> text. I have to make it dynamic and at the same time convert it to text 
> for
> the cubevalue function to understand it. That is what I do not manage.
>
> Clearer now?
>
> Gunder
> ------------
>
> "Ian" wrote:
>
>> I don't know cubevalue and it isn't in Excel help on my machine. The 
>> normal
>> way to would be to refer to a value in a cell.
>>
>> Assuming =TODAY() is in A1.
>> You may be able to replace 2007 with YEAR(A1) or you may have to put
>> =YEAR(A1) in eg B1 and replace 2007 with B1.
>> Similarly you may be able to use CONCATENATE(YEAR(A1),MONTH(A1)) in place 
>> of
>> 200710 or you may have to put =CONCATENATE(YEAR(A1),MONTH(A1)) in eg C1 
>> and
>> replace 200710 with C1.
>>
>> Ian
>>
>> "Gunder" <Gunder@discussions.microsoft.com> wrote in message
>> news:D7CE4A3A-DB77-478E-8C05-6034C9A63077@microsoft.com...
>> > What is the syntax to make for instance the cubevalue function pick up
>> > parameter values from the spreadsheet so my report becomes dynamic?
>> >
>> > I have the following statement:
>> > =CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[2007].[200710]")
>> >
>> > I want to make a dynamic report so that the year [2007] and the month
>> > [200710] changes according to dates I put into the spreadsheet (for
>> > instance
>> > today()) so the report automatically updates the query to current time
>> > periode.
>> >
>> > Thanks in advance!
>>
>>
>> 


0
ic2 (12)
11/3/2007 5:28:45 PM
Reply:

Similar Artilces:

Cell formatting behaviour question
Hey guys, I have 2 related cases that I dont understand how to get Excel formatting to work: 1. If I imported or copied rows of data (numerical) from another file (Word or Access) into Excel, the data is presented as raw numbers in each cell. Now I apply a formatting (i.e. I want comma separation for thousands, etc.) and it will not show up. However, if i then double-click inside a cell (as if to edit the contents directly), then hit enter the formatting I want shows up... but only that cell. I have hundreds of rows and I really need to force excel to display the formatting i want immediately...

Hide multiple rows when particular cell is zero
Hi All, Can anyone tell me how I can hide multiple rows when a particular cell is zero? It has to go automaticly, so no buttons... When D37=0, hide rows 37-48 When D49=0, hide rows 49-58 When D59=0, hide rows 59-68 When D69=0, hide rows 69-78 When D79=0, hide rows 79-88 etcetera. How can help me with this macro?? Thanks in advanced. Best regards, BL Hi, What should happen if e.g. D38 = 0? Maybe this applied to the desired sheet: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim a, rng As Range Set rng = Range("D37:D1000") For Each a In rng ...

Excel Function VLOOKUP
Hi, I'm having trouble looking up a table of "Names". The table deifene below is called ROL_IS and list hundred of other defined tables. Error where: =VLOOKUP(A33,VLOOKUP(CUR_MON,ROL_IS,3,FALSE),2,FALSE) but no errors where =VLOOKUP(A33,ACT_IS,2,FALSE) or =VLOOKUP(CUR_MON,ROL_IS,3,FALSE) ANS: ACT_IS The array function in VLOOKUP appears not to converting formula to NAME. If anyone knows or needs further details if would be appreciated. Thanks Elizabet -- Message posted from http://www.ExcelForum.com If I understand you correctly =VLOOKUP(A33,INDIRECT(VLOOKU...

Xpath / XPathExpression equivalent of SQL's "Top" function
Is there one? Also in either xpath or XPathExpression whats the best way to get the full xpath of a node. As i am using xpath query of (//books/.......) So I would like to see what is before the /book tag? > Is there one? Yes you can do /books/book[position()<6] That will select the first 5 book elements in the books node. > As i am using xpath query of (//books/.......) > > So I would like to see what is before the /book tag? Hmm you can get the parent node once you get the child I guess. -- Victor Hadianto http://www.synop.com/Products/SauceReader/ "ree32"...

Referencing only certain cells in a table
Hello, I posted this question earlier in the execl.worksheet.functions group, but did not get an answer. I apologize for the repost. I need some help referencing certain cells from one worksheet in another. Here is an example of what I am trying to accomplish: Worksheet 1: Item # Name Value 1 item1 100 2 item2 0 3 item3 0 4 item4 54 5 item5 0 6 item6 0 7 item7 70 I need to create a separate table in Worksheet2 that as only the NON ZERO values from the table above in Worksheet 1. S...

adding number only if CDN is displayed in next cell
I have a list of sales for Canadian and US Customers. I marked in the column besides the sales amount if it is CDN or US. How can I add the CDN or the US Sales only. Thanks for your help. Use SUMIF() See HELP for details -- Kind regards, Niek Otten "ED" <ED@discussions.microsoft.com> wrote in message news:7B61595A-17DD-4254-A46F-A8D26BF0D9AB@microsoft.com... >I have a list of sales for Canadian and US Customers. I marked in the >column > besides the sales amount if it is CDN or US. > > How can I add the CDN or the US Sales only. > > Thanks for y...

Close Database Function?
I want to use task scheduler to open an Access DB, run a macro that updates a table from an external source, then close the DB and Access. I do not use any forms on this database, just one table and one macro. So I guess my needs are as follows: 1. Automatically execute 'My_Macro' when the DB is opened from Task Scheduler. My Scheduled Task uses the /X switch to specify and execute the Macro - my main problem is determining how to programatically close the DB and Access. I would like to continue using the command line method of executing the macro if possible so that I can m...

checking input in a cell and return by copying info from a other c
What I want to do is the following. I have a worksheet in which I have created the following; Name-Column , select-Column. I now want to created a formula in a new sheet which check if the are "x" in the select column and copy the names of the name-columns to cells in that new sheet. (which is in the same workbook) is that possible? If so please let me know. Thanks in advance. To find a single "x" you would use VLOOKUP(). For all the "x"'s, see: http://office.microsoft.com/en-us/excel/HA012260381033.aspx -- Gary''s Student - gsnu20...

Averaging List with Blank Cells
Hi, Someone gave me this formula to average the most recent five items in a column of numbers: =AVERAGE(OFFSET(B2,COUNTA(A:A)-5,0,5,1)) It works great unless the list has blank cells. In that case it does not work. 1. 6 2. 3. 7 4. 9 5. 6. 2 7. 8. 1 9. 23 10 4 How can I make it work when the columns have some blank cells? Thanks, Frank Try =IF(count(A1:A10)=0,",Average(A1:A10)) >-----Original Message----- > >Hi, > >Someone gave me this formula to average the most recent five items in >a column of numbers: > >=AVERAGE(OFFSET(B2,COUNTA(A...

Pivot table overwrites neighbouring cells #2
Hello, I have multiple pivot tables on the same worksheet to allow for easy comparison of data. The source data is based on the week and expected to be refreshed daily. As the week progresses the size of the pivot tables will grow and overwrite data below it. I am looking for a workaround to this problem or if there is a way to set the pivot table to insert a new row below it if the size will increase and before data is updated. Thanks in advance, Anissa Hi, There is no feature of pivot tables to do what you wish. You pretty much have two options that I can think of: 1. Prepare e...

Splitting Text from single cell in column across multiple Columns
Text To column wont work because I have name and address info in a single cell in this format. Each cell has different data representing different addresses. Name St # St Name Phone Number City, State zip I want a formula or something to take first line and put in one column, the 2nd line in another column and the 3rd line spread across 3 columns. Although if you could just find a way to split each line into a column, that would work. Once again, Text to colun won't work, I have multiple cells with diffeent addresses, they are not all the same and there are no deli...

Passing an Array created in a Function or Subprocedure back to the calling Subprocedure
I want to have a Function or Subprocedure that can create an array with 52 specific values in it. I then want to call this Function or Subprocedure from a main Subprocedure and be able to access the contents of the array. Everything can be of type variant for the purposes of this exercise. Can anybody help, please? This worked ok for me: Option Explicit Sub testme() Dim myArr As Variant Dim iCtr As Long myArr = BuildArray(myArr) For iCtr = LBound(myArr) To UBound(myArr) MsgBox iCtr & ". " & myArr(iCtr) Next iCtr End Sub Function Buil...

Is there any way of calculating a running total within a single cell in a column
Can anyone help with this query? e.g. I want to work out how many people are working at any time in a 24 hour period. The number of people rostered on differs each day of the week. Monday Time No. rostered on 6.00 2 7.00 2 8.00 2 9.00 2 10.00 (and so on) Can I add additional staff number in my 'no. rostered on column' by just entering the number into the cell. Say, if there were an extra 2 staff starting their 8 hour shift at 9. am. is there a way of just typing in '2' into that cell and getting '4'? What I do at the moment ...

Search function inoperative in Entourage address book
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3342799543_1520007 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit My search function is no longer working in the Entourage Address Book. Initially, large sections of my contact list would simply vanish, allowing me to scroll through long lines of blank space. After rebuilding my database, contacts don�t vanish but the search simply doesn�t do anything. If I type in the �Name Contains...� search...

Custum Cell Format
I have an array formula which automatically summarizes all available values of a certain column, in ascending order. The summary is fixed to a maximum of 8 cells and in many cases it uses less than the available 8 columns. Is it possible to format the cells by showing a 0 when the result/cell is empty In other cells in the sheet the cells show empty instead of a zero by using the custom format of 0;-0;"" Now I want an empty cell to show 0 Thanks, W -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ----------------------------------------...

How can I update endnote cross-references in Word 2007 ?
I created a long document with many endnotes and some cross-references to these endnotes. When I insert a new endnote somewhere in the document, the following endnote numbers change but not the cross-reference numbers. This seems to be a bug in Word 2007 as it worked perfectly in my previous Word version. How can I update endnote cross-references in Word 2007 ? Ctrl+A, F9 should work in all versions of Word. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Joe56" <Joe56@discussions.microsoft.com> wro...

How do I draw a circle around a cell I want to highlight
How do I draw a circle around a cell I want to highlight - I want to see the text within the cell and colour the circle red. I tried inserting a circle from the 'drawing toolbar' but I couldn't see the text in the cell. i have seen Excel worksheets with this feature and would like to emulate it. Thanks, Janev See Tools/Formula checking/Display Formula checking Toolbar/Circle unvalid values Regards, Stefi „Janev” ezt írta: > How do I draw a circle around a cell I want to highlight - I want to > see the text within the cell and colour the circle red. I tried > ...

Dynamic cell references in cube functions
What is the syntax to make for instance the cubevalue function pick up parameter values from the spreadsheet so my report becomes dynamic? I have the following statement: =CUBEVALUE("datasource";"[Measures].[Volume]";"[Time].[YM].[YM].[2007].[200710]") I want to make a dynamic report so that the year [2007] and the month [200710] changes according to dates I put into the spreadsheet (for instance today()) so the report automatically updates the query to current time periode. Thanks in advance! I don't know cubevalue and it isn't in Excel help on ...

All cell contents within a range of cells turns white if Sheet1!A1 = 1
If the return of Sheet1!A1 = 1, I need all fill, font and line colors to turn white from A1:E10. If the value of Sheet1!A1 changes to 0, all colors must return to normal. Does anyone have any thoughts on this? Thanks for any suggestions. Michael If your range to format is on Sheet1............. Conditional Formatting Select A1:E10 Format>CF>Formula is: =$A$1=1 Format to suit If range to format is on a sheet other than Sheet1 Select Sheet1!A1 and give it a name..............insert>name>define On other sheet select range A1:E10 and Format>CF Form...

how do I create a macro to select all colored cells in a worksheet
Need help writing the code for an Excel Macro that would find all cells that have any interior color so that an outer border can be added around all colored cells. Hi, Try this. I've included the code to add the borders but you can delete this if you want to do it manually. Not this will not work for conditionally formatted coloured cells Sub sonic() Dim CopyRange As Range For Each c In ActiveSheet.UsedRange If c.Interior.ColorIndex <> xlNone Then If CopyRange Is Nothing Then Set CopyRange = c Else Set CopyRange = Union(C...

How to link cells only if...
In cell a1 I have an option for an "y" "n". Based on that cell I want to link cell b1 to cell d1. If it is "Y" then link it, if it is "n" then I want it to go to cell b1 and check it and so forth. I only want to link the items in column "b" that have a "y" next to them. I can do this for one cell and even skip and go until I find the first "Y". What I can not do is make the second link start after it has found the first "y" in column "A". Can anybody please help. This is the 3rd time I am asking f...

Preserving lock on cells even when copied and pasted within a sheet
(Sorry for repeating this post from another group; I inadvertently neglected adding both groups in my original post in microsoft.public.excel.misc. In any case, I found a couple of minor errors and omissions that I have corrected :-P.) Hi, I am trying to create a *protected* worksheet that is flexible enough so that additional rows can be created (by copying existing rows from above) if additional data becomes available to be entered. Locking rows with formulas and protecting the sheet does allow the user to copy and paste the cells or rows (most often accomplished by dragging by the handl...

Put result in different cell
Because the formula is in cell C4, the result appears in cell C4. How can I have the result put into a different cell? ...

Cannot Choose cells (or ranges) after column S
With Ctrl + A, I can use highlight Column A - R. And cells in Column S and after cannot be chosen by mouth. Do you know what is the reason? Thanks. Hi try hitting CTRL+A twice. This behaviour depends on your Excel version. The first CTRL+A selects the current used range -- Regards Frank Kabel Frankfurt, Germany plumstone wrote: > With Ctrl + A, I can use highlight Column A - R. And cells in Column > S and after cannot be chosen by mouth. Do you know what is the > reason? Thanks. ...

seeing the formula (not the cell)
Hi All, Suddendly, I have this strange behaviour. I have a cell say C3 which contains 2b as a text. Then on cell I4, I want to se the content of cell C3. on cell I4, I used to type =C3, then on I4, I would see 2b. What is strange now, is that on I4, I see =C3 (the "formula" and not the result). What is more surprizing, is that on this excell file, I have 2 sheets. On the first sheet, referencing works fine. On the second sheet, I can see only the formula. What do I do to not see the formula, but the resut ? I don't remeber touching any option. Regards Jean Simon Your cel...