Search results

I'm trying to summarize key P&L accounts by Trial Balance week, and I've decided sumproduct is the best formula for that. I'm currently using, (in the example sheet attached): =SUMPRODUCT((Sheet1!$C$2:$C$369=$A2)*(Sheet1!$F$1:$BN$1=B$1)*Sheet1!$F$2:$BN$369) This returns the result #DIV/0...

Well, I inherited this workbook from a previous analyst that held this position. It had 7 OLE DB queries already in it using SQL scripting to pull the data in from the ERP system, creating a read only linked table. These tables were then pivoted in various ways and vlookups used to report out...

Looks like I've gotten myself lost. I've pulled in my data tables and linked them very much like I would in an access database. Now I need to consolidate the data on them. If I were still using them in excel as normal, I would use a SUMIF to grab all the quantities from the received in table...

Are those addons pretty intuitive? I've not really used PowerPivot and this is the first I've heard of PowerQuery

So I've got a workbook that's big and slow it's generally somewhere between 75 - 100 Mb. It contains inventory transaction data for a rolling 3 years which populates from linked tables that have half a million or more lines each. These tables have been pivoted, then summaries are done by...

Looks like it works fine. Can you break down how the code works?

I have a worksheet that when I update the date the number of the month's position in the fiscal year populates in A4 (numbers 1-12). I want to use the number in A4 to determine the range of cells to hide. I've a helper row (row 4) that will contain the same numbers (1-12). I'd like the code...

Unfortunately, there is not much consistency with part numbering as they are generally set to match to customers part number, we may very well have 001D00013 and 1D13 that are completely different and would bog down the process more than speed it up. Although the ability to drop the dashes...

I've got a huge inventory list (approx. 25,000) individual items that we've imported into a new ERP system. The upload seems to have items that could potentially be duplicated, but only have a similar item number, things such as leading zeros being dropped, or a dash missing in the middle of...

Hi all, I'm back with another question. I've got this formula: [=IFERROR(VLOOKUP(A7,Sheet3!$A$2:$B$364,2),INDEX(Sheet1!D:D,MATCH(C7,Sheet1!F:F,0)))] This formula is working just fine to return a result, however, I want to know if the result was triggered by the IFERROR or if the result was...

A few hours later it occurs to me that a file might help to see what I'm needing. Hopefully, this can help generate a response. https://www.dropbox.com/s/pjwgd62mswbw1oi/PowerPivot%20Problem.xlsx Thanks again, The Doctor

Hello Ninjas, I'm using PowerPivot for the first time thanks to an Excel upgrade at work. I'm now trying to combine 2 tables, the first table includes a complete inventory valuation for the end of the month. The second is a table that combines the individual count sheets from our full...

I must be using this incorrectly, it returns a #Value! when I paste the code in.

So I've got a worksheet that uses formulas to project payroll for the year. The problem is that anything more than about a month ahead of right now is really un-reliable. As the pay period approaches the data becomes better. So what I want to do is create a dashboard that only shows the user...

Works like a dream SirJB7. I appreciate your help and guidance in using the forum. One thing I noticed while testing it, when the copy & paste by dragging feature (not sure what that's called, sorry) is used to fill the date column the extra lines don't spawn. It happens on both tables, so...

I found another topic that is providing a formula that I really like. Which can be found here http://chandoo.org/forums/topic/automatically-insert-row-into-table, the script provided is great but I need it to go one step further, I have 2 tables on same sheet that may need expansion. I can use...

@ SirJB7 I really like the script you've provided, but I need it to go one step further, I have 2 tables on same sheet that may need expansion. I can use your formula with the named ranges on either one, but I'm not able to wrap my head around how to do both. My bosses really want the expense...

Thanks Hui, that worked wonders.

I've got a list of change orders for various sites which will result in multiple listings. What I need to extract on the summary is the most recent date of the accrual which is column N. I grabbed a formula from Microsoft's help page, but it errors out. I've put a test workbook on dropbox. I...

Looks like your formula works, thanks for simplifying it all for me. On behalf of my management I thank you.

Here is a dummy file with sensitive data removed. https://www.dropbox.com/s/vsh0laeyn5eqyqa/example%20problem.xlsx

I just discovered this post, and I'm trying to use a slightly modified version of this formula. I've got a named range 2 columns wide for job name and site name. On another page I've got a list of cancelled sites with job numbers. I'm using your formula to try to indicate when I've got a job...

2007 Will each regional manager have to add the subfolder to trusted locations also, it will need to work for them also.

Definitely. This one doesn't give me the password dialogue, but immediately opens up a window to select a file..."Update Values:M" if I press Cancel the debugger returns, error in the same place. If I skip the updatelink string it returns an error on the close command as well.

Turns out I already had the automatic update links disabled. That one still brings up the password dialogue, and I can't cancel out of it without debugging. The Debuggger indicates error on line UpdateLink LinkSources(wkbmaster.FullName), xlExcelLinks.