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

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

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

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

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

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

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

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

More questions for the Ninjas out there. I've got a forecast workbook that has been parsed down by region for updating my regional managers. This workbook includes the following script on open to pull up the master forecast and update the data on the regional workbook. Because the master...

I'm trying to automate weekly expense reports a little more. I have a large list of credit card transactions,and using a Vlookup match Credit Card numbers with Employee names. I would like a VBA or Macro to sort the list by names, then copy each employees name into their own workbook, or...

I've got a sheet where we ask field reps to estimate start and stop dates for multiple job sites. this means I have 2 dtpickers for each row on the sheet, and I would like to hide them until the site name/number is filled in. Essentially if the name cell is blank the date cannot be seen. I'm...

I've got a rather large spreadsheet with financial data for several different construction jobs, each worksheet (hereafter referred to as a tab due to the tabular appearance of worksheets in Excel) is titled after the alpha-numerical job number assigned to the job. The problem is that these...

I have a number of jobs going at any one time, and I would like a way to see how many hours have been worked on a job in the last week. Is there formula that says to add range of hours worked if date within 7 days? I would need a similar formula for non labor costs, ie invoices received within...

I have a situation involving more knowledge of Excel than I currently possess. I have a summary worksheet which contains columns such as Cost and Revenue, and Rows that show which job the cost and the revenue get assigned to. So lets say column B represents Cost, C is revenue, row 2 is job1...

I am a job cost accountant, and my boss wants a spreadsheet which shows on sheet 1 the important data from all proceeding sheets. For Example, sheet 1 cell A1 is label Job Number, A2:A7 will show the job numbers which are found on sheet2:sheet7 cell C1. In the past I've just manually...