• ## A Happy Christmas To Everyone On This Wonderful Forum

Re: A Happy Christmas To Everyone On This Wonderful Forum

Indeed, may the Christ of Christmas be evident to all.

• ## Macro To Create Dynamic Charts

Re: Dynamic Charts

Howdy and welcome to the board.

Can you post what you already have? (Be sure to use the [ code ] and [ /code ] - without the spaces, so that it appears as true code)

• ## Hide Workbook

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

• ## Update Macros From Afar

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

• ## Macro: Find & Reformat Cells

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

• ## Sumif Between Date Criteria

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.

• ## Sumif Between Date Criteria

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)?

• ## Sumif Between Date Criteria

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

• ## Dynamic Range. Rows & Columns

Re: Dynamic Range For Pivot Table

Here is an example of the dynamic named ranged (in this case Rng), allowing for column or row expansion.

• ## Extending Sum Function When Adding Rows

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.

• ## Chart Won't Plot Dates

Re: Producing Graph From Weekly Data

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

• ## Chart Won't Plot 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.

• ## Dynamic Range. Rows & Columns

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

• ## Chart Won't Plot Dates

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.

• ## Collaboration Forum

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.

• ## IF Formula Returns True or False, Not Desired Results

Re: Worksheet Function Nested If Shows True Or False?

Yep, knew it was already addressed. I just wanted to point out how the original was resulting in TRUE or FALSE.

• ## IF Formula Returns True or False, Not Desired Results

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

• ## Camera Feature Possible Size Limitation

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?

• ## Scrolling Charts Two Lines

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.

• ## A Survey Of Spreadsheet Users

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