I maintain a number of models requiring use of multiple OLAP Cubes in the same Excel workbook. I'm now using Excel 2007.
While I don't get this problem in Excel 2003, in Excel 2007 the Cubes often lose their connection to the source file. I'm given the error:
"Initialization of the data source failed. Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database."
When I attempt to recreate the connection, or select a new connection pointing to the same OLAP cube I'll often get the same error, but not always. I've tried using a separate connection, pointing to the same OLAP Cube and this also does not solve the problem.
how I can resolve this issue? Or do I just need to move my models back into Excel 2003?
I use Excel to query a SSAS OLAP Cube, the return of which I then model into various outputs. I do not have access to amend the cube.
I am hoping it is possible to be able to add custom calculated members to some dimensions in excel rather than on the server, but I'm struggling to find any resources that explains how it is done or if it is even possible. For example, the cube will return the dimension 'Source Country' with members 'England', 'Northern Ireland', 'Scotland', 'Wales', I would like to add a calculated member 'United Kingdom' to consolidate all those members.
Is this possible or should I just look to calculate this consolidation by formulas in the model.
I am working now on macros changing the datas in a pivot table created from a OLAP cube. I have there some hierarchic dimensions in the format of Category.Group.Item etc. When creating a macro with recorder i get something like this...
Is somewhere out there any tutorial to that [All Dim_Item] thing? ... cause i feel there IS what i need, that with some cunning command i could show an item without knowing his group and category, but i cant get the macro working.
i have generated a olap cube and display it using pivot table. But i am unable to sort my data from acsending order. For example: I had right clicked on my pivot table > field settings > Advanced > Select "Ascending". The data (sum of quantity) remains unsort after i have checked ok.
I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.
Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.
In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.
Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...
'Sub PivotAnalysis() ' Dim myPivotTable As Excel.PivotTable Dim myPivotField As Excel.PivotField Dim myPivotItem As Excel.PivotItem
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.
" JOES TOWING SERVICE [0123451] 123 STREET
CALEDONIA FL 12345 " "BUBBA / SHOP ACCURATE AUTO SUPPLY INC [0234567] 21234 HYDRAULIC DR
I just installed excel 2007 and encounter a strange problem. When I open 2 or more excel instances from windows explore by double-clicking excel icons, those instances are not linkable, they seem absolutely independent to one another (in View/switch windows only sees one workbook). However when I open second file from within the first instance (Ctrl+O), I see 2 in the view/switch windows and it works fine.
I have use excel 2007. In our sheet i have in cell "B3" product name i.e. "abc" , "xyz" etc. That is called sales of goods.
In cell "C3" i have number of quantity If "B3" is "abc" then only calculate below :
1st option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=15 & cell "c3" is<19 then message shown as "one mobile free"
2nd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=20 & cell "c3" is<39 then message shown as "two mobile free"
3rd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=40 then message shown as "three mobile free"
I am using Excel 2007 and am attempting to write multiple IF statements within a cell. This multi-cell validation would be used to validate that A=B and C=D but A&B do not equal C&D as illustrated below:
I have Excel 2007, and I have column 1 with a thousand entries "HC 14-0001" all the way down to "HC 14-1000". I have on my hard drive 1000 foldiers named the exact same "HC 14-0001 to HC 14-1000". I want to create a Hyperlink from each item in that column to its corrisponding folder on the hard drive. I can easily create the Hyperlink, but I want to find someway to copy them. I can create the first two rows... HC 14-0001 and HC 14-0002 and create good Hyperlinks to the folders, but when I highlight them and drag them down, the excel spreedsheet is updated all the way to HC 14-1000, but the Hyperlinks do not change.
How can I get the Hyperlinks to change? I don't want to spend the next week creating Hyperlinks for a 1000 entries on this spread sheet, and I have 5 other spreadsheets exactly the same.
I'm looking to simply find a function that allows me to lookup a certain value in a table of data and return the value in a specific column. So if I looked up the current date, 6/27/2011, it would return the week it is in (26). I dont want to be limited to just the monday column however, as the dates I will be throughout the entire week. Is there an easy way to do this in excel 2007?
I am using Windows xp and Excel 2007. I'm creating a user form and have a vlookup formula that goes to a table and looks up calories and sodium based on the text entered by the user. My problem is that I want to be able to populate the 3 columns with information and then sum the values that are returned to the Calories and Sodium columns. Is that possible with Vlookup?
Insert - Object allows me to insert a object (display as icon is used). Is it also possible to insert multiple objects in one go? Via this option I can always only select 1 object.
I am having cell return blank if no value is found. it is currently looking only at one sheet.
I don't want it to combine the values like this formula does. =(VLOOKUP(Q2,'sheet3'!$D$2:$K$200,7,FALSE)&","&VLOOKUP(Q24,'sheet2'!$D$2:$K$1807,7,FALSE)) #.###,##.##
sheet 1 is where the cell I am writing the formula is in, and I want to look in sheet 2, 3, and possible 4 for a value, I want to only have the greatest value show between the sheets. is this possible.
I'm trying to create a sheet in Excel 2007 that organises shipments, the file is normally pretty large so I want to create a macro which does a few things, so when printed it's easier to work with. First one is to make a gap when items change in Column B and Column F, I can use the below code to make the gaps for one column but not both without making a separate macro. Is there a way to make the gaps on changes in columns B & F on one macro? (I have an example nearer the end)
Dim Rng As Range Dim x As Long Set Rng = Range("F12:F" & Range("F65536").End(xlUp).Row) For x = Rng.Rows.Count To 2 Step -1 If Rng.Cells(x, 1).Offset(-1, 0).Value Rng.Cells(x, 1).Value Then Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down End If Next x
After the data is separated I wanted to insert totals of the weights when there is a spacing, and a counter which stops after each spacing and restarts when the next items start.
Here is an example of the data I have before the Macro with Columns.
Column B Column C Column F Column I
Destination of order booking ref Size weight of orders
[Code] ........
This is how I would like the date to look like after the macro.
Column B Column C Column F Column I
Count Destination of order booking ref Size weight of orders
i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.
these workbooks all have the same name with the date at the end
example: workbook 06-01-2011 workbook 06-02-2011 all the way to workbook 12-31-2011
usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant
i have seein INDIRECT of the cell changes but nothing for the workbook name chaning
here is what i am trying to do: ='C:File Path[WorkbookX.xls]Sheet1'!A1 where the X WorkbookX is variable (the date at end of file name)
i have a list of all workbook names or just the dates and would like to do something like:
=D1&$A$1 where D1 is the full path name or ='C:File Path[Workbook"D1".xls]Sheet1'!A1 where D1 is the date name
using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.
my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks
The workbook has two worksheets "Data" & "Crunch". "Data" contains the raw data-range B1:U50. "Crunch" is the processing worksheet. Row 1 is a header row. Columns A-D are empty. Column E (rows 2-81) contain the numbers 1-80.
The macro is intended to run in "Crunch". I need to add the CountIf function in every cell/row filling the range F2:BC81. A loop does the trick for me to fill the rows in each column but I don't know how to move the loop down each column without creating a new loop.
A section of the code is below. Notice a majority of the code is the same, the copy in red is variable from loop to loop. I need to repeat this loop through column "BC" (50 times in total). My guess is there is a way to write this code one time instead of 50 times adjusting the variable components.
Code: 'Find total # of records and then store in variable totalrecords = ActiveSheet.UsedRange.Rows.Count 'CountIf Statement
I'm using Xl 07 and need to combine two sheets of contact info and have them sorted alphabetically.
Each contact entry takes the follwing form(comma's denote new columns)
55555, Name , John, Smith, Tel: , 555-555-5555 ID , Company, XXX , , Email, xxxxxx
Each sheet has a list of 200 or so entries about 400 rows long. The entries on each sheet are sorted in alphabetical order. the names also alternate alphabetically from sheet to sheet. So when i look at the entries on my final sheet, the 1st will be from sheet1 the second from sheet2 the third from sheet1 and the fourth from sheet2 etc.
I'm not sure whether I should build a new list by pulling alternating entries from each sheet or just copy one list below the other and then try to sort it somehow.
I am trying to make an excel sheet which has a 2 column drop down. For example I want to have a drop down list in column 1 and when I select "Item A" in column 2 "item A" 's phone number appears. If i select "item B" form the drop down this, "Item B's" phone number would also appear in column 2 and so on. how this is done (if possible) on Excel 2007? I was hoping it could be done from one workbook to another but If it is easier from one worksheet to another then that is fine too.
I'm using 2007 and want to add conditional formatting to multiple cells.
Say I have 3 columns and 10 rows of data (A1:10 ; B1:10 ; C1:10). I want to add an icon set to everything in columns B and C based on a comparison with the figure on their immeidate left. So B1 is compared to A1, C1 to B1 etc.
When I do it though it compares everything to A1 (formula in the Conditional Formating is =$A$1 and it won't let me remove the $'s). It looks to me like I can't do it and have to add the formatting to every cell indiviually (which is quite a lot).
I am using Excel 2007. I'd like to merge multiple sheets (about 13) into one workbook. The sheets are placed in one folder, and they all include 2 sheets, - only the first sheet should be merged into the final workbook.
The sheets will be updated every 3 months and merged again (-thus replacing the old data).
I have a workbook with 4 sheets and with sheet 5 as a "consolidated" sheet. I have to create the macro, which will on a daily basis copy the populated data from those 4 sheets and paste into the sheet 5 (same workbook). The data in the Sheet 5 should be pasted from row 2 below each other.. Row 1 will be the headings and shouldn't be replaced. The data in all 4 sheets should be copied from row starting 37, columns C to BA but it will end with different row numbers. note that columns A & B are populated but shouldn't be copied.
Writing code in VBA for splitting data into multiple sheets?
The split needs to be based on value in one of the columns and the sheet where the data will be sent to should get sheet name same as value in the column.
Following is the input sheet, the data needs to be split based on entry in the column "Job Type".
I'm currently working on a process to recategorise products for a distribution company to give a category based on successful sales transactions.
Product Code Category Month 1 Month 2
[Code].....
This isnt the actual data in case you're wondering I'm doing this with 32k lines.
So, what I want to have is categories based on sales transactions, labelled with letters. So, cat A is sales in three consecutive and top X when total of three month consecutive sales, cat B being above X number of transactions in three months not already categorised by cat A. There is a Cat C, and various others which I can do myself, but i dont know how to make the category A work.
