• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. T

    Sumproduct returning div/0 with no division in formula

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

    Looking to speed up big data analysis

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

    Hide Columns With Dynamic Range

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

    Conditional Formatting for Similar Items

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

    Looking for IFERROR conditional format

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

    Combining Tables in PowerPivot to ID Missing Data

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

    Using VBA to show segmented data based on dynamic cell value

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

    Use VBA to add lines to multiple tables

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

    Index to Find Recent Date on Multiple Criteria

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

    Trouble with Password Dialogue Box

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

    Sort and Move Data to Multiple Worksheets

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

    Hiding Multiple DTpickers

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

    Sorting Worksheets

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

    Show Recent Data

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

    Need a formula

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

    Summarizing Data from Multiple Sheets

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