Data Validation using List (But needs unique list in drop down lis
In sheet 1, column A is my title name while column B is person name.
Sheet 1 is my database where i do data entry in this.
In sheet 2, contains my query page. In cell A5, i uses data validation -
list, on this cell. Say in sheet 1 :
column A column B
XXXXXXX Mr A
YYYYYYYY Mr A
ZZZZZZZZ Mr A
AAAAAAA Mr B
WWWWW Mr C
DDDDDDD Mr C
But In sheet 2, cell A5, I saw in the drop down list as follows:
But i want to see this in cell A5 instead (Unique name that is) :
...i am receiving every emai twice?? can anyone help with this issue
I would appreciate any advice
Robert Sparnaaij [MVP-Outlook]
Coauthor, Configuring Microsoft Outlook 2003
Outlook FAQ, HowTo, Downloads, Add-Ins and more
Real World Questions, Real World Answers
"ETA" <ETA@discussions.microsoft.com> wrote in message
> I would appreciate any advice
...Disabling / enabling entire menu and toolbar if child window open and has focus
I have an MFC app within which a user can choose to go to the
company's web site. If they select this from the menu, a CMDIChildWnd
is created containing a CHtmlView. I need to have it so when this
window is opened, all the menus are disabled and the toolbar is
disabled. Also, when the window loses / gets focus, the menus are
disabled / enabled respectively.
Is there an easy way of doing this? How might I go about it?
Thanks for your help!
You can't disable *all* the menu items typically, because you want File>Exit, Help>About,
other Help items, etc. en...deleting multiple rows
I need to be able to delete multiple rows out of a worksheet. What I need to
be able to do is have a function/macro that can identify a string of
characters and then delete all the rows that have those characters.
For example, if row 4, 18, and 45 all had "blue" in them (assume column is
A), then I need something to go delete row, 4, 18, and 45.
At the top of my worksheet I will have several, probably about 3, criteria,
such as, "blue", "red", and "green" that will all need to be deleted.
Now here's where I really need help. A row may ...only the first 5 columns of a 10 column excel spreadsheet sort
How do I get the whole spread sheet to sort? There is a blue lox for the
first 5 columns that limits the range of the sort. How do I remove it?
Using Office 2003.
Maybe if you remove the Data|list
Select a cell in that blue box.
Data|list|convert to range
> How do I get the whole spread sheet to sort? There is a blue lox for the
> first 5 columns that limits the range of the sort. How do I remove it?
> Using Office 2003.
...Hide columns according to background fill color
I am having trouble understanding how Excel handles colors.
I have a public sub that sets a public variable, "TermColor" using the RGB function. TermColor is of type MsoRGBType.
In another module, I use the TermColor variable as follows:
Sub WeedColsByColor(ByRef Clr, ByRef WS)
Dim LastCol, i As Long
LastCol = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
'hide columns if they have one of the forbidden colors
Debug.Print (CBool(.Cells(2, i).Interior.Color = Clr))
...Changing font size in data validation drop-down lists
In Excel 2003, I created the value list on a separate worksheet and labled
the range so I could use it in the data validation wizard on a different
The list is working fine; however, the font size in the drop-down list is
too small to comfortably read.
So far, I have tried:
Making the font in the list larger.
Formatting larger font size in the active cell with the drop-down button
And, creating the list on the same worksheet.
All three remedies have not increased the size of the font in the drop-down
Suggestions are welcome.
There is n...Select a certain number of cells in a row
I'd like to have the macro to select row 5 to 10 in the active column. May I
know what is the VB code to write?
dim myRng as range
dim myCol as long
mycol = activecell.Column
set myrng = .range(.cells(5,mycol),.cells(10,mycol))
I'm not quite sure why you want to select that range. But for the most part, if
you act directly on the range (and avoid .selects), you're code will work faster
and be easier to modify.
Dolph...Can there be variable size columns in one report?
I want to create a report that has 3 sub-reports of different column widths.
Is this possible?
-The 1st sub-report has 1 column that occupies the entire width of the page
-The 2nd sub-report can fit 2 columns in the page width
-The 3rd sub-report can fit 3 columns in the page width
Subreports can have any number of columns that don't have to be the same from
one to another. Typically your columns should display across then down in
order to render properly as a subreport.
Microsoft Access MVP
> I want to create a r...Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the
columns by right clicking and choosing the desired options. In the tab
that opens there is a slider which is supposed tho set the level of
transparency of the column (selceted area). But so far i couldn't find
a way how to use this slider.
I know that there is an alternative way to get transparent bars by
creating a rectangular object formating it and the use copy -> paste.
But i wonder what is the slider for if you can't use it?
Does anybody know have an idea?
...Drag and Drop
I'd like to allow drag'n'drop operation to list box (CListCtrl), placed on
I checked Accept files in properties of this CListCtrl, created
OnDropFiles(HDROP hDropInfo) method of the dialog and placed afx_msg void
OnDropFiles(HDROP hDropInfo); in .h file.
When I drop file, cursor will change but OnDropFiles isn't called.
What's wrong with that?
The OnDropFiles() handler must be in the CListCtrl-derived class, not the
"Deli" <deli_@ANTISPAMop.pl> wrote in message
&...Combine rows to print on one label
I have imported student information into an excel spreadsheet (use Excel
2003). Each student has one row of information for each course they take. I
need to print one label for each student and each course & grade must print
on a single label. How do I combine the rows to cause this to happen?
assuming the info in the row is divided into separate columns for name,
course, grade, etc you can do a mail merge in word using the spreadsheet as
the data source
> I have imported student information into an excel spreadsheet (use Excel
> 2...Removing gray shading in protected forms (drop down box)
I am creating some contract templates for my company's sales force using
Word 2003. I am using the forms toolbar to insert checkboxes and drop down
boxes from which to select deliverable specifications. The problem is that
when a user selects an option from the drop down box, the gray shading
remains. I know the sales teams HATE the gray shading because they don't
think it looks very good when sending to customers. Is there a way to
disable the shading when an option is selected? Thanks.
You can remove form field shading altogether using the corresponding b...Column width
In Sheet 1 I have a certain amount of data, I want to select some cells and
copy them to Sheet 2 keeping the same format. When I do this, the fonts and
the colours remain unchanged, but the column width don't. I have tried paste
special, but couldn't figure it out. Is this possible?
Thanks in advance
--Select the target cell and right click >PasteSpecial>All>OK
--Keeping the target selection right click>PasteSpecial>select ColumnWidth>OK
If this post helps click Yes
"Emece"...Receiving 2 of every message
I recently paid for a hotmail account so I could have
larger storage and not get cancelled for any reasons such
as not checking it often enough. I followed the
instructions and put my service on Outlook Express. Now
I'm receiving 2 of every message. What's up?
Can I fix this?
...How do i keep the colors applied to the specific rows when sortin.
I have applied specific colors to specifc rows, but when i sort the colored
rows follow the sorting format.
...Delete drop down list
I see instructions for creating drop down lists, but not for deleting them. I
have a list that is no longer useful and would like to delete it from the
worksheet (To clarify: I am talking about removing the list from the list of
list names that appears when using the F3 key)
Select the range.
From menu Data>Validation>Select 'Any value'
If you dont have data in those cells from menu Edit>Clear>All
> I see instructions for creating drop down lists, but not for deleting them. I
> have a l...deleting duplicate rows
I have an import file that might have duplicates that I don't want to deal
with. I'm creating a temp table from the import file and want to delete the
duplicates from the temp table before proceding with other code.
Here's an example table:
create table #temp (itemnum int, firstname varchar(10), notes varchar(50))
insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
insert into #temp (itemnum, firstname, notes) values (2, ...Combining Pivot Tables
I have a data set of around 100,000 rows which I have imported into
excel in two sheets (~50,000 rows each).
The data is not in a format that excel can easily parse into a pivot
table directly from the source - it requires some formulae in excel to
be able to use a pivot table.
I have used a pivot table on each of those sheets to summarise the
data, and that works fine.
However, I would like to be able to get a single summary pivot table
from the two sheets (or from the two pivot tables).
Is that possible, and if so, how do I go about it?
"Alan" <...Add values in a column according to value in another column
How can I add the values in a column according to values in another
column? If there is any value in a row in column B, I want to include
the value of the corresponding row in column A. I'm flexible as to
whether this is ANY value (i.e. not empty) or greater than zero.
Maybe something like this =IF(B1="","",IF(B1>0,B1+A1))
"Paul Kaye" <firstname.lastname@example.org> wrote in message
> How can I add the values in a column according to values in another
> colu...How do I make a column be my default column in Access
I need to make my desricption field my default field. How do I do that? Right
not it defaults to my items field.
or in macro
>I need to make my desricption field my default field. How do I do that? Right
>not it defaults to my items field.
Message posted via AccessMonster.com
Or, if you don't want to use events, simply set your tab order from the form
R...Resize rows in a protected sheet
Hi there. I'm sending out a protected worksheet with some
cells that the users can enter variable amounts of text.
I've set the size so they can comfortably fit in about 50
words but I'd like them to have the flexibility to resize
the row height when they're editing the worksheet. But
because it's protected, they can't.
Is there anyway I can allow them to change the row height
dynamically? A button or something??
A workaround. Format the alignment in the cell to Wrap
text. Users can Keep entering text or returns (ALT+ENTER)
in the cell and it will aut...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
I am having an issue with autofitting rows in a spreadsheet of imported text
in Excel 2003. There are no merged cells in the document, and none of the
cells go over the 1024 character limit. When I select all rows and attempt to
autofit the rows to the contents of the cells, some of the rows resize
properly, and some leave one or several lines of white space below the bottom
of the text in the longest cell. I can resize them manually, but this is data
that is imported daily and runs to several thousand rows. If I widen the
column where I am having the problem, once it reaches a certain ...Excel Menu Corruption
I tried to create an excel macro for the 'Sheet Hide' command from the menu.
All I did was corrupted this command on the menu. Now it always tries to hide
the sheet in the file I had open when I tried to create the macro.
Is there something less drastic than un-installing/re-installing office?
If you post your code we would have a better chance to help you.
Did you use the macro recorder when creating the macro?
Possibly you have the sheet name hard-coded in the code.
If you replace that hard-coded name with ActiveSheet perhaps would be better.