Search results

Vletm, Interesting. I was working to create a new file (this one is too big to upload), but when I copied the worksheet to a new workbook, the calculations in the new worksheet were correct. I guess that tells me that the worksheet is (possibly) corrupted?

I wrote a formula which was working properly until I added some additional records to the table. Now the formula calculates correctly, but at the last step it rewrites the answer to match the result of the copied cell. This is the formula: Here is the correct calculated result (after stepping...

PERFECT! That's what I needed! I've never seen the value filters used on a row label field before. Yeah, I know about that very small number ... I have a ticket in with the software vendor about the partial pennies issue. It's maddening.

https://www.dropbox.com/s/q75mhz6xie7osyj/Cash_balances_SQL.xlsm?dl=0 Hopefully that worked. If you look at the pivot tab, I have filtered the balance column to not include 0's. If you click the "local & misc. funds" slicer, everything looks good. But if you click "affordable" it looks ok until...

The lines themselves don't have 0 values, but the sum of the lines based on the fund may. That's what a pivot table is for, correct? To sum values based on criteria?

It's not power query, it's through MS query loaded to the table. The pivot table is only 2 columns ... fund & balance, so if the balance is 0 I want the entire row to not show. You did mention power query. I'm not so familiar with it, but wonder if I couldn't use power query on the table...

I have a pivot table grouped by slicers. I want to exclude 0 amounts in the groups. However, I've found that when I use filters to exclude 0's, the rows with the 0's in them are hidden, so when I click on a different slicer, the row is still hidden and anything that would have been displayed on...

https://www.dropbox.com/s/ggfczyhksl758hf/Calc%20Problem.xlsm?dl=0 The problem is in the Pivot Data worksheet. The Pivot Data (2) worksheet shows how it works on the copied sheet.

This one has me stumped. Formula reads: =IFERROR(INDEX(RawBudgetAmt,MATCH(1,((MID(RawBudgetAcct,3,3)=$A23)*(MID(RawBudgetAcct,14,6)=$B23)),0)),0) entered CSE. What I'm attempting to do is a lookup based on 2 criteria. Because I'm testing, each cell should return 100. I get 0. When I click in...

Wow. That did it. Stupid colon. :DD

Now I have a really weird problem with the password. I have a command button that unprotects the sheet, hides some rows then protects the sheet again. However, when I use the password in any other sub, or even try to enter it manually, Excel tells me my password is incorrect! Any suggestions to...

That did it. Thank you for the simple solution!

I have a workbook with a pivot table on one sheet (Pivot Budget), and a form with the "getpivottable" data on another sheet (Completed Budget). I have created a slicer for the pivot table and placed the slicer on the sheet with the form. All was well & good until I protected the sheet with the...

You just gave me the answer! Even though the ranges appear to be the same size, they aren't. I copied a formula with an =iferror(...,"") in one column to allow the sheet to grow, but did not copy that same formula in the other columns, so while they look to be the same size, they really aren't...

I have a worksheet where I have created several named single-column ranges using the offset function, i.e. =OFFSET(Reformatted!$B$2,0,0,COUNTA(Reformatted!$B:$B),1). I created a sumifs formula using these named ranges and I get a #value error. If I use a standard criteria range of...

I have managed to create named ranges for each month spreadsheet, and have rewritten the formula to read =IFERROR(INDEX(September,MATCH($A8,September!A:A,0),MATCH($A$3,September!$5:$5,0)),""). The problem is that the month is hard-coded because it references the defined name. Is there no way...

Thank you Asheesh. I hope the file is helpful.

Hello Experts! After reading through several blog posts suggesting that volatile functions should be used sparingly, I am trying to rebuild a workbook that uses indirect in a rather wanton fashion (250 times per sheet X 10 sheets). There is a sheet for each month of the year (named "January"...

Fascinating! And here I thought that 1+1=2. Evidently that's not exactly precise. ;) I think I'll set my "precision as displayed" as indicated in the article. Hopefully that will "fix" it so I won't forget & create the error again. Thank you so much.

Hi, I'm Jenny, using Excel in a US govt job. I'd say my skills are intermediate; just enough knowledge to be dangerous. ;)

I have some rows with calculations, which I include in a round function (=round(calculation,2). The rounding calculation works fine, but when I sum two cells that use the round function, the sum doesn't always calculate correctly. It's not obvious either, until I export the file for import to...

Bobhc, the software to which the file is to be uploaded requires 2 position decimal, so bringing the calculation out 3 digits won't solve the problem. I think narayank991 has the only possible solution, except that I'll have to put the calculation in the first row to allow for changes in row length.

Bobhc - I'm trying to understand you, so forgive me if my response seems repetitive. Formatting of the cell is irrelevant, as the results of the calculation will be exported to a .csv file. I have attached a dropbox file for you to reference . . . ...

Thanks all, but those two ideas don't solve the problem. I have to take the calculation & import it into another program, and if the sum doesn't match exactly to the allocated amount, the import won't work. Bobhc, increasing the decimal isn't an option because this is currency . . . there can be...

I'm using Excel to allocate an amount based on the percentage of a cell to the total. The formula is Round((A2/A200)*B1,2), copied down the column. All the amounts in column A are 2 decimal amounts, as is the amount in cell B1. The challenge comes when I then sum all the rounded amounts . . ...