Text box input based on a combo box selection
I have an inventory input form that has a combo box from which the
user selects the shift for which the inventory is being taken. On
this form I also have a sub form from which separate data is gathered
from the user, but the shift information is also needed. The form and
subform are bound to separate tables. I am using a subform to gather
the additional information to a separate table because the results are
used in a separate calculation. I only want the user to select the
shift information once. How can I populate the "shift" text box on
the subform with the same information ...Issue with passing values
I was wondering if anyone might be able to help me.
I was trying to create a function that takes 2 arguments and i go
compiler error messages saying i need '=' expected.
Here is my code
filein = Application.GetOpenFilename()
ImportCSV (mysheet, filein)
in the function of ImportCV, i have
Function ImportCSV(sheet As String, inputfile As String)
Thank you in advanc
associates's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3507
View...Portfolio value by investment account
At the moment I have two accounts of investment and I would like to compare
their results for a period of determined time. Like January till September,
It could obtain a line chart with its results?
...Diagram with constant values in the background
I am working with MSExcel 2003.
From a table I create a graph, no matter the kind of graph.
Say that the scale is 0 to 100 and the number of values is 20.
X: 1, 2, ..., 20
Y: 10, 56, ..., 2
In the backgound I need to display some constant values, for example
to show min and max acceptable values. This can be defined by a single
value for each line.
How can I define to the chart these values, without having to add more
columns (min, max, ...) to the table and fill in them, and to refer to
them in the chart?
In short, you can't. You need to t...Returning multiple values
I have an array of raw data that spans 15 columns and 4000 rows. In column 1
there is a part number and in colum 12 there is a customer name. These part
numbers overlap to customers fairly often (up to 20 customers for a part
number). How would I, on a separate sheet, return all the customers who use
this part number?
This is what I am looking to do:
Part Number Customer
2 ...Help with If statment to compare values #2
Thank you to all of you for your help. I tried each provided option and
they all work great!!!
Many, many thanks!!!! Super!!!....
> "Bryan Hessey"-
> A method without 'if' is: ...
> Just a clarification that the IFs in the earlier formula suggested are
> merely add-on error trapping for clearer outputs, which I normally
> The core formula's just a simple INDEX(...,MATCH(...,1)) with the
> flexibility to read the source reference table as it is. Using
> require...Pivot table will not returned value's
Yesterday i made a macro which gets the value from Cell AM1 and fits it as a
criteria in a Pivotfield in another file.
The macro as seen at the end, works smooth except for this part:
Set pt = Sheets("Table Combi").PivotTables("PivotTable3")
Set pf = pt.PivotFields("ARF Code")
ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage =
"" & k & ""
If pf.CurrentPage <> " & k & " Then
pf.CurrentPage = "(Blank)"
Does ...Force comment based on cell's value
I have a grid of numbers that is used for tracking, and it is being looked
at in many ways.
One last function I have been trying to add is a way to force a comment onto
a cell who's value is less then a set value. For our sake lets just say less
Is there a way to have the cell value tested and start a macro if less then
something like this...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Range("A1") Then
If Target.Value < 1 Then
MsgBox ("Less then one") 'your code goes here
this code goes in ...Excel cell taking only Integer Values
When I write in a Cell in Excel if I am writing integer then Cell takes that
Value but if I write a string value then I cannot see anything in Excel
_objRange = _objWorkSheet.Range(_objWorkSheet.Cells(_intCurrentRow,
17), _objWorkSheet.Cells(_intCurrentRow, 18))
_objRange.Value = "String Value"
This is happening in Excel 2000 (10.0) on WindowsXP.
Thanks and Regards,
This is usual behaviour for cells formatted as numbers - when any text
You need to reformat the cells as General or Text
Message posted from http://www...How do I get a filter to "UPDATE" the rows selected?
I am using Excel 2002 and I have a speadsheet that has prices in it.
If I have the filter set to (the filter criteria is much more complex than
this though) prices for items that cost >$5.00.
Of I change an item to 4.59, it should not be seen. How can I get it to
re-update the rows shown, with out releaseing the filter and resetting it?
Is there something like refresh.filter or anything like that?
AFAIK you must release and reset but a macro with a worksheet_change event
could trigger this for you so that it would happen automatically.
SalesAid...Search Row Multiple Values
In row 2 from A to Z I have numbers from 1 to 12.
Some numbers appear more than once, sometime 4 times.
I want to have an equation that will be able to look through the ro
and pic out all of the cells which have the number 4 and return thei
Message posted from http://www.ExcelForum.com
One way is to use data>filter>autofilter
"ianripping >" <<firstname.lastname@example.org> wrote in message
> In row 2 from A to Z I have nu...Averages of absolute values
I'm switching over from QuattroPro to EXCEL and am having some newbie
I have a list positive and negative numbers (A1..A25). I would like to
calculate the average of the absolute values. The only method I have been
able to come up with is to calculate the absolute values in column B using
B1=ABS(A1), B2=ABS(A2), etc. Then use A26=AVERAGE(B1..B25) and hide column
B. Is there a better way to do this?
In Quattro Pro I would use @AVG(@ABS(A1..A25)), but a similar method doesn't
seem to work in Excel.
Array enter the following formula:
=SUM...Extract Top 5 value from a pivot table
I have a a pivot table listing by country and months of the number of times
a certain internet page has been reviewed.
I would like to have a formula (x5) which would extract the 5 highest pages
viewed for a given month and given country.
I tried the Getpivotdata and sumproduct functions, but I am getting nowhere.
Can you help?
Could you provide a sample of your data?
On 23 Kwi, 01:47, Andre C <Andre C...@discussions.microsoft.com>
> I have a a pivot table listing by country and months of the number of times
> a ...How do I programmatically select various ranges in vba?
From what I understand you must use Application.Union to combine large Range
groups in vba to use in Excel.
Dim BigRange as Range, R(10) as String
R(1) = "1:1, 3:3"
R(2) = "7:7, 20:20, 43:43"
R(3) = "100:100"
Set BigRange = Application.Union ( Range (R(1)), Range (R(2)), Range
Which is fine and dandy with just the 3, but if I had a hundred or so ranges
how would I create a loop statment to combine the various ranges.
*The ranges would contain the entire row of a wo...Look up value range in column and then count
I have a column of numbers ranging from 0 to -100
I need to count the number of cells with in the ranges
0 to -6
-7 to -14
-15 to -28
-85 to -100
Can you tell me the best way to get these figures, I would imagine I have to
put this formula into 4 different cells.
Using Excel 2007
Supposing your values are in column A rows 1 to 17 try this:
Four seperate cells:
=COUNTIFS($A$1:$A$17,"<=-15",$A$1:$A$17,">...Printing a selected part of worksheet
I know it's possible to highlight a portion of a
worksheet and print just that part. That works fine, but
I have a super big spreadsheet that has to be broken into
several pieces before I can print it.
I'd like to make the process more efficient by assigning
names to various portions of the spreadsheet. Then, when
I would want to print, I could simply tell Excel to print
those predesignated portions. But how do I do that?
Any help is much appreciated.
> I ...[Right-clicking a CTreeCtrl] how to select the item under the mouse?
I need to move the current selection in the tree to the node
under the mouse pointer when the user right-clicks the
My problem lies in the use of HitTest:
cp.SetPoint( p.x, p.y );
HTREEITEM selected = faqTree_->HitTest( cp, &flags );
if( selected != NULL && (TVHT_ONITEM & flags) )
faqTree_->SelectItem( selected );
I don't think I'm getting the right coordinates above. What can I
use instead of GetCursorPos() ?
This will help:
http://www.codeguru.com/forum/...Can Comments be automatically converted to text cell values?
I have a large number of cells with Comments. I'd like to basically convert
the Comments to text values in cells (in a column right next to the cells
that have the Comments). Is there a function or other tool for doing this in
You can use a User Defined Function to do that.
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"t...Place actual selection name in table instead of ID #?
I have a CBO that places the companies ID in my table instead of the actual
companys name. I know this is how it is suppose to work but I need it to
place the actual name that the user selects into the table. How is this done
I know of a way?
In that case you have to change the field in the designated table because
initally you would store a number and now you want to store text. So change
the field to text and then change the bound column of the CBO to 2 (which is
probably the field which shows the name). This way you can still use the
combo but it will store the name in the table (as...Obtaining selected printer name
AfxGetApp()->SelectPrinter(m_hDevNames, m_hDevMode, FALSE);
how do I obtain the printer name, e.g. "\\Hostname\Printername"?
Also how do I get a handle to the selected printer?
DEVMODE FAR * pDevMode = (DEVMODE FAR *)::GlobalLock(m_hDevMode);
Sorry for the nuisance. Its been a long day.
"Jan M" <email@example.com> wrote in message news:#7MdFAhyHHA.5964@TK2MSFTNGP04.phx.gbl...
> Having called
> AfxGetApp()->SelectPrinter(m_hDevN...Multifile select in FileOpen dialog
Hello everyone. My app (SDI) requires several different
data files to be loaded, after which it does some serious
number crunching. It would be best if, rather than the
user having to open each file individually, they can
select several files at once in the standard OpenFile
dialog. Is this possible, and if so where do I go for more
info? Thanks in advance!
Since this is an MFC newsgroup, I'll assume you are using MFC and recommend
that you use the CFileDialog class.
Just set the OFN_ALLOWMULTISELECT flag in the OPENFILENAME structure (in
CFileDialog::m_ofn). The documentation for ...Outlook 2000 automatically downloads body if header selected
I'm running Outlook 2000. Until this morning, I would
operate as follows:
1) Tools > Send and receive > account (which downloads
2) Select the headers I wanted
3) Tools > download > selectec items OR delete them
But today, when I select a header, the body immediately
gets downloaded. I've checked all settings, can't find a
setting that applies. My preview pane is closed.
AutoPreview is enabled, but it always has been....
Problem solved. Hours of Googling didn't help. Finally
solved the problem - I'd installed a bluetooth d...Search and display based on cell value
On page one. I have various full names listed throughout the worksheet
in various places.
When a user clicks on a cell with a name in it, I want them to be
directed to sheet 2 where the same name (cell values) is found in
column A. (There will be profile info starting with their name in
I think the following may do what you are looking for. Right click the tab
at the bottom of Sheet1 (which is what I assumed you meant by "page one"),
select View Code from the popup menu that appears and then copy/paste the
following into the code window that appeared...
...Pie Chart not to display 0 Values
I have an excel Table something like below.
Column A Column B
Item 1 24%
Item 2 13%
Item 3 0%
Item 4 3%
Item 5 0%
The Values in Column B are being automatically updated from another table.
There are 24 Items in the table and usually about 10 of them are 0, in a
given period of time.
Is there any way that the chart can be intelligent enough to skip the 0
Values and display only values greater than 0.
This would clear the clutter on the chart.
I am using a pie chart for the purpose.
Thanks for Help
Andy Pope has instructions on his web sit...Excel macro to insert rows if a cell does not equal the value above that cell
I have a spreadsheet listing columns of information for many different
people. If more than one row exists for the same person, I'd like to
insert 2 rows after the row so that the group is separated from the
next group. For example, here's how the data appears now:
LastName Account# Balance
Martin 1 500.00
Martin 2 750.00
Smith 5 100.00
Thomas 9 900.00
Here's what I'd like it to look like after running the macro:
LastName Account# Balance
Martin 1 500.00
Martin 2 75...