barcode - How can I read the content of a data matrix into multiple cells in excel

I want to use a barcode-scanner (+ data matrix) to perform an input in an existing excel file (*.xlsx). The content of the scanned code should be devidied into two cells, split accoring to a tabulator symbol.I have generated the following data matrix with the content Hans<tab>Müller where <tab> equals the decimal character code of 9 in ASCII. Scanning the matrix in Notepad++ (focus in Notepad, scanning the matrix) results in the correct result. But if I try the procedure in excel (selecting a cell, scanning the matrix), the input is...Read more

excel 2010 - Datatype conversion failed for measure in PowerPivot workbook

I have a problem, during importing data from cube using PowerPivot for Excel.When I import measures to the PowerPivot workbook, it treats numbers as text. When I try to change datatype to decimal number, I get following error:The following system error occurred: Type mismatch. PowerPivot database error: Datatype conversion failed for [Table: 'Query', Column: '', Value: '6198.9357860000009'].I'm trying to create pivot table with these values, but while it treats numbers as text, I'm not able to create it correctly. Does anyone know how to dea...Read more

excel 2010 - Link PowerPivot slicer to pivot tables based on different data sources

I'm new to PowerPivot and have been asked to link multiple pivot tables pulling from multiple PowerPivot data sources.Looking at blog posts and forum discussions it seems to be possible to either link based on a one to one or one to many relationship and also - if following the recommended models - linking via many to many relationships.The data sources have primary/foreign key fields which I have used to create a relationship within PowerPivot.I have used PivotTable Connections to link a slicer to pivot tables from both data sources. But, when...Read more

excel 2010 - conditional formatting not working

(am using PHPExcel 1.7.8)I create a sheet and populate it with a table of numeric data. Then Excel refuses to apply conditional formatting of the 3-color-transition type to the numeric data.That means, too:1) I can use conditional formatting of the "top 10 values" kind2) If I populate right next to the generated table few cells with numbers then I can use the cond formatting of transitional kind3) If I copy two cells from the generated table somewhere else in a simple copy/paste fashion still can't use the transitional formatting4) If I copy tw...Read more

excel 2010 - updating data base of a chart or pivot table in an XLSX using PHPExcel

UPDATE:Just tested this scenario with PHPExcel 1.7.8 and it is still not working. Pivot tables in sheets that are not actively touched through PHPExcel simply vanish and what is left is the old numbers surrounded by borders.I have an Excel 2007 workbook with two sheets:dashboarddataIn 'data' there is just an aggregation of tabular organized data and in 'dashboard' is a simple chart and a pivot table based on the data in 'data'.What I want to do is update the data in the 'data' sheet using PHPExcel. So far no problem. But when I open the workboo...Read more

datasource - Excel creates new sheet from invisible data in pivot table cell

I have been sent a pivot table in excel 2010, and the data in the cell is linked to data not visible on any of the sheets when double clicked. When the cell is clicked on a new sheet is created with data not previously visible on any sheet. However, when I use the pivot table tools to search for the data source it leads me to data in one of the visible sheets and does not allow me to click on the external data source section. I would like to create something like this/ understand how the cells are simultaneously linked, as I do not understand w...Read more

Excel Conditional Formatting date within x days

I have one date in C4. And I have dates calculated in Column I. I am trying to highlight with conditional formatting any date in column I that is before the date in C4 and within the next 45 days of today.Criteria 1: Date in Column I is sooner than date in cell C4Criteria 2: 45 days or less from today = TrueThis is the formula I have so far, but can't seem to get working correctly - =AND(C4>TODAY(), C4-TODAY()<=45)I can get the formula to work on a single cell as follows:=AND($C$4>$I$13, $C$4-TODAY()<=45)I tried to copy down, but th...Read more

Excel formula do not update automatically

I have created a simple excel formula to know what is the color of the font of the cell A1Function GetFontColorIndex(elrango As Range) As Integer Application.Volatile GetFontColorIndex = elrango.Cells(1, 1).Font.ColorIndexEnd FunctionIn excel-2010:File > Options > Formulas > Workbook Calculation > Automatic is checkedIn cell A1 I have a number, and in cell A3 I have =GetFontColorIndex(A1)but when I change the font of cell A1, the formula does not update automatically on cell A3. I have to click shift+F9 and then it works.Any idea w...Read more

excel 2010 - Conditional formatting, entire row based

I've searched and read through answers related to conditional formatting, but I can't seem to get mine to work, so maybe I'm doing something wrong.I have a worksheet for work. It contains a list of animals in our shelter. What I'm attempting to do is color the entire row green if they've been adopted (noted by an "X" in column "G"). I've had =$G$2="X" and =$G2="X", but neither work. It'll only color the one row that was active when I set the rule, and when I enter "X" in another row, it does nothing. What am I missing?...Read more

Excel 2010 Macro does not find value

I have a macro that searches but does not find the “7” (If Right(pair, 2) = 7 Then). The thing is when I change the number to 11 or 12 etc. (any two digits) and the findXX in the code, it works fine. Does anyone know what’s occurring and what is the exact change I need to do.Option ExplicitSub DivideSomeStuff()Dim pair As Range, accumulator As RangeDim findSeven As DoubleDim remainder As LongFor Each pair In Range("B30, F30, J30") If Right(pair, 2) = 7 Then If pair.Offset(0, 2) <= 12 Then remainder = 0 Else ...Read more

excel 2010 - Getting #NUM! for outer nests of IF statement division in PowerPivot measure

I have some sales figures for which I have values in pounds, units, and dollars. From PowerPivot, I'm creating a pivot with Periods columns (Current Week, MTD, QTD, YTD) and sub-columns of "Measure" (Dollars, Pounds, Units). The calculation I'm using gets the percent difference between this year's value for the given Period and Measure, and last year's value for the same Period and Measure, e.g., ([MTD This Year]-[MTD Last Year])/[MTD Last Year].To do this, I've added in PowerPivot next to my Data table, a one-column Period table and a one-colu...Read more

excel 2010 - Import custom Atom feed in PowerPivot

Question: I have created a sample xml document containing data conforming to the atom 1.0 schema. When I import the contents of this file (for testing purposes) in PowerPivot, it creates columns for each atom element in each entry, instead of creating a column per content element. Why is this?Background: A customer wants to import data from a web service which provides a feed that uses a custom XML schema that is not supported by PowerPivot. The service provides the ability for the caller to supply an XSLT template that will be applied to the f...Read more

SSAS error message in Excel when adding new dimension

I have a cube in SSAS that work well. I access the cube via Excel/connection and create a pivot table.But when I add one new specific dimension into it I am having problem:As soon as that new dimension is in the cube, I can still create a new connection to it, but when I close the file and reopen it I got the following message:"Excel encountered unreadable content in [filename.xlsx]."Then the connection doesn't work anymore and the pivottable is empty, we have to recreate the connection and the pivot table... all working well until we close the...Read more

excel 2010 - Power Pivot Sum across factTables

I'm having trouble creating a sum across multiple factTables in Power Pivot for Excel 2010. Here's a simplified layout. Network A - Date, Product Name, RevenueNetwork B - Date, Product Name, Revenue Network C - Date, RevenuedimCalendarThey are all related via dimCalendar. Network A and B have multiple products that are unique to each network. Each network has a measure to sum its revenue, for example: Network A - Revenue:=SUM([Revenue])I have created a Pivot Table that has columns for each network's revenue and rows for the date. How can...Read more