how to keep cell rounding to 2 places even when someone else pastes number with more.
At work, i receive spreadsheets of bonuses that can be derived from
formula. however, when i copy and paste the info into our file fo
payroll, it can only be 2 places, otherwise it kicks out an error.
now, i've been here almost a year now and it's been near impossible t
get the people who submit the file to make sure they ROUND the numbe
to 2 places.
i'm wondering if there is something i can do in the cell that when the
paste their number in it, it automatically rounds.
or if that's not possible, if there's a way for me to highlight
column then have it round all t...Zero filling a number field
I have an auto number field that I want to zero fill to six digits but
can't figure out how. Any ideas?
You don''t say where you're trying to do this, but basically
>I have an auto number field that I want to zero fill to six digits but
>can't figure out how. Any ideas?
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000/2003
Message posted via AccessMonster.com
You can't do that wi...Date Elimination
I have a worksheet with mainly dates in column A in the format of '25
Aug 2008'. Is it possible with a macro or similar to delete lines
beyond a certain date (2 years hence)? Basically, I'm not interested in
data more than 2 years old.
This would eliminate a lot of data and make for a more viewable worksheet.
Do Until ActiveCell.Value =3D ""
dt =3D Date - 730
If ActiveCell.Value <=3D dt Then
End ...Optimum number of pages
I am doing a book that should be about 200 pages (each page is half an 8x11).
I now have 116 and when I try to add pages (on the INSERT menu) It says ok
and then cuts me down to 16!
I can't find anything that tells me how many pages one file can handle in
The last book I did was on the original version of publisher! I can split
the publication into two or more files, and probably will have to because
pictures aren't inserted yet, but need to know about file size so don't have
to do everything twice. Thanks
How much memory do you have? What about your s...combo
have placed a subform, which gives the Price. It is based on the Query. I
have given a criteria in the query like [forms]![mainform]![productid].
So, when I select a product from a combo box, on the main form, I should see
the price on the subform. The problem, is it does not refresh. It still shows
the same price for the earlier product. But, when I move, it gets refreshed.
Can I have a code , so that I should see the price as soon as I select the
product from the combo box .
On the After Update event of your combo box put the following:
[Forms]![frmMainForm]![sfrmSubfo...How do I find the closest number to a given number in an unsorted.
I need to find the first occurence of a number in an unsorted list (stock
prices by date). I need to be able to do this from within a formula. The
lookup functions and Match functions don't work because of the inability to
sort. Any help would be appreciated.
if you're looking for an exact match. If you need a closest match try
the following array formula (entered with CTRL+SHIFT+ENTER):+
Regard...How do I selectively sum up a series of numbers?
I have a column filled with numbers, but I only want to sum up a few of them.
Let's say I have numbers filling A1:A10. Let's say I only want to sum up
the numbers located is A3 and A4. I'd like to ability to enter something in
the b column (e.g. "y" in cells b3 and b4) and have a formula that adds up
the values in the "A" cells that have a corresponding "y" in the "B" column.
This formula would allow me to selectively choose which cells needs to be
What is the best/cleanest way to write that formula? Thank you.
"No Sp...Eliminate unused columns
I have a table of columns of data per week, which I convert to a table of
columns of data per month. This leaves 3 or 4 blank columns between the
months. Is there a simple way to consolidate the month columns into a
contiguous region, eliminating the unused columns between the month columns?
Or... is there a smarter way to do the conversion than sum across an fill
One approach is to use a macro:
if your empty columns are completely empty you could use this macro. Hit
Alt-F11, Click Insert/Module (check the project window to make sure your
workbook is the active project before...How to convert an account ta a lead
I have had 100 % of my leads added as accounts by mistake. Can I convert them
I don't think there's a way to turn an account into a lead. Could you
delete them all and then re-import them as leads?
"Mats Nilsson" <MatsNilsson@discussions.microsoft.com> wrote in message
>I have had 100 % of my leads added as accounts by mistake. Can I convert
> to leads?
You should be able to delete the acount and contact, then go to the
closed leads view and reactivate the lead. ...Excel should add numbers without leading punctuation
Why can't Excel add numbers in a cell without a leading '=' or '+'. For
example, 3+2 returns a cell with "3+2" instead of "5".
Transplanted Buckeye wrote...
>Why can't Excel add numbers in a cell without a leading '=' or '+'.
>example, 3+2 returns a cell with "3+2" instead of "5".
Turn on Transition Formula Entry, Tools > Options, select the
Transition tab in the Options dialog, check the entry for Transition
formula entry, click OK.
Be warned - this will make date entries much more difficult beca...ignore spaces
Eg. I have all the courses numbered uniquely like first course of year
2008 course nam SMS will be given course no. 001SMS08 by the user. now
if the user enters 001 SMS 08 and saves the course number. Then some
else when queries about course no. 001SMS08 then it will not show any
So how to ignore spaces typed (accidently) between characters in
the text field of a form save the value with all space removed in
On Sat, 23 Feb 2008 08:53:21 -0800 (PST), Rajtomar wrote:
> Eg. I have all the courses numbered uniquely like first course of year
> 2008 course nam ...Placing an CHtmlView on a Dialog Box.
I have created a dialogbox . On Above that I placed a Window Created
from CWnd , parent of this window is my dialogbox.
Now My task is placing a CHtmlView on this Window(Window Created from
CWnd). Is it possible.
> I have created a dialogbox . On Above that I placed a Window Created
> from CWnd , parent of this window is my dialogbox.
> Now My task is placing a CHtmlView on this Window(Window Created from
> CWnd). Is it possible.
You can use CHTMLCtrl:
...End of paragraph line spacing
The last line of a paragraph always has a larger space between the lines then
the body of the paragraph. Any ideas why?
Format, Paragraph, be certain you have 0pt before and after the paragraph.
"cybun" <email@example.com> wrote in message
> The last line of a paragraph always has a larger space between the lines then
> the body of the paragraph. Any ideas why?
Thank you very much. Worked great.
"Mary Sauer" wrote:
> Format, Paragraph, be...Adding a Text Box
Does anyone know how to add a text box to an Excel Document?
I don't have a clue as to how to begin so if you could be descriptive,
that would be helpful.
I want someone to put in a value to the box, hit 'enter', and then have
the results of their value come up ...almost like a query...
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
Go to View-->Toolbars and select Control Box. The Control Box toolbar will
now be shown. Howver you...Excel is defaulting to Number format instead of General format
The programme for 2007 has recently been installed, and my cells are all
formated to Number instead of General, and Excel won't let me change it back
to General. We tried to uninstall and re-install but this did not help.
Maybe you have a workbook template file named book.xlt (or .xltx or .xltm) in
your XLStart folder.
This template file is used as the basis for starting new workbooks.
You could try to edit it (and change the number format) or move it (or delete
it). Then restart excel to test.
> The programme for 2007 has recently been installed, a...Finding missing numbers in a list
I have a series of client account numbers that range from 001-999. I
first imported the client information from Timeslips which where we
keep client information into an Excel spreadsheet. What I need to now
do is create a macro or a query that will tell me what numbers are not
being utilized from the spreadsheet that I created but I don't know how
to do this. I have only created one macro before and that was with
help from an instructor so I don't know where to begin can someone
please help me with this.
If you're just looking for a report...
Create a new worksheet
Our inventory item numbers have a format of ###-##### where the first three
positions are numbers that together represent the manufacturer. The five
digits to the left of the dash are generally used sequentially. For example
the first inventory item assigned to manufacturer "101" is 101-00001, the
second 101-00002, etc.
We have had numerous instances where the same part number was entered in
multiple times. When we discovered this, we deleted the duplicate entries but
have gaps in the numbering sequences. So I now might have 101-00001,
As I enter new it...Searching LIKE Numbers
I have an Unbound text box on a form that searches my database for exact
matches. On After Update Event, it moves the focus to NumberID and if a
match is found, displays the record on the form. If no match, Microsoft
Access displays a message stating that "You can not go to the specified
record". If No record is found, I would like to open another form and
display the CLOSEST MATCH(s). For instance, I search 1234ABC and it does not
exist. However, 1234A does exist. I want to show this record as a possible
choice. This where I need some help. So far I have the following ...URGENT HELP NEEDED PLEASE
I am no longer able to see /search for disqualified leads. We used to be
able to see any disqualified leads but now i cant. Is there some settings in
lead views that i need to check or confirm.
Can someone pleaseeeeeeeeee advice me on this.
Are you talking about quick find or advanced find? Quick find does not locate
closed leads(Qualified or disqualified) you can only see them by using
Advanced find to run the searches. This same functionality exists for all
inactive objects in all entities. You can build a saved query to run however
the quick find will still...Supress Scientific Numbers??
Is there any way to get Excel to not display numbers in scientific
format?? To force it to display 1.86E+08 as whatever the number really is??
Have a look at
Format / Cells/ Number
> Hi ...
> Is there any way to get Excel to not display numbers in scientific
> format?? To force it to display 1.86E+08 as whatever the number really is??
What "the number REALLY is"!!! if X=1.86e-3 then that is what x reallly is.
The decimal sy...eliminating duplicates
I need to elimnate duplicates the following script. I believe what is
creating the duplicates is the provcont table, there are different provcont
ids for each record. Thanks in advance.
INNER JOIN PROVIDER ON PROVCONT.PROV_ID = PROVIDER.PROV_ID
INNER JOIN PROVSTAT ON PROVIDER.PROV_ID = PROVSTAT.PROV_ID
INNER JOIN PSTATUS ON PROVSTAT.STATUS = PSTATUS.STATUS
WHERE (PROVIDER.PROV_PATH = 2) AND ...Column Headers showing numbers
My Excel spreadsheets are opening up with numbers at the
top not letters. I'm sure I must have clicked something!
Thank you in advance
Tools | Options | General Tab | Uncheck R1C1 Reference Style
"Julie" <firstname.lastname@example.org> wrote in message
> Hello there,
> My Excel spreadsheets are opening up with numbers at the
> top not letters. I'm sure I must have clicked something!
> Thank you in advance
I have a table with 7 different columns of team members on different teams.
I need a count of all team members. How do I query seven different columns
and eliminate dups?
You've discovered yet another problem with denormalized tables. If you have
7 different columns, each containing the same basic information, you have a
If you can't go back and redesign your tables, the easiest approach would be
to create a UNION query that normalizes the data so that it's in only 1
SELECT Member1 AS Member
SELECT Member2 AS Member
FROM MyTabl...How do I count number of cells with info??
I'm writing a list of numbers, in the end I wand to count how many numbers
there are, I DON'T want the sum, just to know how many ther are. How can I do
"HJK" <email@example.com> wrote in message
> I'm writing a list of numbers, in the end I wand to count how many numbers
> there are, I DON'T want the sum, just to know how many ther are. How can I
Thanks, it works, can I also count specifik numbers or letters??
"Biff" skr...eliminate message from append query to eliminate duplicate record.
Create a new query in design view. Add the two tables for which you
wish to compare. When you see the two tables in the design view of the
query click on one of your 2 join fields and drag to the corresponding
join field in the other table (a line should appear) then double click
on fields to add them to the query. For your comparison field Your
going to have to explain how you want to compare the two fields. What
type of field is in table B, and what kind of comparison do you want
to do? You could start with something simple like entering