Report of sales by month based on a cross tab query
I have a cross tab query that will provide sales by month for 2008. Right
now it is sales for January08. As there are sales for future months, they
will appear as well. I want to create a table that has all of the months
listed out already. Right now, on the first day of the month, I go into the
report and add the new month. I want it so that all month are listed which I
have done. The problem is that when I run the report, I get an error that
says "the Microsoft Jet does not recognize February as a valid field name.
After today, there will be February data but no March ...Find and Replace within an XML file
I was wondering if there is a simple method of replacing characters within
the xml document.
I'm looking at writing a Biztalk Pipeline that escapes illegal characters
before they are processed by a HL7 assembler.
I.E. replace all instances of & or & with \T\ .
I thought about converting to a string and using the replace function, but
that would mean knownig all the HTML codes for characters (as the escape
characters are to be dynamically chosen by the user). Where as I could via
xpath ??? replace all instances of the original character such as &.
Hope it makes sen...Excel Formulae #4
I've been trying hard to find a formula which would
enable me (from one cell) to Add several cells and
Subtract the answer from another cell (subtracting items
of expenditure from a starting total). I could manage
this with Lotus - but Excel does not seem to behave the
same. Advice would be much appreciated. Thanks,
Say you wanted to subtract A1, A2, A3, J4 and N5 from L6:
In article <firstname.lastname@example.org>,
"KenS." <email@example.com> wrote:
> I've been trying hard ...Shorter Formula
Can anyone shorten this formula please. Basically all it
does is gives me an average of the figures in Column "W"
depending on the number of times that product appears
in "R" column
I didn't try too hard to analyze your formula, just noted that your ranges
and sum_ ranges...Formula counts incorrectly
Column K contains dates and blank cells. I would like to count how many
of these dates fall within a given date range. For example, in the
first week in May. Column K contains these dates. 5/2/2005, 5/3/2005,
5/5/2005, 5/5/2005. I would like to count this as 4 dates within the
first week of May. I created this formula but the output is 3.
I assume that it is only counting 5/5/2005 one time?
I changed the dates in the formula to check a second 2nd date range
(5/9 to ...VBA Code to Open File1; Open File2 copy sheets to File1
Using XL 2003 & 97
Need help with the following VBA code:
Dim myNewWorkbook As String
myNewWorkbook = ActiveWorkbook.Name
Sheets(Array("Formulas", "Department Lables")).Activate
Sheets(Array("Formulas", "Department Lables")).Copy _
My goal is to have myNewWorkbook active then:...Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental
changes during update by different users. However, if I protect them, any
new row creation and deletion are disable. Is there any way resolve it?
Especially, when create a row, how can all formatting and formula to create
automatically for the new row?
What version of Excel?
"Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message
>I need to protect the formatting & formula in each cell to avoid accidental
>changes d...Is there a way to automatically put the $ sign into the formula?
I want to turn this =A1 into =$A$1.
How is the best way of doing this. Is there a shortcut....keyboard stroke to
Hit [F4] several times and watch the results.
> I want to turn this =A1 into =$A$1.
> How is the best way of doing this. Is there a shortcut....keyboard stroke to
> do this??
Enter =A1 then tap F4
Repeated taps of F4 scroll through the referencing options
When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently an...VBA Combo Box
I have a combo box that I want to populate with a list of names.
I do not want to reference names in the worksheet - using "RowSource" - I
want to write the names in the code.
I tried the following code:
Private Sub ComboBox1_Change()
..RowSource = ""
..AddItem = "Female"
This gives me an error. Does anyone know where the code should be added.
Is this on a userform?
If yes, then maybe it should be in the Userform_initialize procedure?
If it's on a worksheet, maybe in the auto_open or workbook...pie-charting non-numeric data
I would like to chart and graph responses to a survey that are non-numeric in
nature, such as a bar graph for "yes" and no" responses to a question. Or in
particular a pie chart that shows how many people picked option "P" vs. "K",
"J" or "N". For the pie chart I have a column of data cells, each containing
one of the four letters.
In short, how do I accomplish charting and/or graphing non-numeric responses?
So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range,
and you can produce counts of each value...Help writing a formula
I have a huge database that I need to create numbering for each line like
this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the
number doesn't change each line - sometimes the number will be duplicated on
multiple lines because those lines have the same title, I also need to create
another cell extending that letter/number combo that will differentiate the
So the first cell stays the same if the title is the same. The second cell
adds another layer of numbering to differentiate the first cell.
(Row1, Col 1-4) AK-1, AK-1-1, Title1...Print dialog box using VBA in excel
I like to add a print button (that will select several worksheets and
print them) in my excel worksheet. I have recorded my action using the
macro recorder the problem is I can't select the printer everytime I
run the macro it print in the background without asking the user to
select the printer and uses the default printer.
Can anyone help?? I just want the user to be able to select the desired
printer every time before printing.
My current code:
Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 5)).Select
'intent...Automatic Find and Populate
Ok, I have several spreadsheets that represent bills and have tabs that
represent the months. So, when I recieve a new bill for the month, I copy
last month's bill onto a new tab, and rename it using the billing cycle.
Each year, for each account number is contained in its own workbook- ie:
account 245-9999 has a workbook for 2002, 2003, 2004, and 2005; within that
workbook are tabs for Jan, Feb, Mar, etc. Ok, on a single month's tab, there
are anywhere from 10 to 900 individual line items representing circuits.
What I want to do is create a separate spreadsheet that lists unique c...Troubleshooting a non-delivery Problem
I am trying to troubleshoot an intermittant non-delivery problem to a
public folder. I have a public foler that is set not to be replciated
to the other exchnage servers in our exchange environment. End users
create Outlook forms based messages that get sent to the public folder
involved. All of the user accounts that use the forms involved are on
the save server. The public folder is located on a differnt exchange
On occassion, when submitting a message using a specific form, the
user will get a delayed deliver message that is always followed a
couple of days later by a non-deliver...Excel 2003 - VBA
With code in Module 6, I created a button on a sheet as follows:
ActiveSheet.Buttons.Add(1245, 16, 80, 16).Select
Selection.Caption = "AutoFocus On"
Selection.Name = "AutoFocusBut"
Selection.OnAction = "FocusOn"
Focus = True
I placed the sub "FocusOn" also in Module 6.
Public Sub FocusOn()
If Focus = True Then
When the button is depressed I get the message: "Ambigous name detected:
What am I missing.
My bet is you have two 'things' named FocusOn. Two subs or a sub and a
variable...Re: Could someone please help me with formulas
"No" <firstname.lastname@example.org> wrote in message news:...
> I created a workbook and tried to explain the best I could how everything
> should be. you can download the workbook at,
> I really appreciate everyones help on this.....
> "CLR" <email@example.com> wrote in message
> > Maybe in cell J2 you could put the formula =H2+I2, which would give you
> > sum of the C...Obtain Sub-Folder Names with VBA?
Is it possible to obtain the names of all Sub-Folders for a specific parent
Just 2 levels (1 Parent Folder) and several children folders.
We need the names of the sub-folders, not the names of the files in the
Thanks for your help.
Lookup the Dir Function in the help file, specifically at the example it
illustrates exactly how to get a directory listing. You'll see it isn't hard!
Hope this helps,
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the...Slow load for Analysis Toolpak
I have a macro in a file that automatically loaded the Analysis Toolpak - VBA, but when I upgraded to Excel 2003, it takes FOREVER to load this add-in. Anyone know why or how to fix?
No, but I don't think you need the macro. Just select Tools, Add-ins, Analysis toolpak and it should load every time.
> I have a macro in a file that automatically loaded the Analysis Toolpak - VBA, but when I upgraded to Excel 2003, it takes FOREVER to load this add-in. Anyone know why or how to fix?
...Failed Installation after 2nd 22 hours download!
Tks both Mary and John.
Successfully downloaded Office Publisher to desktop, but installation failed.
My 6 week old new system has caused me DAYS of unsuccessful attempts at
rectifying Vista issues. I not able to do much more than surf the net with
it. I'm at my wits end.
I may just take the entire system back and wait until the tech issues have
been resolved. I'm in the thick of setting up my own business and have spent
more time trying to "fix" my computer than on my business set up.
LOVE the Vista improvements, but the "EASE" of using XP is gone. This
produc...turning off formula bar
I can't remember or find how to turn off the formula bar in Excel 2003? Can
someone help please. This is the bar that displays cell contents up top.
Tools>Options>View tab, uncheck Formula bar
Microsoft MVP - Excel
"Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in message news:firstname.lastname@example.org...
|I can't remember or find how to turn off the formula bar in Excel 2003? Can
| someone help please. This is the bar that displays cell contents up top.
| --...Report to show totals for each day of month
I have an Excel spreadsheet that I use to capture:
Column A) Day of the month (1-May, 2-May, etc.)
Column B) How many clients were in residence on each day (Occupancy)
Column C) Total Capacity (27)
Column D) Shows a "1" if the Occupancy is 90% or more of the Total
Capacity, else "0"
The Totals row shows:
Column B) The average Occupancy for the month
Column D) Totals the times there is a "1" in Column D
Is there a way for me to capture this data in a Report?
I've got a query set up that uses these expressions:
For AdmitDate: <=[Dat...Copying non-adjacent columns to adjacent rows
Hi all, I consider myself fluent in Excel, but I've developed a situation
that has stumped me. Any help would be much appreciated.
I might be able to solve this issue if somebody could show me how to add a
number to a column. For example, if I want Excel to pull data from Column
D, how can I get Excel to realize that column D is really the same thing as
Column A + 3? I know you can use the column() command to get the numerical
value for a column, but is there a way to have it do that in reverse, such
that you could tell it the column number is 4 and it would know that you are
referring...Formulas in a criteria area
Can a formula be used in a criteria area when extracting data from a table in
...Deleting multiple rows through a formula
So does anyone know a formula that will allow me to delete multiple rows of
re-occuring data in a spreadsheet.
For example, I have a spreadsheet of 10,000 lines, with multiple mobile
numbers on it. I need to only have one of each mobile number and delete the
remaining rows where the mobile has been repeated?
you may be able to use the advance filter where can select unique records
select data filter advanced filter select copy to another location then in
list range box enter range to filter in the copy to box enter a free column
select unique records only then ok
then just ...How do I clear a column of data without clearing the formulas?
I want to zero out a column of data, but not the subtotal formulas. How can
I do that easily without going to every cell?
Select your column
then choose edit / goto / special - constants - ok
this will just select the non-formula cells then press the delete key
"EllenSwarts" <EllenSwarts@discussions.microsoft.com> wrote in message
>I want to zero out a column of data, but not the subtotal formulas. How
> I do that easily without going to every cell?
qlso look at: