ADVANCE FILTER SORT
Hello! I have this form called "frm_FilterMain" with a button that execute
this code "DoCmd.RunCommand acCmdAdvancedFilterSort" to open the advance
filter sort fom named by access as "frm_FilterMainMain1 : Filter". After I
completed selecting all the fields I wanted, key in all the cretirea, sort,
etc then click the apply filter button this advance filter sort fom
"frm_FilterMainMain1 : Filter" is still there and doesn't close. Is there
any way to close this form as soon as my "frm_FilterMain" is displayed and
active. Could anyone...Copying Cells on a diagonal?
I need to copy cells on the diagonal for a large data set as follows
P1 P2 P3 P4 P5
Group 1 P1 5 4 5 4 3
Group 1 P2 5 4 5 4 3
Group 1 P3 5 4 5 4 3
Group1 P4 5 4 5 4 3
Group1 P5 5 4 5 4 3
Group 2 P1 5 4 5 4 3
Group 2 P2 5 4 5 4 3
Group 2 P3 5 4 5 4 3
Group 2 P4 5 4 5 4 3
Group 2 P5 5 4 5 4 3
I need P1's Self score, P2's Self Score, P3's self core etc for each
team lined up in a single column.
which for the above data would be
Is a formula like
the best way to do this and then cut and paste the formula for each
Than...Linking a range from an external workbook to a single cell
I am linking a series of workbooks to a summary work book. I need to
know if I can enter a cell range from the external workbooks to a
single cell in a summary workbook.
In this instance I have 12 months data stored in workbook A in 12
seperate columns. I know I can link each cell in workbook A to a
corresponding cell in workbook B. However this requires me to link each
of the 12 cells in workbook A to the 12 in workbook B. I would like to
enter in the first cell in workbook B the start of the range of 12
cells in workbook A and automatically generate the same range in
I have tri...auto numbering spread throughout a document
I have word 2007. I'm working on a process memo with numbered steps. The
steps are auto numbered.
Within this document also have a series of internal controls that are
numbered: "internal control 1.", "internal control 2." etc. Is there a way
for me to auto number "internal control" so that each time I have to add one
to my document it's automatically number and if I remove one it renumbers?
These internal controls appear throughout the document and do have a specific
Does this make sense?
Create a Style for ...Problem with auto correction in cells
When I type ex. 0771 in a cell, excel auto change it to 771.
How do I avoid this
Message posted from http://www.ExcelForum.com
if you need a leading zero you have some options:
1. If you don't need to calculate with these values either
preformat the cell as 'Text' or enter the value with a
2. If you need these values as numbers you may apply a
custom format to these cells. In your case for example the
>When I type ex. 0771 in a cell, excel auto change it to
>How do I avoid this?
&g...How to count cells excluding repeat info
How do you create a formula or count information in a range of cells an
exclude any repeat information? For example, I have vulnerabilitie
listed in Column A and IP addresses listed in Column B. I want to b
able to count how many machines are in the spreadsheet or how man
Column A Column B
4 Vulnerabilities; 6 Machines
I would appreciate any help in this ...Split cell
Is it possible to split several cells in a column, but not the whole column?
"Split" means what to you?
Gord Dibben Excel MVP
On Wed, 12 May 2004 12:43:01 -0500, "Cathy C"
>Is it possible to split several cells in a column, but not the whole column?
robably not, but what, exactly, do you mean by
"split several cells" ?? an example, perhaps?
>Is it possible to split several cells in a column, but
not the whole column?
&...Pasting an object into a cell with a drop down menu
I have made simple Macros to paste objects (Autoshapes) into various
' Keyboard Shortcut: Ctrl+q
I would like not to use Macros but instead use a Drop Down Menu.
Objects cannot be pasted "into" cells.........only lay on top.
To have a picture show up based on VLOOKUP when you select an item from a DV
list or type in a name see JE MCGimpsey's site.
http://www.mcgimpsey.com/e...Alternating Row Formats When Cell Value Changes
I have a list of schools with the school names in column B and the school's
district number in column C. I'd like to alternate the row shading each time
the district number changes. Is there a formula that can be used for
conditional formatting, VB code or some other method to do this?
Thanks for any help or advice,
My Excel add-in "Shade Data Rows" does that.
It shades groups of like valued rows or will shade by every nth row.
Choice of shade colors and an option to skip hidden rows.
Comes with a one page Word.doc install/use file.
It is free upon direct req...Protecting Selected Cells and Functions
I have a worksheet. In Cell B2 is a Data validation box Listing a range
of colleagues names( DRop Down Menu). On selection of a name in B2, the
contents of the whole worksheet changes.
I like to Protect the worksheet for:
1) Hiding the formulaes
2) And most importantly preventing editing of the contents of any other
cell (except B2).
and yet be permiitted to:
3) Select contents in Cell B2 (Data Validation Box)
4) Select Auto filters in Row 4
I've tried using the the Tools/ Protect worksheet menu, ticking Select
Lock Cells, Select Unlock cell, use auto filters. And in in
Format/Cells/...Reducing the number of lines of code required
I'm trying to create a form for work and I have the following lines o
code created within it... I have another 20 checkboxes which wil
require similar lines of code. Is there any way to cut down on th
total number of lines required (as it will end up being a ridiculou
CheckBox5.Value = False
TextBox14.Enabled = False
TextBox14.BackColor = RGB(128, 128, 128)
CheckBox6.Value = False
TextBox15.Enabled = False
TextBox15.BackColor = RGB(128, 128, 128)
CheckBox7.Value = False
TextBox16.Enabled = False
TextBox16.BackColor = RGB(128, 128, 128)
CheckBox8.Value = False
TextBox17.E...missing "advanced" button in export
Using A2003 with A2k format.
I'm trying to create an export spec. I've done this before but it's been
quite a while (and probably under straight A2k).
I remember going to the export option and in the screen where you specify
the file name and type, you click the "Format" checkbox and then at some
point you get a wizard with an "advanced" option.
I'm not even getting to the wizard. It seems to be missing. I checked my
installation of Access and everything seems to be installed.
Can anyone tell me what might be wrong here?
Never mind. I se...The terror of advancing cell numbers!
I apologize for this really obvious problem, I haven't encountered i
before so its really ummm "bugging" me.
I am using the vlookup function, and in the formulae it refers to
range of figures A1-Z100 in another worksheet. If you copy and past
the formulae to find the next piece of information xcel automaticall
advances the the references, so that now the range of figures it refer
to is A2-Z101, which means that the vlookup function is no longe
looking in the full range of figures. How do I stop it from doin
In the past I just did one row, manually changing...associate alphabet letters with numbers?
I want to associate letters of the alphabet with numbers so that when a
letter is put in a cell, a numerical value can be associated. Sort of
like a phone pad-I want it so that you could put the letter in a cell
and a designated number would automatiocaly show up in another cell. Is
there a way to create a formula that can do this? Help would be greatly
appreciated. Thank you.
Loriandme69's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28971
View this thread: http://www.exc...worksheet cell references
The below is an example of a formula that I was shown to get values from
other worksheets into my current worksheet, using a cell to reference the
appropriate worksheet needed. Is there a way to write this so that the
additional string cell reference is not absolute.
I would like to copy and paste, but the only reference that changes is the
A1. I know that if I put a $ in front of the 1, the A$1 will always
reference the same cell, when copied and pasted, for the worksheet name I
need to use, but I need to get different information from sequential cells in
=INDIRECT...Conditional formatting depending on sub string content i cell
This has been eluded me all afternoon, I just want to mark out cells
in Excel that contains a specific sub string.
I have thousands of rows all containing small text strings. I would
like to mark the once containing one specific word with blue text, and
other cells containing another specific key word with a different
Very simple, don't care about corner cases (containing several key
words etc.) Mostly I'm annoyed that I cannot solve this simple
problem. I just cannot get Excel to understand any of the formulas I
try in conditional formatting.
Can anyone h...Advance filter not working #2
I have data lik
Sr. no., Date, name, bill no., item, rate, bill amoun
When I use advance filter with above data it works some times only
I have created criteria range in A1: B7 and data are A3:B135 when I put criteria in name it works some time and after some days data done not return any information about name and other data
...Excel Chart How do I create Y axis to represent numbers I am using
I am attempting to create a chart that is populating its data from another
worksheet in the same book. I am pulling in numbers that range from 75000 to
3000000 and I would like my y axis to be 50K to 350K in multiples of 25K but
I can not figure out how to adjust the axis key.
Thanks for your help in advance.
Select the axis and press Ctrl+1 (numeral one).
Excel 2003 and earlier:
In the dialog that appears, click on the Scale tab, and enter the values you
want for min, max, and major unit, and don't check the corresponding Auto
Check Fixed for each of mi...How do I quickly enter the same number into different cells?
I am entering a "1" into various cells (collating answers from
questionnaires). Is it possible to fill the cell with just a mouse click
rather than having to type in number 1 each time? Many thanks for your help.
Here's a neat trick:
Hi-light all the cells you want to enter.
type 1 and then CNTRL-SHIFT-ENTER
You will fill all the cells at once
> I am entering a "1" into various cells (collating answers from
> questionnaires). Is it possible to fill the cell with just a mouse click
> rather than...How do I filter or sort by cell shading?
I have been sent a 2000 row report with just the pertinent rows highlighted
by solid color shading (about 300 rows scattered throughout). Is there a way
to filter down to just those rows that are shaded, or to at least sort to
Have a look here
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"urnojfk" <email@example.com> wrote in message
news:...read a column of names and place a number in the next cell
I want to automatically read a column of suburb names and add a column of
matching street directory map numbers. I have to do this monthly on several
If you have a table with the suburbs and matching map numbers you could use
the VLOOKUP Function.
Assume on sheet2 you have column A with suburbs, column B with matching
On sheet1 you have suburbs names in Colimn A.
In B1 enter a VLOOKUP formula
Double-click on the fill handle to copy down.
Gord Dibben Excel MVP
On Wed, 7 Dec 2005 14:43:03 -0800, "Judy ...insert an order number when another sheet opens
I have a form and every time I open it I need it to create a new number. Is
this possible. also if I put it a number that does not correspond to an
other number can it give me a warning.
Thank you so much for your help
...Worksheet name in cell
Is it possible to set the worksheet name for a cell. Eg.
In cell A1, how to set A1="Sheet1" if Sheet1 is the
worksheet name. If the worksheet name changes to "abc"
then A1 should reflect "abc".
Any help in this regard is appreciated. Thank You.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Giri" <firstname.lastname@example.org> wrote in message
news:0...Checking for black cells
I have the following formula on my worksheet:
=ROUNDUP((C3/D3) + (F3/G3) + (I3/J3) + (L3/M3) + (O3/P3) + (R3/S3) +
(U3/V3) + (X3/Y3) + (AA3/AB3) + (AD3/AE3) + (AG3/AH3) + (AJ3/AK3) +
(AM3/AN3) + (AP3/AQ3) + (AS3/AT3) + (AV3/AW3) + (AY3/AZ3) + (BB3/BC3),
The formula doesn't work if any of the cells are empty, how can I
alter it so that it checks for empty cells and only calculates
Thanks for your advice
Well, one way would be:
=ROUNDUP(SUM((IF(D3=0,0,C3/D3)+IF(G3=0,0,F3/G3)+IF(J3=0,0,I3/J3)+IF(M3=0,0,L3/M3)+IF(P3=0,0,O3/P3)+IF(S3=0,0,R3/S3)+IF(V3=0,0,U3/V3)),(...Cell Reference locking
I am trying to lock a cell reference from one sheet to another.
Cell A1 on "sheet 1" needs to ALWAYS be referenced by cell D5 on "sheet
I have tried using $ signs and using the sheet protection to no avail.
What happens is when ever a user moves the information via drag and drop or
copy and paste (other than copying and pasting up in the tool bar) the cell
reference moves with it.
User highlights the cell B1 on "sheet 1" and drags it to cell A1.
On my machine what happens is that Cell D5 is now referencing B1 and the
cell that was refe...