Re: A Happy Christmas To Everyone On This Wonderful Forum
Indeed, may the Christ of Christmas be evident to all.
Re: A Happy Christmas To Everyone On This Wonderful Forum
Indeed, may the Christ of Christmas be evident to all.
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)
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 ByTheCringe2The 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: Dynamic Range For Pivot Table
Here is an example of the dynamic named ranged (in this case Rng), allowing for column or row expansion.
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
Quote from slolayDisplay MoreHi Shades
Thanks again but did what you said and the x-axis has 1/0 as categories. When i put the formula in the values i get are
19-Oct-06 1/0 60173 16488 21755
26-Oct-06 1/0 60328 16436 21843
02-Nov-06 1/0 60543 16434 21913
09-Nov-06 1/0 60496 16605 21965
16-Nov-06 1/0 60530 16561 21857
23-Nov-06 1/0 60568 16559 21878
30-Nov-06 1/0 60935 16260 21877
Is this what i'm supposed to get? I'm new to Excel, can you tell me what the formula is doing?
Cheers and thanks for you assistance
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?
Yep, knew it was already addressed. I just wanted to point out how the original was resulting in TRUE or FALSE.
Glad it worked for you.
Re: Worksheet Function Nested If Shows True Or False?
Quote from Simon LloydHi 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.
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???