Recent content by The Doctor

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...