Query from Multiple Workbooks
I have 12 files in a directory with a sheet name Sales containing
Sales data for each month having more than half a million rows in each
The field names are all the same in all the files like date, item,
itemgroup, branch, unit, salesman, amount etc
I am looking for a way to get a query from all these workbook and get
a summarised report based on criteria like sales by salesman, by
month, by product, by product group, by date, by branch etc.
Is this will be possible through code?. Itry to use MS query but not
Using Excel 2007 and Win XP
...Installing CRM3, multiple errors
I have installed CRM3 on a clean SBS2003 machine (under VirtualPC), but have
got a series of errors and cannot now acess CRM at all. I followed the steps
in the Implmentation Guide.
I had to change the web site port to be 80 and the alias to be able to
access it (as crm3demo).
I installed CRM3 under Administrator login and was initially able to open
CRM correctly, login as Administrator, go into the Administrator user and
uncheck the restricted access check box. Everything appeared normal.
Then I set up the sample data users in Active Directory and tried installing
the sample data by ...Varying colors on a line chart with multiple series
In Excel 2003, is it possible to change the color of part of a line on a
chart with multiple data series? I can do it on a single series chart.
Yes, you would need to select the segment of the line and then format it.
So select the series
pause and then select the segment
Andy Pope, Microsoft MVP - Excel
"LKP" <LKP@discussions.microsoft.com> wrote in message
> In Excel 2003, is it possible to change the color of part of a line on a
> chart with ...match dates in two columns get value from third
I have a data set on daily basis and another data set on weekly basis. i want
to put the weekly data in daily data set by putting a formula which picks
values from col c for relevant dates in daily set. example - let us say this
is the data set
ColA Col B Col C
jan1 jan1 8
jan2 jan7 7
I want the data from colC against dates in ColA as in ColD below. it should
leave other cells blank (colA and ColB are dates format)
ColA Col B Col C ColD
jan1 jan1 ...Copy and paste formula; Value pastes instead
I've tried starting several worksheets, checking formats,
pasting special (formula only). For some reason the
formula actually copies OK by looking at the formula bar,
but the value in the cell is identical to the value of
the copied cell.Incorrect. Tried even the simplest
formulas with no joy.
...OL2003 Comining Multiple Contacts
As I mentioned in another post, I'm playing around with Outlook.
Here's my Contacts problem.
I was using Outlook at work and synchronizing my Contacts with my Palm
Pilot. So that part of the transition was a breeze. But I was using a
completely different email program at work which has it's own address
I know how to import that address book into Outlook, but the end
result will include be a whole bunch of partial duplicate entries
where say, I have name and address information in the old Palm/Outlook
database but only an email address in the email address book.
Any sugge...setting up multiple hotmail accounts not possible
I am trying to set up 2 Hotmail accounts in Outlook
2002. When I have entered all the correct information
and try to Send/Receive, after about a minute, it gives
me an error message saying that it needs to be in offline
mode. My original account works fine and does even with
the second added. But when I add the second e-mail
address, it doesn't send or receive from that new
account. Does anyone have any ideas on how to fix this?
It would be greatly appreciated.
Outlook does not support more than 1 account - some people have 2 or more
working but it seems like the luck ...Combining multiple click events
Is there a way to combine multiple click events into one relatively short
What i have right now is a form with two sets of checkboxes. Each set is
about 25 check boxes.
When the form loads, the most commonly selected ones are automatically
checked. But to save time, I also added a "select all" button for each set of
check boxes. When you click it, it will auto check each of the 25. Uncheck
it and all 25 are unchecked.
What I want to happen is if I click the "select all" checkbox and then
uncheck one of the 25 options, the "select all&qu...Nesting Lookup Functions
Is it possible to nest an HLOOKUP in a VLOOKUP equation?
Here's what I have: =VLOOKUP(C27,A2:B5,(HLOOKUP(D27+1,INDIRECT(A2),21)
Each VLOOKUP lookup value is the start of a range that I would like to be
the first row of an HLOOKUP range. I might be going about this the wrong
way, so if you have suggestions, I'm definitely open to it!!
The objective is to provide Excel with two associated values (i.e.,
department and production value in a given range) and have Excel produce the
production value ranking. I have a ton of departments each with a range of
production valu...How do I eliminate multiple task status reports in Outlook?
In Outlook, if I keep a task open for several minutes while working on it, it
sends a Task Status Report to the person who generated the task every three
minutes. I'd like to turn this off so that the only time anyone gets a report
is when I click Send Status Report.
...Change font size based on value of a cell
If the value of A1>0, I need the font size in a merged cell to change
from the default 10 to 16. It needs to return to the default size
when A1 returns to a value of 0. Can someone help with this? Thanks.
Here's a little macro that will do it.......
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("a1").Value = 0 Then
Range("B1:E3").Font.Size = 10
Range("B1:E3").Font.Size = 16
Vaya con Dios,
> ...Chart Value Changes in VBA
When you have say a bar chart and drag the bar downwards this will change
the value in the reference source cell. Is there a way through VBA to
identify the source and the cell changed by when dragging the bar. That is,
when you drag the bar in the chart let me know the cell that ahs changed!
Any help would be appreciated. Thanks in advance.
If your objective is to prevent the cell changes via the chart, you can
protect the worksheet (Tools - Protection). The cells should be locked
(Format - Cells - Protection).
Or you can use the Chart_SeriesChange event macro for the cha...p-value, statistics
I want to determine the p-value of a regression line. I hope someone can
explain to me how i can do this in excel.
wim rademakers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30134
View this thread: http://www.excelforum.com/showthread.php?threadid=502248
wim rademakers -
> I want to determine the p-value of a regression line. I hope someone can
> explain to me how i can do this in excel. <
One way is to use the Regression tool, part of the Analysis Too...sum negative and positive values separately
I have a time series like this:
and I would like to sum just the positive and negative value separately
in this way
And I don't know if it is
To sum only positive values use =SUMIF(B1:B10,">0") and to sum only negative
But I cannot see how you get the second tables from the first.
Bernard V Liengme
remove caps from email
"bora" <firstname.lastname@example.org> wrote in message
news:1...Currency values set to 2 decimal places
Is it possible to set a currency value fixed to 2 decimal points a
opposed to just rounded up or down.
for example if 33.3% discount was applied to �10 it would be �6.66666
which would display as �6.67 Therefore if you had 3 x �6.67 it woul
work it out as �20.00 and not �20.01.
clappus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1613
View this thread: http://www.excelforum.com/showthread.php?threadid=27569
Yes, set the format to Currency, and that will work.
R...List Box Values
Can I use values from a list box/combo box in a formula and if so how do I
Sorry wrong NG
"Mike Cramsey" <email@example.com> wrote in message
> Can I use values from a list box/combo box in a formula and if so how do I
> do that?
...Setting AutoNumber to Non-Default Value
I would like to use the AutoNumber data type within a field. However I would
like the sequential autonumbering to begin from 6000 rather than the default
value of 1(for the first record). Thanks for your help in advance.
I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
There are other ways of providing the numbers...Multiple workbooks open -- closing workbooks
Dear Clever People,
I hope this is not the "silly question of the day".
We were wondering (when you have multiple workbooks open and you close one
workbook) why are all other workbooks forced to be closed, too?
Is it possible to ever close one workbook and leave all the others open?
Close it by going to file>close or by pressing Ctrl + F4 or by clicking the
smaller X below the big red X
"Janet Panighetti" <JanetPanighetti@discussions.microsoft.com> wrote in
message news:1C9BC30B-BE1A-4605-B215-99C07CB1D366...Extended Pricing price groups should allow multiple Units of Meas.
We categorize our items without regard to units of measure, and want to make
an overall change to a particular category of items, e.g. increase the
discount percent for all items in that category. But we cannot group these
items into one price group because they have different units of measure.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft ...Outlook 2003 opens multiple instances
When I double click the desktop icon for outlook it opens
twice or has two instances available in the task manager
window. Once I disable or end these and reopen outlook it
is fine. Also, we are currently running Viewmail and
Viewfax from Active Voice. Any help would be grealty
Charles Garcia wrote:
> When I double click the desktop icon for outlook it opens
> twice or has two instances available in the task manager
> window. Once I disable or end these and reopen outlook it
> is fine.
This happens randomly for me too. In fact, for a long time it used to
a...Mult-Value Select returning ID# not text.
I have a text field that is based on what is selected in a combo box. The
text field is populating but it is returning the ID numbers and not the text.
The combo box is based on a query from a form that has a combo box field
that allows Mutli-Value selections. I tried basing the field on the
uderlying table but then it only returns the one value and not the
mutl-values that have been selected.
Try this --
Open form in design view, click on combo, double click on combo, scroll to
near the bottom, select Properties. Scroll to Column Widths and change it
form something like t...Return Column header, if row value is > X
I need to create a formula the looks at a row range for a value over
$1,000,000 and if a cell in the row matches the criteria, return the header
of that column that matches the cell...
A B C D
1 "What FY contains value >1,000,000?" __________
2 FY01 FY02 FY03 FY04
3 1,500 15,000 150,000 1,500,000
DESIRED result in cell D1 should be FY04.
Any solutions on how to write this formula? I'm sure it will be a nesting
stat...Upgrading Multiple Companies at Different Times
I have multiple companies currently in my GP 8.2 installation on a single
server. Of course these companies each share the DYNAMICS database.
I am plotting out the upgrade process to 9.0 and would like to upgrade one
company at a time, as one of them is ready to go, while the other will
require more work due to customizations.
Would there be any issue with upgrading CompanyA to 9.0 on a new server, and
then a month or two down the line, upgrading CompanyB to that same server?
Would CompanyB be able to slide right in to using a DYNAMICS database that it
had not used for a couple of...return record if 5th character is a letter
I have the mid statement to pull the 5th character, but I need the
(query) syntax to filter out the ones that are a letter (alpha)
UCase(Mid(MyString, 5, 1) >= "A" AND UCase(Mid(MyString, 5, 1) <= "Z"
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"Ron5440" <Ron5440@discussions.microsoft.com> wrote in message
> I have the mid statement to pull the 5th character, but I need the
>...Avoid 0 values in a graph?
i have a graph based on a pivot table with countries and production as
fields. What i need is the graph not taking into account the countries
with production=0% so those countries would not appear in it.
Any help would be greatly apreciated.
daru's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34656
View this thread: http://www.excelforum.com/showthread.php?threadid=544228