Posts by hyperdreamz

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Objective: With reference to current sheet, select another sheet by user input and conditional format to highlight differences in cell f23 current sheet vs. f23 selected sheet

    I have combined a script from tom-urtis.5 with my recorded macro in order to achive this outcome.

    The conditional formating is applied, but for some reason the condional formating formula is ending up encapsulated with double quotes

    i.e "F23<>Sheet1!F23"

    When I remove the douple quotes the formating works.

    i.e F23<>Sheet1!F23

    What am I doing wrong in this code?

    Hi All

    Need a little help in calculating the maximum concurrent connected calls in a half hour based on a dump of start time and end time of 4000000 call records in ACCESS.

    I have been supplied a raw database in ACCESS.

    For your reference I have attached a sample EXCEL file to represent the data I have in ACCESS.

    I need to populate columns “E” to “AZ” with the count.

    For example

    Start Time 05/08/2008 1:57 AM
    Stop Time 05/08/2008 2:37 AM

    Would have a count in the following intervals

    01:30 to 02:00 = 1
    02:00 to 02:30 = 1
    02:30 to 03:00 = 1

    Any help or suggestion would be highly appreciated.

    Hyper Dreamz

    Re: Disable &quot;Automatic Calculation&quot; only on CERTAIN SHEETS in a Workbook ?


    Thanks a million

    Appreciate the assistance

    However even with that my sheets going all crazy on me

    I jus give up???

    I?ll probably have to redo the whole thing from scratch cause something is screwing something somewhere an I cant seem to figure it out?????

    Thanks once aging



    Re: Disable &quot;Automatic Calculation&quot; only on CERTAIN SHEETS in a Workbook ?

    Plz forgive me if I seem like a little excel newbie

    But I am one :)

    I'm not a pro at VB so I'm kind of lost here, my macro creating skills a limited to CTRL+C & CTRL+V and most basic editing

    I have 9 sheets in the workbook

    I have sheets 2 - 6 to be on Automatic Calculation

    And Sheets 7 - 9 on Manual Calculation

    Could u be so kind as to give specific steps as to achieve this result?


    Disable "Automatic Calculation" only on CERTAIN SHEETS

    1) Is it possible to disable "Automatic Calculation" only on CERTAIN SHEETS in an Excel Workbook?

    2) Is it possible to do an ActiveSheet.Calculate every time a user uses a particular Drop-Down Menu?

    I have a mini business application I just got done with

    I use o LOT of SUMPRODUCTS on certain sheets

    And the data that it working with is vast

    So due to the Automatic calculate function being enabled by default my comp just goes loopy on me every 30 second?s I?m using the sheet (switching between sheets)

    So I went ahead and disable Automatic calculations and made it Manual

    Provided a macro do an ActiveSheet.Calculate when I need it to update

    Sadly this has caused more problems than it solved

    On certain sheets I have some VLOOKUP's INDEX's that need to be automatically calculated but since I turned the damm thing off it will only update when I run the macro

    But this screw's up the resultant data on screen producing hundreds of errors (UGLY)

    This has totally frustrated me after putting in so much effort into this project it finally seemed to be done then this......

    I would really appreciate any assistance with this problem


    Re: COUNTIF data after applying Auto filter


    headings are replaced too

    the formatting re-applied

    but if it’s needed I can go the A2 way too

    so long as this results in only the filtered data appearing to the right

    if all the data reappears

    the and I save the file in error

    it will never open (at least on the lame ass mc I work wit)

    jus ran a check wit this week’s data 175MB

    had to delete it because it kept freezing every time I opened it

    I think it’s due to all the SUMPRODUCT's I have working in there



    Re: COUNTIF data after applying Auto filter

    What happens is

    On the sheet DATA Complete select all clear is done

    Then a paste only values is done

    This is done because next conditional formatting is applied that changes week to week

    I too researched the filter bit ur talking bout

    Plz forgive my naivety

    The best way I can describe it is that the cells that have the calculations in will be cleared every week

    And new data pasted in there

    Resulting in blank cells where ur doing the calculations now

    In spite of that it’s returning the unique values on the unique sheet after that

    But along with it its pasting all the data in the range DATABASE on sheet data to the right wher u did the calculations

    Can pasting of all the data again be prevented?

    Try clearing the cells where ur doing the calculations on DATA then go to UNIQUE & come back to DATA & see what happens

    U’ll se what I mean


    Re: COUNTIF data after applying Auto filter

    A quick question

    The file that u sent works like a charm however there’s a little snag

    I noticed that certain calculation are being performed on sheet DATA to obtain the unique data on sheet UNIQUE

    However this (DATA) sheet is updated constantly with a huge cut paste of a dump of an html page

    This will overwrite anything on this sheet

    It’s a paste of pure values resulting in those being effectively erased

    Now this actually works inspire of that but I’m getting all the info on sheet DATA pasted over to the right when the original cell were cleared and the macro re run

    This bloats up the file resulting in a 25-30 MB file when a real sample of the data is used.

    This in turn kills my comp

    Is it possible to move all the calculations to the unique sheet only?

    And prevent the data getting pasted over again

    Sheet UNIQUE must be a stand alone sheet and should not have any reference to any other sheet other than DATA

    The sole function must be to obtain unique values from the given columns on sheet DATA

    Also I would old like to thank you for all the assistance I’m really obliged by the prompt and fantastic solutions ur throwing my way

    It’s all truly appreciated

    Seasons Greetings,


    Re: COUNTIF data after applying Auto filter

    Thanks a ton man

    That file put me back on track

    However there a small modification I need

    I need it too dump ALL the unique values to a sheet called “Unique”

    Column A = UNIQUE Tech Name
    Column B = UNIQUE Tech Badge
    Column C = UNIQUE Manager Name
    Column D = UNIQUE Fiscal Week Number

    Instead of manually running the macro to generate the lists is it possible to run the macro by just viewing the UNIQUE sheet.

    i.e. the UNIQUE macro must be triggered when a user views the UNIQUE sheet thereby updating the unique values

    For now I’m dropping the upper lower limit on the fiscal week

    I think I’ve found another simpler way to tackle that problem

    Just need a complete list of all UNIQUE values

    Is this possible?

    Seasons Greetings,

    Re: COUNTIF data after applying Auto filter

    I have received excellent solutions from the people here

    But sadly don’t have a final solution

    I have got bits and pieces from many people that are getting me there

    I sincerely appreciate the effort

    However I’m going to update you guys very soon with my progress

    My current points of failure in the solution are :-

    - My inability to obtain a DYNAMICALLY UPDATED UNIQUE LIST for given parameters. I need these unique lists for the DROP DOWN menus.

    For a given Upper limit fiscal week & Lower limit fiscal week I need a DYNAMICALLY UPDATED UNIQUE LIST of

    1) Tech names
    2) Badge ID’s
    3) Fiscal weeks
    4) Manager Names

    - For a given fiscal week range pull up a DYNAMICALLY UPDATED UNIQUE LIST of manager and the corresponding techs that appear under those individual managers

    Seasons Greetings,

    Re: Make a dynamic alphabetically sorted UNIQUE list

    Got the plug-in

    Every thing works like a charm

    But is there a way to distribute the plug-in embedded in the file

    Or that particular function

    For e.g. can I extract the UNIQUE VALUES function to say a macro and sent it as a part of a single file????

    Quote from Krishnakumar

    See the attached file.


    Re: Make a dynamic alphabetically sorted UNIQUE list

    I actually got the file to open and even got it extracting unique values from multiple columns

    Hover the single resultant sheet for 5000 records was 8MB and froze forever when entering any data

    Is there a way to do the same thing using a DYNAMIC RANGE

    Something that won’t bloat up the file to such un-reasonable sizes and actually open

    On an average 2000-5000 records will be added on a weekly basis

    The calculations bust be instantaneous or at least reasonable

    I can’t seem to get this to work

    Plz help ! !

    I’m ready to even accept a macro at this stage

    Need to jus get UNIQUE ALPHABETIACLLY SORTED VALUES from columns A, E, & F from a sheet called DATA and save those to a sheet called UNIQUE in columns A, B & C respectively without the need to run a macro somthing that will probably auto update the moment u switch between sheets.

    Plz help Ahhhhhhhhhhhhhhh !!!

    Quote from Domenic

    Since I make it a practice not to open attachments, I've attached a sample file for you.

    You'll notice that the formulas have been copied to Row 20 only, to keep the size of the file small. Any new data you enter, up to and including that row, will automatically be updated.

    Hope this helps!