Posts by shades

    Re: Hide Application?


    Howdy. I guess I would ask what you want to achieve with another application instance rahter than hiding the instance?


    (Remember I am old, slow, and ,....)

    Re: Update Macro With Macro


    Howdy. Just thinking out loud for a minute: what about developing an .xla file that has the macros. Stephen Bullen, et al, have advice on how to package and distribute in your kind of environment. Professional Excel Development

    Re: Macro To Find And Reformat Cells


    Howdy, and welcome to the board. As a starting point, Record macro feature, and go to one worksheet, and make the change that you need. Once you get that you can post the code. We can help you generalize for looking for the text. (PS, this approach is not "giving" you the answer, but a method of determining how to get the answer).

    Re: Sumif Dates Meet Criteria


    I added a column (G), and then put actual dates into F2 (1/1/2006) and G2 (3/31/2006), then in H2 (formula cell), I put this formula:


    =SUMPRODUCT(($A$2:$A$68<=$G2)*($A$2:$A$68>=$F2),($C$2:$C$68))


    It works. The reason for the additional column and putting cell references, is that you can change the dates using cell references rather than changing individual formulas. Now you can add the other quarter dates:


    F3: 4/1/2006
    F4: 7/1/2006
    F5: 10/1/2006


    G3: 6/30/2006
    G4: 9/30/2006
    G5: 12/31/2006


    (other ways to do it, but this works). Then copy the SUMPRODUCT formula down.

    Re: Sumif Dates Meet Criteria


    Perhaps the problem is a little deeper. How are you entering, formatting, and tracking hours? Is it as a number or as time? If time, then there will be additional steps to take. Will they be always on the same day or will the hours overlap on days (i.e. a night shift)?

    Re: Sumif Dates Meet Criteria


    Quote from ByTheCringe2

    The formula you have used uses the range A3:A68, it should be A2:A68, I think.


    However, the main problem is that the dates in column A are entered as text. They need to be entered as real Excel dates. The only way I know to correct this is to format column A as Date, with the format you require, perhaps 03/14/98. Then re-enter the dates.


    No there is a faster way. Put the number 1 in a blank cell, copy it, then select the entire column of dates, and Paste Special, "Multiply". Click okay. This will give the dates in serial number, then reformat to Date. See Convert Text to Numbers

    Re: Extending Sum Function When Adding Rows


    Many possible ways. One that I use is to leave one row above and below all data, and one column to the left and to the right. Then reference the empty rows/columns in the formulas, and they will automatically pick up any new rows (or columns) of data inserted between the empty rows/columns.


    Works great.

    Re: Producing Graph From Weekly Data



    That's because the dates are really formatted custom, and not dates.

    Re: Producing Graph From Weekly Data


    Okay, did a little testing. I added a column between weeks and data, and put this formula in B2:


    =TEXT(A2,"m/d")


    then copied down. Then changed the Category axis to B2:B8. This works perfectly.

    Re: Dynamic Range


    Howdy. I use the following to define the range:


    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),11)


    However, if the number of columns cahnges you can make the 11 dynamic as well. Replace 11 with COUNTA(Sheet1!$1:$1)


    Then just use this named range when setting up the Pivot Table (in the Wizard for range). Hit F3 to get the list of names (that way you don't have to remember the exact spelling).

    Re: Producing Graph From Weekly Data


    Howdy, and welcome to the board. I suspect that you have included the Chart date in the data section, rather than the Category axis label. Right click on the graph, and choose Source Data, click on the Series Tab. On the left side, if the dates are listed there, then remove it, and put it in the Category axis at the bottom.

    Re: Collaboration Forum


    I think the book (and CD) MS Access 2003: Inside and Out has a database for that specific need. Perhaps it will give you ideas, even if you are working in Excel.

    Re: Worksheet Function Nested If Shows True Or False?


    Quote from Simon Lloyd

    Hi all i am trying to create a nested IF but i dont usually use worksheet functions, the formula i have will show either true or false but not the values i am trying to return.


    Code
    =(IF(B3=11,"Sick",Sheet2!B2)=IF(C1="Line Off","",Sheet2!B2))


    Could someone help re-arrange it please and explain what i have done wrong? Regards,
    Simon


    Howdy. Your formula is in fact a TRUE or FALSE


    = [COLOR="SeaGreen"]([/COLOR][COLOR="Blue"]IF(B3=11,"Sick",Sheet2!B2) [/COLOR]= [COLOR="Red"]IF(C1="Line Off","",Sheet2!B2)[/COLOR][COLOR="seagreen"])[/COLOR]


    You have two individual components joined together with an "=" sign. And the outer parentheses do not fit in with the IF situation (They basically surround the result of the equation.)

    Re: Camera Facility ... Size Limitations?


    Howdy. Try this:


    Click on the picture (from the camera tool), then in the formula bar, type in the range (including sheet name), i.e. Sheet2!$A$2:$H$19.


    I tried 15 Columns and 25 rows, and no problem (and again 19 Col/30 rows, no problem). What is the size of each row/column?

    Re: Scrolling Charts Two Lines


    Howdy. I think that to get the years to coincide, you would have to have four columns:


    Sales 2005....Sales 2 2005 ..... Sales 2006.... Sales 2 2006


    Then have four lines on the chart.

    Re: A Survey Of Spreadsheet Users


    When I began this particular job 6 1/2 years ago I had never used Excel, but was hired as an "analyst". My predecessor had typed everything in manually from 18 hardbound notebooks (2" each)-- 80 cities, 10-15 companies for each in 7 categories, plus regional summaries and National summaries. It took a month, and everything was done with a calculator, then the results transferred to Excel for final numbers.


    I did that for one Quarter-Report cycle and decided there had to be better ways of doing that. Over the next two years, I learned about automating some of the tasks, getting digital files (not paper), and using some of Excel's capabilities. I also used Access to consolidate the data from three different sources and standardize, then exported to Excel for individual city reports. It reduced time from 1 month to 6 hours. And half that time was for my own checking to make sure; yes, I instituted a few safe-guards to let me know if something was wrong.


    Since that time I had refined it even more. I haven't touched one of the charts for more than 3 years, everything is automated, including getting rid of zero-entities in pie charts. Andy Pope and Jon Peltier (through their web sites) were significant help in this process.


    I have taught various groups, but the casual user tends not to want to know. Those who have their heads buried in data are always appreciative of new approaches, functions etc. Recently I trained six other analysts in another group. Their Director had been my Director for only five months prior to moving to the new group, and he saw the potential of what could be done in his new group. He mandated that everyone in his group attend the training. That has opened the door to even more training opportunities. Typically word passes quickly that if there is an Excel challenge or problem they come to me. So far, I have managed to meet the needs, or in most cases I point them in the direction so they can solve for themselves. That gives me the greatest thrill.


    Who would have thought - from an old codger???