Posts by tac_79

    I have a spreadsheet with 3 pivot tables. Each of these tables has a different data source coming from 3 different spreadsheets. All saved in the G: drive of the network. Every now and then, I get an error re Cannot open PivotTable source file. The "G:" part of the source has disappeared just leaving the rest of the reference. Any idea why this is happening (happens maybe once every couple of weeks)

    I have a Pivot Table that is about 25000 rows of data by 10 columns. I have only ever done much smaller data sets. Each change to this larger pivot table takes 30-35 seconds. Is this normal? I deleted the data from the range I did not need and changed the table to about 6000 rows but it did not make much of a difference. TIA

    I have a spreadsheet to collate the time off requests for my staff for their roster. In one cell they need to select 'Date of Request'. I have a drop down list that only allows them to select today's date as defined by a reference cell with =TODAY().


    Looking around some forums though, it would look like when I check this tomorrow, the date will have updated.


    I want to limit the cell value so that it has to be today's date (so they can't change dates or enter an earlier date to make it look like they were in first) with a static value...


    Any help is greatly appreciated!


    Cheers


    Tom

    I have tried a few VBA codes to either combine 12 workbooks into one (all with 6 worksheets so new workbook would have 76 worksheets) or also to create 6 new workbooks all with the same sheet from the 12 workbooks (all 12 x Sheet1 in one workbook, all 12 x Sheet2 in another). I would prefer the first option. I have tried the below option and had limited success. I get a run-time error 1004 'That name is already taken. Try a different one'.


    All worksheets within the individual workbooks have the same name.


    Using this code, seems to let me combine 3 workbooks before the error appears


    _________________________________________________________



    Moderator Comment: Please read the rules you agreed to when you joined this forum. Pay particular attention to the rule on Code Tags. I have added them for you today. Please comply in the future.

    Hi all,


    I am using the formula =INDEX($A$2:$A$5800, MATCH(0, COUNTIF(L1:$L$1, $A$2:$A$5800)+IF(COUNTIF($A$2:$A$5800, $A$2:$A$5800)>1, 0, 1), 0)) to create a list of any duplicate values in the range A2:A5800.


    This takes a really long time for excel to process...is there an easier (quicker) way? I played with a pivot table which I am new to, but couldn't work out how to filter only if a value was a duplicate.


    Cheers,


    Tom

    I have a spreadsheet with 3 repeated rows - Quantity, Total Price, Unit Price. Next to Quantity is a cell with the date. In my example A10 = Date, C10 = Quantity, C11 = Total Price, C12 = Unit Price. I am looking for a formula to sum all Quantity values. For the sake of trying to get the formula right, I have been using two sets of data A10:C15...but this will go on without end.


    I can get an array formula to sum by month ={SUM(IF(MONTH(A10:A15)=11,C10:C15,""))}
    I can get an array formula to sum by year {=SUM(IF(YEAR(A10:A15)=2016,C10:C15,""))}


    I cannot get one to sum by both...I have tried, without success ={SUM(IF(AND(MONTH(A10:A15)=11,YEAR(A10:A15)=2016),C10:C15,""))}


    Any ideas? Thanks in advance.


    (also...my first post...hopefully I met all the rules/criteria for a good post!)


    TC