Removing characters from cell
Hope someone can help me with this one....
I have text in column "A" a list of parts like the following.....
Is thier anyway of removing the dots in front the part number?
I dont want the points moving after the the main part of the number.
Do you part numbers all start with nhg?
If yes, you could select column A
And keep hitting the replace all button until all are fixed.
> Hope someone can help me with this one....
>...Setting a date in the Header
I have a report that I need to print every Friday, but the report is for the
following week. So, I want it to say "For the week of: Monday, November 5,
2007." How do I calculate the date to be next Monday?
IF you run the report on Friday (and never on any other day),
-- Add a control to the report
-- Set its source to
= "For the week of " & Format(DateAdd("d",3,Date()), "Long Date")
If you run the report on any other day of the week this will fail. So you
might want to use
= "For the week of " & Format(DateAdd("d&...How to count cells
I want to put a summary of Job orders in a table. (Excel (Zip) file
I am having a problem of total.
I want to make a total of only approved job orders not the all job
Also if the column contains hot / cold type, I need red and bold for
hot job order of complete row.
Also I want to be counted by job type and entity. For example, in the
total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how
can i display in the abstract table.
Can anyone help me please.
|Filename: count.zip ...Formula argument based on text
I have cells (A1:A10) populated with text ("Pass" or "Fail").
In cell (A11) I want to have a formula that returns the text: "pass" if all
the cells (A1:A10) contain the text "pass"
"fail" if any one of the cells (A1:A10) contains the text "fail".
How would I write the formula to achieve this?
In A11 enter:
"gregork" <firstname.lastname@example.org> wrote in message
> ...Unpopulated Cell Address
I'm a newbie and was wondering can anyone help with my query.
I am trying to find out how to print a spreadsheet with just the data that
is contained in certain cell address's.
Where a cell address is not populated I want the whole row to not appear in
a printed copy.
Try this idea
.SpecialCells(xlCellTypeBlanks).Rows.Hidden = True
.Rows.Hidden = False
Microsoft MVP Excel
"Mick Smith" <notsa...change the formula by changing contents of cell
I have a table that ranks a list of players by their statistics, for example,
when I change cell B2 to "walks", my formula (large) lists the player with
the most walks in cell B4, 2nd most in B5, 3rd in B6... that works well
because more walks are good. But, when I change cell B2 to "strikeouts" I
want it to list the player with the fewest number of strikeouts in B4, next
fewest in B5 and so on.
Is there a way to change the "large" function to the "small" function within
I have set up a helper cell (C2) that changes fro...query-based distribution group not showing in Outlook
Software: Exchange 2003 standard and Outlook 2003
I have created a query-based distribution group and it isn't showing up in
Outlook Address Book. If I create Distribution groups they show up in the
Address Book. How do I get the query based groups to show in the Address
Under Exchange Advanced I have confirmed that the check box to hide list is
On Tue, 27 Dec 2005 10:54:52 -0800, "Colin" <email@example.com>
>Software: Exchange 2003 standard and Outlook 2003
>I have created a query-based distribution ...Preventing Column Cell Values from Printing
I have a summary worksheet with several columns of information.
There is one particular column (E25:F39) where I don't always want to print
the values. Is there some simple way to toggle this column's values on and
off just before hitting the print command?
I was thinking of using an adjacent tick box outside the print area, which
when ticked would print the column values, and vice-versa.
I'm looking for a formula that would examine the tick box, and if the tick
(letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the
values in column (E25:...Conditional Formatting #2
I have a spreadsheet that has about 1200 rows many of which need to have
conditional formatting. I had a template set of rows that I was transferring
the conditional formatting from and it worked for 20 or so rows. Then in
attempting to both copy/paste and drag formatting even further I noticed
that the conditional formatting was not being transfered to the target
cells. I have even tried doing one row of formatting at a time but it seems
like it will not allow any more conditional formatting. The spreadsheet is a
good size (about 2MB). I recieve no error messages about this. It simpl...Calculating dates
A quick question before i'm off to bed :)
I got several dates, all in the future. I'll just give an example, cause
it'll be hell to explain otherwise :p
I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I need to
know how many days have past from 26-07-2004 untill now. So, day and month
stay the same and year needs to change to the year before the current one.
Then count the days untill the date now.
I need this to calculate accrued intrest, should someone wonder (or if that
makes it easier to understand ;) )
Preferably, a short function (if possible) since it...How do I change the value in cell based on a future date
I would like certain cells to be cleared after a certain date
Put this in the ThisWorkbook code module (Right-click the workbook title
bar and choose View Code):
Public Sub Workbook_Open()
If Date > #12/15/2004# Then _
Note that this won't work if the user opens your workbook with macros
Note also that there's no way to prevent a user from accessing your data
- i.e., they can set their clock back and open the file. You can make
things more difficu...IF COUNTIF & COUNTA on Filtered Visible Cells #7
Hope this will help.
Correction to Last Posting:
My Helper Column "U" increments one Row at a time and says:
Check from Row above Current Row back to beginning of my "V" Range: i
the Room was used previously give me the Last (MAX) time it was used b
returning the relevant Row Number of the (text based) Group Nam
(Executive, Trainees, Manager, Graphics, etc.) that used the Room Last
The Helper Column "U" then passes this data to the Formula in Colum
"T" - it checks for the criteria within the specified Range and doe
the COUNT calculation us...Problem with a date and concatenation
I got some data out of one of our in house databases and it comes out
as 01/022206, for example..a missing second "/" (we need to FIX that
damn databse too)
I have thousands of these rows, and I need to do some analyses by
date, so I need a correct date.
So I was going to use text to columns> fixed width and separate the
01/02 from the 2006, then I would concatenate with
(replacing the actual contents with the cell name of course)
But when I do the text to columns, I get the 2006 but I I don't get
01/02.. I get a...Check Number format
In Great Plains our checks were initially set up with 20 zeros. For some
reason when running the Vendor check request report on some of the PC’s (on
screen and to the printer) the entire check number does not show up so it was
requested that we remove 8 of the leading zeros. I went into
Cards>>Financial>>Checkbook and pulled up the appropriate checkbook and
removed the leading zeros from Next Check Number and Next Deposit. When I
print the report, the zeros are still visible. Am I changing the correct
field or will this change not affect existing numbers in the report I...Changes to requested ship date in SOP should roll down to lines
When changing the requested ship date at the header on open SOP orders you
should have the option to roll this change down to existing SOP lines.
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 Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/News...Data within a cell
If names are in cells as "last name, first name, middle initial" can a macro
be run to change the order to "first name, middle initial, last name"?
A formula will do
Assuming you have spaces between commas and names,
Then just copy down. If you want this more permanent, copy the cells with
formulas, right click, paste special.
*Remember to click &q...Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need.
Items Column is List for Valid entries in table below
Prices are the numbers I want entered when I enter the Item
If I enter Oranges from Dropdown List in A16 , I want Excel to automatically
enter the number 7.95 3 columns over in D16,or if it is quite a bit easier,
just 1 column over in cell B16
Example of table I want:
My Entries: Excel Enters
...Transfering a changing cell value progressively through a workbook
I am creating a payment application form in excel 2007 using windows 7. Each
worksheet represents one months invoice. Say I have a formula in "sheet 1/
cell Q7" that sums the total billed to date for a particular budget line
item. This value will be transfered to a the next months payment application
"sheet 2/ cell K7" this becomes the total amount of previous applications,
then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7,
and so on and so on, until the completion of the job. How do I acomplish
...Always Reply in HTML Format
Is there a way in 2003 to reply in HTML always
redgardless of what orig message has been sent in? I
know the security issues and the reason why its set up
the way it is, just curious though.
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the discussion intact. Due to
the SWEN virus, all mail sent to my personal account will be deleted
After searching google.groups.com and finding no answer, BigZ asked:
| Is there a way in 2003 to reply in HTML always
| redgardless of what orig message has been sent in? I
| know the security issues ...shortcut ket to change formatting of text
whats the shortcut key to change numbers to dates, and vice versa.
Message posted from http://www.ExcelForum.com
This may be the sort of thing you want:
Apply the Date format with the day, month, and year.
This is a quote from Excel Help ( use "Keyboard shortcuts" for th
Message posted from http://www.ExcelForum.com
ctrl-1 will pop up the Format|cells dialog
ctrl-~ (ctrl-tilde, aka ctrl-shift-backquote on my USA Keyboard)
will format a cell as General
ctrl-# (ctrl-shift-4) will format as d-mmm-yy
Try searching excel's...converting plain data to table format
It has been a while since I used excel but I am almost sure there is a way to convert just plain rows and columns of data into a table format. This way I believe that adding rows to this block of data is as simple as tabbing after the last cell and it should just drop down to the next row and insert one??? correct??
Excel should recognize a contiguous range as a table, or list. Include
headings in the first row, and leave at least one blank row and column
between the table and other items on the worksheet.
You can use the built-in data form to add records to the table
(Data>Form). Or,...Preventing users from saving forms into unprotected formats
I need to stop users from saving forms into unprotected formats, e.g. RTF
(believe it or not, my users like to mess about with form layout, creating
business interoperability problems) I have successfully intercepted the
FileSave and FileSaveAs commands. I also need to intercept the Save As
dialog which comes up for a new dialog when the file is closed, so I'm using
a BeforeDocumentClose event handler. I would like them to be able to choose
not to save, so I give them a YesNoCancel message, but I can't prevent the
regular message coming up if they click No on my message...perpetual dates
Is there a way to have excel automatically add a new date to the end of a
column each day? My goal is to have an automatic date in column A and daily
closing prices for the S&P500(in column B) corresponding to those dates that
are updated via web query. Thanks so much!
Dim LastCell As Range
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
If LastCell.Value = Date Then
'already opened and populated
With Las...Switch format characteristics cell-by-cell?
I want to do something like this:
IIf([table_a].addr1 <> [tableb].addr1, SwitchToBold([tablea].addr1),
Is there such a function or set of functions, e.g. switch to italics
or change the font entirely? I can't find it and am not entirely sure
where to look.
Thanks in advance.
On Thu, 30 Aug 2007 11:30:14 -0700, Jonathan Ball <firstname.lastname@example.org> wrote:
>I want to do something like this:
>IIf([table_a].addr1 <> [tableb].addr1, SwitchToBold([tablea].addr1),
>Is there such a function or set of functions, e.g. swit...How many characters per cell
How many characters can you fit into one cell.
We are having a problem with text being cut off when printing, but i
shows up when viewing on either Print Preview or normal view
aletoconstco's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3533
View this thread: http://www.excelforum.com/showthread.php?threadid=55334
Excel Help on "limits" or "specifications" reveals that Excel will allow
32,767 characters to be entered in a cell.
However, it goes on t...