Manual or Formula entry in cell.
I have a column in a spreadsheet called weekly costs. Not the majority of
the weekly costs will be manual entries. However 25% need to be picked up
from another spreadsheet. I could add an addtional colum say A and the have
in a formula in Column B that picks up from col A or does a vlookup. But I
feel that having 2 columns may cause confusion.
What I am ideally looking for is to go into a cell and have a dropdown list
that says manual or formula, clicking manual allows a manual entry, whereas
clicking formula uses the value from the vlookup formula.
Any way that this can b...Creating Format Conditions via Code
I'm able to successfully create format conditions via code, while I'm able to
manipulate the format of the control (.enabled, .forecolor, .backcolor), is
it possible to control wether or not the field is visible or locked.
For i = 0 To Me.subfrmLoadListDetail.Controls.Count - 1
Set ctl = Me.subfrmLoadListDetail.Controls(i)
If .Tag Like "*" & "loadListDetail" & "*" Then
For j = 0 To .FormatConditions.Count - 1
Debug.Print j, .Name, .FormatConditions.Count,
..FormatCondi...Entering data to non protected cells
If I protect the table I can still "stop" at any cell and get the message if
trying to enter data to protected area. My Q here is. can you avoid this
being able to click on any cell and instead just jump to a non protected
cell and to the next and next?
Use the "Tab" key to jump directly to unlocked cells. Your worksheet will
have to be in protected mode for this to work.
> If I protect the table I can still "stop" at any cell and get the message if
> trying to enter data to protected area. My ...find all cells that match and use in an index/vlookup
I have a LARGE set of data...
I parse it to a pivot.
Chair living room .1 .2
dining room .6 .3
bedroom .3 .4
sofa living room .9 .2
dining room .01 0
bedroom .2 .5
(4 colums, multiple rows)
I need to be able to:
Find what room a soffa is most likely to be in, and what that chance is, and
use that data in another worksheet where there will just be a list of
furniture, in room, with max chance.
I can seem to figure how to find the range of rows that have chairs (there
are not always the same amou...Possible Conditional Formatting Workaroud?
I have conditional formatting already set to shade a row differently for 3
conditions. But I also need to have a date cell's value change to red if
it's manually changed.
I'm wondering if there's a way to use Excel's custom number format feature
to do this?
Here's the layout:
G - Submission Date (manually entered)
H - Hidden Workdays field to determine a 3 day Due Date date based on G
I - Displayed Due Date field that equals value of H
J - Hidden # Days Turnaround Field in General format (currently equals I -
G but can change that to H-G if I need to do that t..."Blank" cells
I've got a table in Excel where I want to do this: suppose we're in
line E; I want to fill all the cells, from E2 to E10, for example, with
blank values if E11 < 0; all the referred cells - E2 to E10 - contain
Can I do this?
Kepler's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29328
View this thread: http://www.excelforum.com/showthread.php?threadid=490443
Not being able to see the existing formulas in E2:E10 kinda ...CUSTOM TEXT FORMATTING???
My cell has 8 mixed characters of numbers and letters. I
need to have a hyphen "-" after the first 4 characters in
a column. Can I make a custom text format so no matter
what I enter a hyphen is automatically inserted? How do I
this could only be done with VBA, using an event procedure (as you have
text and numbers in your cell). e.g. put the following code in your
worksheet module (not in a standard module):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100"...Arrow keys not moving 1 cell at a time
User has a spreadsheet where the arrow keys are doing a 'page down
rather than moving 1 cell at a time. Not sure how this got set, or ho
to get it back. Thanks for your help
Message posted from http://www.ExcelForum.com
slivi, check to see if scroll lock is on, on the key board
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 97 & 2000
** remove news from my email address to reply by email **
"slivi >" <<slivi.11vnhp@e...Highlighting selected cells/rows/columns
Try adjusting the contrast on your computer monitor. Mine are little
buttons on the lower right just below the screen. My contrast was
turned up on high, when it was lowered, I could see the gray shading
on selected rows/columns/cells.
...Format cells #5
I have Excel 2000, Win ME. Recently the pulldown command to format cells
does not always respond - ie, no box appears, so I can't apply borders or
control number format. The shortcut approach doesn't work either. I've
tried Help..Detect and Repair but the fault remains. Any suggestions
If I want to conditionally format a whole row, depending on the contents
of just one cell in that row, can this be done?
So, let's say I have the range A1:M100
I want each individual row (A to M) to be conditionally formatted if
the value in the M cell of that row is <0.95, or >1.05 (2 different
conditions & formats)
Is this possible? Ideally without VBA, as I'm wanting to pass the
technique onto staff whose Excel knowledge, is, shall we say, limited.
Amy advice greatly appreciated.
Select all of the rows 1:100, goto CF, change Condition 1 To Formula Is, and
a...Using wide and regular format monitors
My job just gave me new Lenovo T400 laptop running XP. (No Windows 7 yet
It has a 14 inch wide format screen. I'm attaching the computer to a
traditional 20 inch screen. The wide display on the laptop 1280 x 800 is
squished and off the screen on the traditional montior. When I open display
properties, it shows both montiors as Monitor 1.
I'd like to have the traditional monitor work property without having to
resize the display resolution every time I connect to it. (I do NOT want to
use one monitor as an extension of my desktop). Is there a way ...How do I print formula figures and calculated amount in a cell?
In order to pass a file monitoring on income eligibility, I need to be able
to show the calculations in a paper copy of a spreadsheet, not just the final
If someone makes 3000 a month, the calculation would be =3000*12. It is
calculated using a cell calculation formula (ie. =G4*12
I need it to print 3000*12=36000. Does anyone have any ideas on how/if this
Does that help?
> In order to pass a file monitoring on income eligibility, I...Last Cell of Worksheet
The shortcut CTRL+END is very handy for moving to the last
cell on the worksheet, in the bottom-most used row of the
rightmost used column. This shortcut works until I delete
rows at the end of the worksheet. After I delete a few
rows, the CTRL+END shortcut takes me to the last cell that
used to hold data, not the last cell that contains data.
Is there a shortcut that moves to the last cell on the
worksheet that contains data? If not, is there a way to
change what Excel thinks is the last cell so that the
CTRL+END shortcut takes me to the last cell that contains
Thanks in adva...Ecel Cell Format Oddity
I have a problem with excel that may be a result of my obliviousness,
or may be an actual problem. In short, when I enter a date, microsoft
auto formats it as a formula, adding the equal sign in front as so
=02/09/04. It does this even though the cell is formatted as a Date
cell. This happened after everything was working well for the first
100 rows or so. I tried changing the formating back and forth, but the
problem remains, and in fact if I go back to a cell which worked
correctly in the past, it now also has this problem. Previous sheets
still work fine however. I did figure out t...AMVERAGIF Condition
I am having trouble adding an AND statement to the condition portion of the
AVERAGEIF statement. Is there a trick to doing this?
"And" will return true/false, try using IF(AND(-------)) which will return a
value as the criteria.
"rwtrader" <firstname.lastname@example.org> wrote in message
>I am having trouble adding an AND statement to the condition portion of the
> AVERAGEIF statement. Is there a trick to doing this?
Don't know the new function AVERAGEIF, ...Complicated conditional formatting problem [Excel 2007]
I have a cell with the following formula:
It displays for example:
Now, I`d like to color the cell red if any of the following is true:
a) First value(here: 145) is not between 90 and 140
b) Second value (here:80) is not between 50 and 90
Any ideas how to do this?
In the CF dialogue box you need to select Formula Is and then use this
RIGHT(A1,LEN(A1)-FIND("/",A1))<50,...Task Duration Changes on SAVE & OPEN in XML format
We have discovered a strange problem in MSProject after installing SP2
The task duration changes when MPP file is saved as XML and XML file is
reopened. This is causing a havoc.
There is nothing specific about the file. File with even one task shows the
Any help possible. Can I upload the file somehwere?
I'm running Project 2007 SP2. I created 7 tasks with durations from 1d to 7d
and linked them. Saved to xml. Closed. Create new project from xml file and
finish date of 7th task was same and all durations same.
What's the minimum you have to...How do I display images from an XML doc that are in this format>>
The xml doc has pictures but in the format of a very long character string.
I'm not sure what this format is called so I do not know what type of
converter I should be looking for. May have something to do with base64. I
want to read it from the xml doc and display it in a windows form pictureBox.
I'm programming in c#.
example(it actually has a lot more characters than this)
<Picture>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQAB2gAAAAAAAAAAACgKQAAQk2YKQAAAAAAAFYAAAAoAAAAgAAAABAAQAAAAAAAAAAACICwAAiAsAAAgAAAAIAAAAYKQAAAAAAAFYAAAAoAAAAgAAAABAAQAAA</Pi...Exact Copy of Linked Cells in Excel
I have a Main information Sheet that links to corresponding othe
sheets. When I type into the main cell, it will link the letter that
type, but bold portions and fill color does not link. How can I hav
an exact copy of my main cell? (I used Paste Special-Paste Link t
link the cell)
I attached an example of what it is doing. "Main Sheet" is what I wan
my linked sheet to look like. "Linked Sheet" depicts what actuall
happens with the link.
Message posted from http://www.ExcelForum.com
formulas (that is your linked cell) can only return values. Ther...XML Serializer
I have a class I am serializing, and need the resultant XML to skip/omit
classes that are not initialized, or their member variables have not been
set. Is this possible?
Say for the following code, sometimes the Person may not have given us one,
both, or either of their addresses.
The serialize function will still put in a "<BillingAddress />" tag
indicating no data. I want it to completely omit the tag if the class is
empty, similar to the way it omits any tag for blank STRING members, such as
if there is no SSN given.
VB.NET SAMPLE CODE:
Public C...Safest image format to use with Word?
Operating System: Mac OS X 10.6 (Snow Leopard)
Well... IS there a safe image file format to use with Word? (jpeg vs tiff vs png vs...) <br><br>Until now, all of my Word documents have been lean, clean (styled) text only. But recently I've realized that it would be helpful to include images in my files. <br><br>Am I asking for trouble? Am I asking for less trouble if I... <br><br>- Insert them all as in-line graphics? <br>
- "Size" the images in another application before inserting them so that ...conditional formatting 02-19-10
Windows XP fully updated
Excel 2002 sp3
is there a way to make a cell's background colour change depending upon
the contents for ANOTHER cell.
if cell A1 contains the word 'done' I would like the background colour
of B1 to be yellow.
Conditional formatting only seems to work on the cell in question not on
This question appears as unanswered in my OE, so excuse me if this is an
extra, unneeded reply.
Select B1, then use CFR with the Custom option, with a formula that returns
TRUE or FALSE
IF A1 will j...Interactive XY charts where data are in different number formats
I posted this question earlier but, in spite of Tushar
Mehta's valiant efforts, I was unable to resolve my
problem. My data are organized differently than in the
examples cited on his web page and my knowledge of the
OFFSET and COUNTA functions simply proved to be too
So, if I may burden you again:
I have an array of data organized as follows:
Response 1 2 3 4 5
Data in cells A4:F19:
Probability 80% 70% 65% 75% 85%
Number of Customers 115 185 100 160 190
Selling price $2.15 $1.85 $2.00 $2.30 $2....referencing cells in relation to named Cells
Not sure if this is possible but have named cells a1:a8 named as test01
what i want to do is in formulas be able to use =countif(test01+ 4
eg want to be able to use say e1:e8 in a formula but not have to use
references each time
have got a lot of formulas that use this range ?1:?8 and want to be
able to simplify the way the formulas work
can anyone help with this
PS using Excel 2003
Not sure if Iam going about this the right way but any solution would
You can try something like this: