if cell format
Hi, would some be kind enough to show me how to word an if then statemen for formating? Here is the question in regular people talk. if the cell A1 is bold return true in not then false thank -- Message posted from http://www.ExcelForum.com
ksnapp.14gvl1
excel
39880 articles. 
0 followers.
4 Replies
158 Views
Hi
you need VBA for this. Try the following user defined function:
Public Function is_bold(rng As Range) As Boolean
application.volatile
is_bold = rng.Font.Bold
End Function
us this like
=IS_BOLD(A1)
--
Regards
Frank Kabel
Frankfurt, Germany
> Hi,
>
> would some be kind enough to show me how to word an if then statement
> for formating? Here is the question in regular people talk.
>
> if the cell A1 is bold return true in not then false
>
> thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
frank.kabelCouple of things the OP should be aware of:
1) Even with the Application.Volatile statement, this function is never
guaranteed to be correct, since bolding or unbolding a cell doesn't
trigger a calculation.
2) This works well for 1 cell. It appears to work well for multiple
cells, in that if all are bold, it returns TRUE, if none are bold, it
returns FALSE. However, if there's a mix, rng.Font.Bold evaluates to
Null, and, since the Function is declared as Boolean, it returns the
#VALUE! error. I'd expect it to return False (i.e., implied AND rather
than implied OR)
One way around that:
Public Function Is_Bold(rng As Range) As Boolean
Dim vTemp As Variant
Application.Volatile
vTemp = rng.Font.Bold
If IsNull(vTemp) Then vTemp = False
Is_Bold = vTemp
End Function
In article <eAK8tQoHEHA.3240@TK2MSFTNGP12.phx.gbl>,
"Frank Kabel" <frank.kabel@freenet.de> wrote:
> Hi
> you need VBA for this. Try the following user defined function:
>
> Public Function is_bold(rng As Range) As Boolean
> application.volatile
> is_bold = rng.Font.Bold
> End Function
>
> us this like
> =IS_BOLD(A1)
jemcgimpseyHi JE thanks for the addition :-) -- Regards Frank Kabel Frankfurt, Germany JE McGimpsey wrote: > Couple of things the OP should be aware of: > > 1) Even with the Application.Volatile statement, this function is > never guaranteed to be correct, since bolding or unbolding a cell > doesn't trigger a calculation. > > 2) This works well for 1 cell. It appears to work well for multiple > cells, in that if all are bold, it returns TRUE, if none are bold, it > returns FALSE. However, if there's a mix, rng.Font.Bold evaluates to > Null, and, since the Function is declared as Boolean, it returns the > #VALUE! error. I'd expect it to return False (i.e., implied AND rather > than implied OR) > > One way around that: > > Public Function Is_Bold(rng As Range) As Boolean > Dim vTemp As Variant > Application.Volatile > vTemp = rng.Font.Bold > If IsNull(vTemp) Then vTemp = False > Is_Bold = vTemp > End Function > > > In article <eAK8tQoHEHA.3240@TK2MSFTNGP12.phx.gbl>, > "Frank Kabel" <frank.kabel@freenet.de> wrote: > >> Hi >> you need VBA for this. Try the following user defined function: >> >> Public Function is_bold(rng As Range) As Boolean >> application.volatile >> is_bold = rng.Font.Bold >> End Function >> >> us this like >> =IS_BOLD(A1)
Conditional Formatting can be done in VBA as Kabel showed.
A simpler approach is to use the User Interface
Select a cell to format.
Format > Conditional Formatting...
In the Condition 1 box:
You can choose "Cell Value Is" or "Formula is". To set the condition
you need to test that a value in another cell, then use "Formula Is"
If you choose "Cell Value Is".
Second box: choose a test condition like "between"
Third and Fourth boxes: enter either hard coded numbers or cell
references for the bounds of the condition.
If you choose "Formula Is", then you can set the cell format based upon
the value of a different cell. If the formula is in A8 and you want to
test C8 to see if it has an error in it, you can enter
=ISERROR($C8)
Once the condition has been defined:
click the Format... button.
there is a Font tab where you can choose BOLD and ITALIC
combinations and Font Color and underline treatment.
there is a Border Tab
There is a Patterns Tab where you can change the interior
(background) color.
All that for one condition. If you want an "ElseIF" condition, press the
Add >> button to create a Condition 2 or a Condition 3. 3 is the limit.
Conditional Formats are copied and pasted along with all other Formats.
So you can copy a Conditional Format cell and paste it many places. The
Cell References honor the absolute or relative addresses, just like any
other cell formula.
Stephen Rasey
WiserWays, LLC
Houston, TX
"ksnapp >" <<ksnapp.14gvl1@excelforum-nospam.com> wrote in message
news:ksnapp.14gvl1@excelforum-nospam.com...
> Hi,
>
> would some be kind enough to show me how to word an if then statement
> for formating? Here is the question in regular people talk.
>
> if the cell A1 is bold return true in not then false
>
> thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
rasey
Similar Artilces:
coloured cells #2
Hi, I have a user that created a spreadsheet that is highlighted by colours in different cells. On his home computer he can see the colours but at work the spreadsheet becomes pure black and white. If he prints the spreadsheet at work, it is in colour. He used to see the colours on the screen at work also. He is using Excel 2002. Any ideas? Joyce Look if he has set this Click Start, click Control Panel, and then click Accessibility Options. Click the Display tab, and then click to clear the Use High Contrast check box. Click OK to close the Accessibility Options dialog box. -- ...
tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when f.e. this phone is away from the office. This is PDA phone. On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" <fwitkowski@gmail.com> wrote: >How can I use exchange to send tasks to cell phone. I want to do when >f.e. >this phone is away from the office. This is PDA phone. If you're using a Smartphone you can sync tasks already with ActiveSync. "Mark Arnold [MVP]" <mark@mvps.org> wrote: >On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" ><fwitkowski@gmail...
sorting error on non-merged cells
Excel 2002, WinXp Home My spreadsheet has columns for Number, Date, Text, etc. When I select a column to sort, Excel expands the selection to include all cells (as it should). When I try to sort I get the error message about merged cells having to be the same size. I looked at KB291063 but that doesn't help. The cels are NOT merged so why do they have to be the same size? What size is the error refering to (# of characters, absolute value, ?)? How do I un-merge non-merged cells so I can sort? Firstly I would never rely on Excel to expand a selection to include the data I wa...
Formatting text in a cell
Is there a way to key text in one cell that is multiple lines? We would like to be able to insert new lines where we want them. Thanks for the help!! -- akkrug Use the Formula Bar: 1. click in the formula bar 2. in the formula bar, move the cursor to the desired point of insertion 3. touch ALT-ENTER 4. enter the new line. -- Gary''s Student - gsnu200855 "akkrug" wrote: > Is there a way to key text in one cell that is multiple lines? We would like > to be able to insert new lines where we want them. > > > Thanks for the help!! > -- > ak...
Finding combinations of cells that sum to near a value
Starting with a column of ~30 numbers, I'm looking for a way to have Excel find all combinations that sum to a target value +/- a specified range width. (For example, I may be looking for all combinations that sum to 1000 +/- 10.) I've found some code from Tushar Mehta that finds a list of all possible matches that sum to a number, but it doesn't allow me to put in a range to find sums to within a delta of this value. I tried modifying the code to add this in, but I couldn't get it to work right. Here is the code: http://www.tushar-mehta.com/excel/templates/match_values/index....
separating multiples digit in a cell into individual cell
I have a series of number sets. When I copied from the MS Word and pasted them into Excel, all of the numbers in the set are pasted into one cell. I need those numbers to be in an individual cell. How do I do it? I think MS Excel 2003 was able to do it. I currently have Excel 2007. Please help. Thank you very much. EggHeadCafe - Software Developer Portal of Choice A Wrapper for the Dispatcher class of Threading Namespace to manage thread items http://www.eggheadcafe.com/tutorials/aspnet/bce7889e-d2cf-42b8-a6af-2f01a383cff6/a-wrapper-for-the-dispatc.aspx Hi, You may use Data >...
Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...
cell in cell formulation possible???
Let's say I have a string of text in a cell, something like: "We may charge you $XXX fee if item is not returned in good condition." Is it possible to make that XXX a formula/calculation (based upon information entered in another cell)? ="We may charge you $" & format(A1,"#,##0.00") & " fee if item is not returned in good condition." HTH, Gary Brown "Abi" wrote: > Let's say I have a string of text in a cell, something like: > > "We may charge you $XXX fee if item is not returned in good condition." &g...
Do Not Want to count blank Cells
I copy pasted an Access dynaset (e.g., results of a crosstab query) into an Excel spreadsheet. I tried to use the function =counta(a4:z4) to count the number of entries (nonblank) in row 4. But the result is giving me 26 because it is counting blank cells. Is there a way to count the entries without counting the blanks or a way to easily reformat or change the blank cells so the formula will not count the blanks. I do not want to have to manually delete every blank cell in a table 26 columns by 2000 rows. Thank you, Steve CountA does what you want. It seems that you must have s...
How to matching substring from a Cell
Dear Sir/Madam, I am now having a column likes below Column A My Name is David Chan Marry David Williams He is David and is a boy I want to find out Column A cells with contact the word "David', then display "T" on Coloum B if found or "F" is not found. The result should be as following: Column A Column B My Name is David Chan T Marry F David Williams T He is David and is a boy T How can I do it ? I have tried if( ), but it only match the c...
moving cells
I have a roster with 30 names on a fixed schedule for 15 weeks i want to move the names through the schedule. Thanks Hi Paul, I would try an Excel web search on your Google toolbar or at http://www.google.com/advanced_search?hl=en for excel rotation OR rota schedule OR timesheets and a Google Groups search on a dropdown on your Google toolbar or at http://groups.google.com/grphp?hl=en&tab=wg&q= rotation OR rota schedule OR timesheets group:*excel* to get you started then ask specific questions if that does not provide enough information. --- HTH, D...
Attaching Comment to cell contents rather than cell
I have a spreadsheet that is regularly upadated overwriting columns, however I need to enter comments on the sheet referenced on the content. The next time I update the sheet the referenced cell will move down a row, but I need the comment to stay with the content rather than the cell. Any suggestions? Many thanks If the values that you're commenting are unique, you may want to toss the comments and create a table in another worksheet. Value Comment Then use an =vlookup() formula to return the "comment" to the adjacent cell. El Damo wrote: > > I have a spreadsh...
Money's database format
I decided to tool around with my old Money files in a binary editor (can't do much else with them). The first thing in the file's header was "MSISAM Database"--this is not news, the newsgroup FAQ states this. Well I noticed that in a similar position in an Access database I had lying around it says "Standard Jet DB", so I replaced "MSISAM Database" with that. I saved it and now Access didn't complain about unknown database type, but it did request a password--which just means they changed a tiny bit of stuff so that it wouldn't read straight int...
Pause and Input to cell
Help, Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, then the macro would place the input data in a cell, let's say d9. How do you do this in Excel. Val, InputBox. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Val Steed" <vals@msn.com> wrote in message news:uEGWBOXTEHA.1168@TK2MSFTNGP11.phx.gbl... > Help, > > Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, > then the macro would place the input data in a cell, let's say d9. > >...
Can a cell be turned on or off?
A1=B1*C1+D1 A2=B2*C2+D2 A3=B3*C3+D3 A4=B4*B4+B4 I would like to be able the have the total in any one of the A cells to add up only if the cell is click on. In other words, can a cell be turned off or on as needed? -- Joe Right click sheet tab>view code>insert this Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Column <> 1 Then Exit Sub 'B1*C1+D1 tr = Target.Row Target.Value = Cells(tr, 2) * _ (Cells(tr, 3) + Cells(tr, 4)) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@g...
How do identify a blank cell in a formula
Example: IF(s69=blank,"Void".... What I want to say is if a cell, (s69in this example) is blank, enter the word "Void" but the above way don't work - I don't know how to enter that in this IF formula. Barb, here is one way =IF(ISBLANK(S69),"Void","") -- Paul B 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 "Barb123" <Barb123@discussions.microsoft.com> wrote in message news:47...
senders address format in from field
Our company uses exchange 5.5 and outlook 2000 as clients. When our customers receive our emails, they only see the full name of the sender in the 'fom' field. We would like to see the full email address i.e. name@company.com in the 'from' field when the mail arrives at our customers. Is this possible. Does it depend on the clients mailsoftware? Thanks in advance Marcel On Thu, 25 Aug 2005 22:25:53 +0200, "Marcel" <gaper01@(nospam)worldmail.nl> wrote: >Our company uses exchange 5.5 and outlook 2000 as clients. >When our customers receive our email...
How do I use Conditinal Format for another cell?
Assuming Cell A1 is the value "Red" or "Green" or "Yellow" How could I have a formula on Cell "C1" containing Conditional formatin the same as below if A1 is "Red" then Bacground of A1 turns to red if A1 is "Blue" then Bacground of A1 turns to Blue if A1 is "Yellow" then Bacground of A1 turns to Yellow I assume that your statement if A1 is "Red" then Bacground of A1 turns to red should be if A1 is "Red" then Bacground of C1 turns to red ??? Assuming that to be true Select C...
Copying into Filtered cells
Hello - I am trying to create a spreadsheet that will record a Year group's data. Each student is attached to a Maths class. I therefore need to be able to filter for a specific maths set and then copy and paste that groups test resutls into the visible cells. However, excel just copies the results into the first 30 cells in the spreadsheet (some of which are not visible due to the filter) - does anyone know how I can get excel to only paste into the filtered cells? this is an example of my spreadsheet - the filter is on maths set. SURNAME CFIRST Maths Set Teacher Paper Level NC ...
increase cell character limit
Hi all, Simple question that is driving me crazy. I'm making a table containing text and numbers. One of the cells in this table has 1459 characters. For some reason, the last couple sentences won't fill the bottom of the cell; instead, they're cut off. I can only see them if I increase the column width, but I don't want to do that since it'll complicate other things (readability, etc). So, how do I get all the text/characters to display in this cell? Thanks -- Weissme ------------------------------------------------------------------------ Weissme's Profile:...
MS Excel VBA DAO SQL where clause cell
Hi I'm trying to get a table of data from MS Access to MS Excel via VBA DAO programming. In my worksheet call 'Data' in cell D14 there is my string value call "toys & Chairs" I can get the DATA into MS Excel without the Where clause in my SQL VBA script. The problem is when I use the WHERE clause which sources its value from cell D14... In my VBA code. Firstly, cell D14 is defined in VBA: Dim r1 As String r1 = Range("D14") Secondly, MY SQL is: SQL = "LVL_1, LVL_2, LVL_3" SQL = SQL & " FROM table_ABC" SQL = SQL &...
How do I get the total of a range of cells that are in another she
I have two sheets in the workbook and I need to get the total of a range in sheet1 into a table on sheet2. How do I go about this? something like =sum(sheet2!$a$1:$a$5) note the ! after the sheet name bobf -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28504 View this thread: http://www.excelforum.com/showthread.php?threadid=487937 ...
Need to add cell comments in unlocked cell on protected worksheet
Using Excel 2002, I have a worksheet that I have "protected" so that only a certain block of cells can be selected/edited. Today, I have a new requirement to be able to add/edit/delete cell comments on those cells that remain unlocked. But the options to do so on the cell context menu disappear when the worksheet is protected, but reappear when the worksheet is unprotected. When you protect the worksheet, scroll down and check "edit objects". This will allow comments to be added/removed/changed--along with other objects (pictures/shapes/etc). dan400man wrote: >...
Swap selected cell values
I am looking for a simple macro to swap the contents of two selected cells. The cells might not be next to each other. I know what I want it to do, but I don't know vba well enough. If anyone has a moment to help, here is what I want to do. Select cell1, could be A1 Control-Click additional selection of cell2, could be C1 Hot-Key Macro to: Assign cell1 value to variable1 Assign cell2 value to variable2 Assign variable1 value to cell2 Assign variable2 value to cell1 Note: the specific cells being selected will not always be the same. So after the macro is run the values that started ...
Add "Significant Figures" to Number tab in "Format Cells" of Excel
Using Excel for science classes (the only classes I ever use Excel for) is a pain because there isn't an easy way to deal with significant figures. Sometimes setting the number of decimal places helps. Plus, it would be nice if excel offered a method for dealing with the number "53,000,000" in which it had 5 sig. figs. Scientific notation would be useful in this circumstance, but usually I have to deal with, for example, "10" such that it has 2 significant figures. It really is a pain going through all the data fixing it to deal with such simple matters. I am...
Hi, I have a user that created a spreadsheet that is highlighted by colours in different cells. On his home computer he can see the colours but at work the spreadsheet becomes pure black and white. If he prints the spreadsheet at work, it is in colour. He used to see the colours on the screen at work also. He is using Excel 2002. Any ideas? Joyce Look if he has set this Click Start, click Control Panel, and then click Accessibility Options. Click the Display tab, and then click to clear the Use High Contrast check box. Click OK to close the Accessibility Options dialog box. -- ...
tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when f.e. this phone is away from the office. This is PDA phone. On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" <fwitkowski@gmail.com> wrote: >How can I use exchange to send tasks to cell phone. I want to do when >f.e. >this phone is away from the office. This is PDA phone. If you're using a Smartphone you can sync tasks already with ActiveSync. "Mark Arnold [MVP]" <mark@mvps.org> wrote: >On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" ><fwitkowski@gmail...
sorting error on non-merged cells
Excel 2002, WinXp Home My spreadsheet has columns for Number, Date, Text, etc. When I select a column to sort, Excel expands the selection to include all cells (as it should). When I try to sort I get the error message about merged cells having to be the same size. I looked at KB291063 but that doesn't help. The cels are NOT merged so why do they have to be the same size? What size is the error refering to (# of characters, absolute value, ?)? How do I un-merge non-merged cells so I can sort? Firstly I would never rely on Excel to expand a selection to include the data I wa...
Formatting text in a cell
Is there a way to key text in one cell that is multiple lines? We would like to be able to insert new lines where we want them. Thanks for the help!! -- akkrug Use the Formula Bar: 1. click in the formula bar 2. in the formula bar, move the cursor to the desired point of insertion 3. touch ALT-ENTER 4. enter the new line. -- Gary''s Student - gsnu200855 "akkrug" wrote: > Is there a way to key text in one cell that is multiple lines? We would like > to be able to insert new lines where we want them. > > > Thanks for the help!! > -- > ak...
Finding combinations of cells that sum to near a value
Starting with a column of ~30 numbers, I'm looking for a way to have Excel find all combinations that sum to a target value +/- a specified range width. (For example, I may be looking for all combinations that sum to 1000 +/- 10.) I've found some code from Tushar Mehta that finds a list of all possible matches that sum to a number, but it doesn't allow me to put in a range to find sums to within a delta of this value. I tried modifying the code to add this in, but I couldn't get it to work right. Here is the code: http://www.tushar-mehta.com/excel/templates/match_values/index....
separating multiples digit in a cell into individual cell
I have a series of number sets. When I copied from the MS Word and pasted them into Excel, all of the numbers in the set are pasted into one cell. I need those numbers to be in an individual cell. How do I do it? I think MS Excel 2003 was able to do it. I currently have Excel 2007. Please help. Thank you very much. EggHeadCafe - Software Developer Portal of Choice A Wrapper for the Dispatcher class of Threading Namespace to manage thread items http://www.eggheadcafe.com/tutorials/aspnet/bce7889e-d2cf-42b8-a6af-2f01a383cff6/a-wrapper-for-the-dispatc.aspx Hi, You may use Data >...
Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...
cell in cell formulation possible???
Let's say I have a string of text in a cell, something like: "We may charge you $XXX fee if item is not returned in good condition." Is it possible to make that XXX a formula/calculation (based upon information entered in another cell)? ="We may charge you $" & format(A1,"#,##0.00") & " fee if item is not returned in good condition." HTH, Gary Brown "Abi" wrote: > Let's say I have a string of text in a cell, something like: > > "We may charge you $XXX fee if item is not returned in good condition." &g...
Do Not Want to count blank Cells
I copy pasted an Access dynaset (e.g., results of a crosstab query) into an Excel spreadsheet. I tried to use the function =counta(a4:z4) to count the number of entries (nonblank) in row 4. But the result is giving me 26 because it is counting blank cells. Is there a way to count the entries without counting the blanks or a way to easily reformat or change the blank cells so the formula will not count the blanks. I do not want to have to manually delete every blank cell in a table 26 columns by 2000 rows. Thank you, Steve CountA does what you want. It seems that you must have s...
How to matching substring from a Cell
Dear Sir/Madam, I am now having a column likes below Column A My Name is David Chan Marry David Williams He is David and is a boy I want to find out Column A cells with contact the word "David', then display "T" on Coloum B if found or "F" is not found. The result should be as following: Column A Column B My Name is David Chan T Marry F David Williams T He is David and is a boy T How can I do it ? I have tried if( ), but it only match the c...
moving cells
I have a roster with 30 names on a fixed schedule for 15 weeks i want to move the names through the schedule. Thanks Hi Paul, I would try an Excel web search on your Google toolbar or at http://www.google.com/advanced_search?hl=en for excel rotation OR rota schedule OR timesheets and a Google Groups search on a dropdown on your Google toolbar or at http://groups.google.com/grphp?hl=en&tab=wg&q= rotation OR rota schedule OR timesheets group:*excel* to get you started then ask specific questions if that does not provide enough information. --- HTH, D...
Attaching Comment to cell contents rather than cell
I have a spreadsheet that is regularly upadated overwriting columns, however I need to enter comments on the sheet referenced on the content. The next time I update the sheet the referenced cell will move down a row, but I need the comment to stay with the content rather than the cell. Any suggestions? Many thanks If the values that you're commenting are unique, you may want to toss the comments and create a table in another worksheet. Value Comment Then use an =vlookup() formula to return the "comment" to the adjacent cell. El Damo wrote: > > I have a spreadsh...
Money's database format
I decided to tool around with my old Money files in a binary editor (can't do much else with them). The first thing in the file's header was "MSISAM Database"--this is not news, the newsgroup FAQ states this. Well I noticed that in a similar position in an Access database I had lying around it says "Standard Jet DB", so I replaced "MSISAM Database" with that. I saved it and now Access didn't complain about unknown database type, but it did request a password--which just means they changed a tiny bit of stuff so that it wouldn't read straight int...
Pause and Input to cell
Help, Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, then the macro would place the input data in a cell, let's say d9. How do you do this in Excel. Val, InputBox. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Val Steed" <vals@msn.com> wrote in message news:uEGWBOXTEHA.1168@TK2MSFTNGP11.phx.gbl... > Help, > > Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, > then the macro would place the input data in a cell, let's say d9. > >...
Can a cell be turned on or off?
A1=B1*C1+D1 A2=B2*C2+D2 A3=B3*C3+D3 A4=B4*B4+B4 I would like to be able the have the total in any one of the A cells to add up only if the cell is click on. In other words, can a cell be turned off or on as needed? -- Joe Right click sheet tab>view code>insert this Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Column <> 1 Then Exit Sub 'B1*C1+D1 tr = Target.Row Target.Value = Cells(tr, 2) * _ (Cells(tr, 3) + Cells(tr, 4)) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@g...
How do identify a blank cell in a formula
Example: IF(s69=blank,"Void".... What I want to say is if a cell, (s69in this example) is blank, enter the word "Void" but the above way don't work - I don't know how to enter that in this IF formula. Barb, here is one way =IF(ISBLANK(S69),"Void","") -- Paul B 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 "Barb123" <Barb123@discussions.microsoft.com> wrote in message news:47...
senders address format in from field
Our company uses exchange 5.5 and outlook 2000 as clients. When our customers receive our emails, they only see the full name of the sender in the 'fom' field. We would like to see the full email address i.e. name@company.com in the 'from' field when the mail arrives at our customers. Is this possible. Does it depend on the clients mailsoftware? Thanks in advance Marcel On Thu, 25 Aug 2005 22:25:53 +0200, "Marcel" <gaper01@(nospam)worldmail.nl> wrote: >Our company uses exchange 5.5 and outlook 2000 as clients. >When our customers receive our email...
How do I use Conditinal Format for another cell?
Assuming Cell A1 is the value "Red" or "Green" or "Yellow" How could I have a formula on Cell "C1" containing Conditional formatin the same as below if A1 is "Red" then Bacground of A1 turns to red if A1 is "Blue" then Bacground of A1 turns to Blue if A1 is "Yellow" then Bacground of A1 turns to Yellow I assume that your statement if A1 is "Red" then Bacground of A1 turns to red should be if A1 is "Red" then Bacground of C1 turns to red ??? Assuming that to be true Select C...
Copying into Filtered cells
Hello - I am trying to create a spreadsheet that will record a Year group's data. Each student is attached to a Maths class. I therefore need to be able to filter for a specific maths set and then copy and paste that groups test resutls into the visible cells. However, excel just copies the results into the first 30 cells in the spreadsheet (some of which are not visible due to the filter) - does anyone know how I can get excel to only paste into the filtered cells? this is an example of my spreadsheet - the filter is on maths set. SURNAME CFIRST Maths Set Teacher Paper Level NC ...
increase cell character limit
Hi all, Simple question that is driving me crazy. I'm making a table containing text and numbers. One of the cells in this table has 1459 characters. For some reason, the last couple sentences won't fill the bottom of the cell; instead, they're cut off. I can only see them if I increase the column width, but I don't want to do that since it'll complicate other things (readability, etc). So, how do I get all the text/characters to display in this cell? Thanks -- Weissme ------------------------------------------------------------------------ Weissme's Profile:...
MS Excel VBA DAO SQL where clause cell
Hi I'm trying to get a table of data from MS Access to MS Excel via VBA DAO programming. In my worksheet call 'Data' in cell D14 there is my string value call "toys & Chairs" I can get the DATA into MS Excel without the Where clause in my SQL VBA script. The problem is when I use the WHERE clause which sources its value from cell D14... In my VBA code. Firstly, cell D14 is defined in VBA: Dim r1 As String r1 = Range("D14") Secondly, MY SQL is: SQL = "LVL_1, LVL_2, LVL_3" SQL = SQL & " FROM table_ABC" SQL = SQL &...
How do I get the total of a range of cells that are in another she
I have two sheets in the workbook and I need to get the total of a range in sheet1 into a table on sheet2. How do I go about this? something like =sum(sheet2!$a$1:$a$5) note the ! after the sheet name bobf -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28504 View this thread: http://www.excelforum.com/showthread.php?threadid=487937 ...
Need to add cell comments in unlocked cell on protected worksheet
Using Excel 2002, I have a worksheet that I have "protected" so that only a certain block of cells can be selected/edited. Today, I have a new requirement to be able to add/edit/delete cell comments on those cells that remain unlocked. But the options to do so on the cell context menu disappear when the worksheet is protected, but reappear when the worksheet is unprotected. When you protect the worksheet, scroll down and check "edit objects". This will allow comments to be added/removed/changed--along with other objects (pictures/shapes/etc). dan400man wrote: >...
Swap selected cell values
I am looking for a simple macro to swap the contents of two selected cells. The cells might not be next to each other. I know what I want it to do, but I don't know vba well enough. If anyone has a moment to help, here is what I want to do. Select cell1, could be A1 Control-Click additional selection of cell2, could be C1 Hot-Key Macro to: Assign cell1 value to variable1 Assign cell2 value to variable2 Assign variable1 value to cell2 Assign variable2 value to cell1 Note: the specific cells being selected will not always be the same. So after the macro is run the values that started ...
Add "Significant Figures" to Number tab in "Format Cells" of Excel
Using Excel for science classes (the only classes I ever use Excel for) is a pain because there isn't an easy way to deal with significant figures. Sometimes setting the number of decimal places helps. Plus, it would be nice if excel offered a method for dealing with the number "53,000,000" in which it had 5 sig. figs. Scientific notation would be useful in this circumstance, but usually I have to deal with, for example, "10" such that it has 2 significant figures. It really is a pain going through all the data fixing it to deal with such simple matters. I am...