Creating a new field based on conditions
I have a database that tracks insurance information for our various vendors.
Each insurance type has 2 fields - a requirement field (yes/no), and an
effective field (some show an expiration date, some are yes/no). I have
created a query that will return only the records for which insurance is
required but is expired/missing. My problem is that I want to create a new
field that is calculated based on the values in the other two fields in order
to make the resulting report more user-friendly.
For example, if GLRequired is True and GLExpiration is <Now(), I want the
new field to say...Hyperlink via indirect cell reference
I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.
For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
In another sh...Save formatted text from RichEdit control to rtf-file
How can I save the text from Rich edit control (2.0) to *.rtf , *.txt ,
I tried to get the buffer and putting the buffer to file, then saving
but the text in the file is something different.
Please let me know what to do?
Here is the Code I ma using:
mFile.Seek( 0, CFile::begin );
int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength();
HWND focusWnd = ::GetFocus();
cBuffer2 = m_oChatMessageControl.GetSelText();
LPTSTR...How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the
cells that head up the first page. I've done it before in school but can't
remember what it is called or how to do it...that's why I'm doing this.
Anyway, I want these cells to print off on each new page without having to
type them on each page. I hope that makes sense and I hope that someone can
If you mean for printing do file>page setup>sheet and select rows to repeat
otherwise for viewing you can select a2 if the headers start in row 1 and do
window> freeze panes
How can I rename column A to read "bills" instead of the letter A?
You can't. The closest you will get is to hide column headings, via Excel
Option, and then create your own.
"shoe" <email@example.com> wrote in message
> How can I rename column A to read "bills" instead of the letter A?
you cant change the headers or row labels but you can define you data as a
list (or table) and the headings can then be used to refer...merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell?
Not possible I'm afraid. Try placing the dat from both cells into one
and use "Center across selection" under Format>Cells>Alignment
Merge cells always end up causing grief. they are best avoided.
***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
"bob" <firstname.lastname@example.org> wrote in message
> How can I merge 2 cells without losing data from the other...Create static text from cell reference
I have two columns of text which I'm combining in a third column using the
formula (for C1, for example) =A1 & char(10) & B1
This gives me the contents of A1 on a line above the contents of B1 and
What I NEED to do is somehow create column C as TEXT, not as a REFERENCED
data from columns A and B. How do I create a cell that contains the actual
TEXT content of another cell instead of a REFERENCE to the other cell?
Select all the cells in "C" that have content. R-click them and select
"Copy" then r-click again, sele...Cell Format #4
Is there a way to have a cell format based on contents of an i
if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma
I want the If statement to test a condition, return contents of th
correct cell and format automatically.
Any help is appreciated
bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177
View this thread: http://www.excelforum.com/showthread.php?threadid=26133
You can't change the fo...Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How
do I do that?
x-axis y1-axis radius(y1/2)
0 0.00 8.0000
1 0.25 8.0242
2 0.50 8.0691
3 0.75 8.1281
4 1.00 8.1989
5 1.25 8.2803
6 1.50 8.3716
7 1.75 8.4729
8 2.00 8.5832
divide the y1-axis by what?
2 as an guess with y1-axis in column c
in the y1/2 column(d?), enter
> I want to divide the y1-axis column and save it to radius (y1/2) column. How
> do I do that?
> x-axis y1-axis radius(y1/2)
> 0 ...Formatting #13
How can i have codes in this form 00.00.0000.00, & i wanted to sum to the values below like next code, 00.00.0000(+1).00 I'm tired to format but always sum in the last 2 digits 00.00.0000.00(+1), what can i do
Someone can help me
How did you put 00.00.0000.00 in the cell?
Did you type 0 and then give it a custom format?
If yes, try changing your custom format to:
Then add 1, but make sure that the resulting cell also has this custom format.
This is really a funny formatted number with 2 decimal places now.
> How can i ...Send out blank reply
Recently, after receiving a message, my Outlook 2000 is
sending out a blank reply message to the sender
automatically. Would this cause by setting or virus?
...conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have
a notifiction sent to you when a date on a spreadsheet has expired?
> how do you add a phrase to a field if the filed is blankl,
What "phrase" do you mean? A Comment? A value?
also, can you have
> a notifiction sent to you when a date on a spreadsheet has expired?
Maybe you can apply an open event (date to be tested being say in F1):
Private Sub Workbook_Open()
If Range("F1") < Date Then
MsgBox "Date expired"
...cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop
selecting cells. Have tried double clicking, playing with the Function keys,
all sorts of things, but to no avail... don't want to force quit.
Any clues? TIA, Geri
See David McRitchie's notes at:
"Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in
> All of a sudden the mouse is acting like it is held down, a...Need Help with drop down list with conditions...
I need help.
I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index:
The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton).
The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1.
And Last but not least, the 3rd list would...Average of logic cells
I used a logic test to determine some levels from raw scores. For EG
>120 =5, 119-110 = 4, etc. I now want to dtermine an average score of
several of the the results from the logic tests but it doesnt seem to
work. (AVG does not recognise cells with logic tests) Can anyone help,
ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245
View this thread: http://www.excelforum.com/showthread.php?threadid=489704
hi, ckdkvk !
> I used a logic test to determine so...Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet.
(Don't use my reply address - it's spam-trap)
"MT" <MT@discussions.microsoft.com> wrote in message
> I have a project to do. I have to create an input ...Cell References..
I have a 12 month rolling report with a seperate worksheet within th
workbook which refers to the column containing current month's Numbers
When I "Cut" Column C (which contains the oldest Month) and "insert
column C between N & O it shifts my cells left and all I need to do i
input all of the current Month Data into Column N. The formulas al
remain intact and everything is peachy. Until I goto the Workshee
that refers to the Current Month on the 12 month rolling report.
My problem is that when I shift the columns on the "Report" workshee
it chages the cell...Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format.
its been helpful to me so maybe it will do good for you too:
how to create a simple macro within Microsoft Excel, and then how to use =
that macro to calculate a single cell value.
I have two fields in a subform, "User" and "IT Announcement"
I would like to do conditional formatting to this effect:
On ther "User" field:
If "IT Announcement" = Yes then make the "User" field turn blue (I would
choose the color blue from the conditional formatting selection.
How would you write this?
On Tue, 23 Oct 2007 14:59:01 -0700, Iram wrote:
> I have two fields in a subform, "User" and "IT Announcement"
> I would like to do conditional formatting to this effect:
> On...Excel number formatting #2
I receive spreadsheets with separate columns of numbers
and text. The problem is that the numbers column is not in
number or general format (when sorting behaves like text).
Is there a way to turn those columns into numbers (except
stepping into each one separately)? When I just highlight
the number in the cell and hit enter, the cell
automatically becomes numeric (I'm looking for a more
You can do this:
1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit,
Copy. Now highlight entire column(s) that you want changed to numeric, and
sel...Changing named range reference depending on a cell's content
Where to start?!
I've got the following formula pulling data in from a secon
spreadsheet within the same workbook:
We have 8 different locations ("MICH" being one of them) that we nee
to be able to access. I can write a nested IF formula that looks a
cell I7 (that contains a list of all 8 locations) and, depending o
I7's content, brings back the desired values.
I was hoping someone in the forum could help me write a simpler formul
that would not have 7 IF statements embedded in it.
Any help w...Strange behaviour: show/hide formatting symbols reveals old change
In Word 2007, I'm getting some strange behaviour in a document that was
authored by someone else. Track Changes is switched off, all changes have
been accepted, and everything looks as it should in whichever view I happen
to choose (Print Layout, Draft, whatever). But when I click to show
formatting symbols (in whatever view) a whole lot of old changes - deletions
AND insertions, ostensibly all accepted, and from before the document got to
me - appear in the document, making it quite tricky to work with. These old
changes are impervious to anything I try to do with them
E...Color change in cell when > 49.99
I need a cell to change color if the value inside the cell reaches 50 or
higher.either text or cell shade. just so it catches the users attention.
im running excel 2000. and i have this currently in the cell that i want to
aplly this to:
have you tried conditional formating?
>I need a cell to change color if the value inside the
cell reaches 50 or
>higher.either text or cell shade. just so it catches the
>im running excel 2000. and i ...How did I break the cell display limit? #2
If there is anyone out there who can help with this, I would be most
most grateful. Here's the facts:
- I'm working in a MS Excel worksheet.
- I am aware of Excel's 255-character-per-cell display limit. (As
understand it, each cell can hold up to 32,000 characters, but wil
only display/print 255 characters.) This is a major pain in th
petunski, as most of my cells contain more than 255 characters
Therefore, most of my spreadsheet shows cells that look like this
###############. I have to look in the formula bar to see the actua
contents of each cell.
- In cell C35, I have 1,05...formatting of charts changes when copying from excel 2000 to 200.
When I copy a chart from Excel 2000 and paste it into Excel 2003, some of the
formatting is lost. In particular, scale and axis formatting. Is this a
programming issue or can it be corrected easily.
First one would answer why would you copy charts from 2000 to 2003, why not
make them in 2003?
Second and more important - how are you copying them - there are maybe 20
possible methods of copying a chart from one program to another. Please tell
us exactly which steps you use to do the copying. Also, exactly what
formatting are you loosing, what do you get instead?