if.... Anyway around this?

I'm new to this. I need to drop a value into a cell where there is nothing. 
The 'target' cell should not have a formula because some days I would need 
to place a one time value there , doing so would wipe out the formula.

Example:

A1                    B1                    C1
BOB                JOE                   =IF(A1="bob",A2=B1)

A2
(BLANK)


I need the blank cell (A2) to take the value of B1 if C1 is true.
Maybe I have the syntax wrong, maybe it's not possible. Let me know.

Thanks



0
sycewm (3)
4/12/2006 3:21:18 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
364 Views

Similar Articles

[PageSpeed] 54

In C1, enter
=A1="bob"

In A2, enter
=IF(C1, B1, "")

or leave C1 empty and enter in A2
=IF(A1="bob", B1, "")

You cannot write a function in cell C1 that will place a value in A2.  
Functions return values, they don't manipulate other objects (at least not 
when called from a worksheet).


"sycewm@sbcglobal.net" wrote:

> I'm new to this. I need to drop a value into a cell where there is nothing. 
> The 'target' cell should not have a formula because some days I would need 
> to place a one time value there , doing so would wipe out the formula.
> 
> Example:
> 
> A1                    B1                    C1
> BOB                JOE                   =IF(A1="bob",A2=B1)
> 
> A2
> (BLANK)
> 
> 
> I need the blank cell (A2) to take the value of B1 if C1 is true.
> Maybe I have the syntax wrong, maybe it's not possible. Let me know.
> 
> Thanks
> 
> 
> 
> 
0
jmb (270)
4/12/2006 4:10:01 AM
Hi

This may get you started:

Right-click on the sheet tab and select View Code.

Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target <> Range("A1") Then Exit Sub
    If Range("A1").Value = "bob" Then
        Range("A2").Value = Range("B1").Value
    End If
End Sub

The code only runs if cell A1 has been changed and only checks to see
if "bob" has been entered in that cell, if it has then if copies the
data in B1 to A2.

Regards

Steve

0
britwiz (47)
4/12/2006 10:46:18 AM
Reply:

Similar Artilces:

Is there anyway to simplify the Deployment process?
Hello, I am looking for any best practices that will aid in deplyment of my application. My application has one SQL 2008 database. When I make changes and update/upgrade the schema, I need to track the changes and prepare a script to deliver to each of my Clients. They run the script and everything is wonderful again. However, the issue I have been facing is that different Users are at different levels of patches applied. So, for instance, User 1 may be running a version from last December, while User 2 is running a version from last Tuesday. When patches to the schema/stored pr...

Anyway to transfer Money to a new computer?
I purchased and downloaded Money 2007 Home and Business some time ago. I now have a new laptop, and have learned that I can no longer download the product (time has passed). My old system is still operational -- is there any way for me to transfer the application? Does Microsoft expect us to re-purchase with each new system? (lame) "kjcb" <kjcb@discussions.microsoft.com> wrote in message news:61FB8109-5199-467D-A7DB-A971FCB5F671@microsoft.com... >I purchased and downloaded Money 2007 Home and Business some time ago. I >now > have a new laptop, and have learned...

getting text box to wrap around graphic
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) How do I get text in text box (in a newsletter) to wrap around a graphic that is behind the text box, not in the text box? You can't. Text Boxes are objects themselves & you can't have one object wrap around another. Only document text can flow around objects. What you're attempting to do [if I'm envisioning it correctly] is a fairly sophisticated page layout which is better done in desktop publishing programs. Word simply is not equipped to effectively do this sort of work... At least not without a high ...

who needs money anyway?
http://www.richgetquick.com ...

if.... Anyway around this?
I'm new to this. I need to drop a value into a cell where there is nothing. The 'target' cell should not have a formula because some days I would need to place a one time value there , doing so would wipe out the formula. Example: A1 B1 C1 BOB JOE =IF(A1="bob",A2=B1) A2 (BLANK) I need the blank cell (A2) to take the value of B1 if C1 is true. Maybe I have the syntax wrong, maybe it's not possible. Let me know. Thanks In C1, enter =A1="bob" In A2, enter =IF(C1, B1, ""...

is there anyway to create a clone of an excel sheet in vba?
is there anyway to create a clone of an excel sheet in vba? Hi Daniel, To add a copy in the same workbook, try: Sub Tester With ActiveWorkbook .Sheets("MySheet").Copy After:=.Sheets(Sheets.Count) End With End Sub To create a new single-sheet workbook containing a copy of the sheet, try: Sub Tester2 ActiveWorkbook.Sheets("MySheet").copy End sub --- Regards, Norman "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:uIBBqHegFHA.2156@TK2MSFTNGP14.phx.gbl... > is there anyway to create a clone of an excel sheet in vba? > > ...

What causes Word to display/remove lines around table cells
At one point Word was displaying a faint line around all table cells -- even when the cells had no borders. These lines disappeared in print/print preview. For some reason, now Word is not displaying these lines. How do I turn them on or off? On Mon, 28 Dec 2009 11:59:29 -0800 (PST), "faceman28208@yahoo.com" <faceman28208@yahoo.com> wrote: >At one point Word was displaying a faint line around all table cells >-- even when the cells had no borders. These lines disappeared in >print/print preview. > >For some reason, now Word is not displaying the...

Anyway to search for a folder name?
I have about a 200 folders, I used to have them in trays but the trays are gone, so I want to find a folder that has the word "training" in it. Is there a way to search for a folder name in outlook 2003? There should be an Advanced Find option in 2003 (I don't use 2003 yet). In earlier versions of Outlook there is a find button on either the standard or advanced toolbar. -- K. Orland Thoughts lead on to purposes; purposes go forth in action; actions form habits; habits decide character; and character fixes our destiny "Kim Webb" wrote: > I have about a 200...

Active Directory Backup Setup
my question is this. what good is a backup domain controller anyway. it does not appear to be a real backup. I setup a DC for my domain and then setup an additional DC on the network. when I pull the network cable out of the first DC, nothing is accessible (users, groups, etc) on the second domain controller. the information is only there if the first DC is up and running. Did I setup the second dc incorrectly? I thought the second dc is to be used in case the primary fails. thanks for your insight and perhaps what I'm doing wrong. -- jjj0923 ---------------...

Text wrap around in a cell
When there is the potential for a lot of text to be entered in a cell Is there a way that the cell can be formatted to move on its own whe the text has been fully entered, to display all text without having t open the cell. Even when wrap around has been selected -- Message posted from http://www.ExcelForum.com GO TO FORMAT, CLICK ON CELLS, GO TO ALIGNMENT AND CLICK THE BOX THAT SAYS WRAP. THIS SHOULD WORK. REMEMBER THAT THE WRAP WILL BE THE SIZE OF THE COLUMN ----- aly1cat > wrote: ---- When there is the potential for a lot of text to be entered in a cell I...

Error msg when opening Excel (but works anyway)
I have macros in my file. When I open it I get a msg: Cannot find 'C:\Program files\Microsoft Office\Office 11\Library\Msquery\Xlquery.xla'!Register.DClick, which has been assigned to run each time .....file is open My file works anyway. I have a refresh to ACCESS (query). This is annoying....how can I get rid of the msg. thx Hi Greg, did you resolve your DClick problem at all? I ask because I am getting the same error when I publish a document to Sharepoint Team Services - doesn't happen under any other circumstances: different user access right levels, running local...

Anyway to COMPRESS RESOURCES???
Is there any way to compress resources? My res directory is 1.7MB and 1.4MB of that is from 4 files. 1 24bit BMP, 1 32bit BMP and 2 TTF files. I need to keep all 4 of these files embedded in my DLL. I'd like to avoid "heavy baggage" as that would defeat the purpose. It looks like storing the 2 BMP files as PNG would knock off about 500k to 600k. But it seems like I'd have to use GDI+ (CImage class) to display them. I'd like to avoid that requirement as a larger DLL is better then that. Likewise I'd like to avoid bloated 3rd party libraries that'd bloat my ...

Getting around limitations with MIN function
I have a range of calculated values and want to display the smallest value in an adjacent cell. However, some of the cells (correctly) return #DIV/0! errors and, as the help file explains, the MIN function subsequently returns this same error. Can anyone think of a way of getting around this problem? I imagine there must be a way to only count valid numbers or something similar. Thanks in advance! Paul It isn't correct to return errors! You can avoid the #DIV/0 error (which comes about by trying to divide by zero) by adjusting your formulae like this: =IF(divisor=0,"error",y...

Anyway to nest more than 7 "IF"s?
This is the forumla I'm trying to write, but it won't work. I suspect it has to do with trying to nest more than 7 "IF"s. Is there any way to nest more, or to write it in a different way so I don't have to nest more than 7? This is what I wanna write: =IF(INDIRECT("F"&"1021")="Yes, DT",E516*0.8,IF(INDIRECT("F"&"1021")="No, DT",E516*1.25,IF(INDIRECT("F"&"1021")="Yes, DD",E516*0.8,IF(INDIRECT("F"&"1021")="No, DD",E516*1.5,IF(INDIRECT(&q...

my cursor jumps around in my Office Outlook email program..why?
Can anyone help? My cursor is constantly jumping around the page while I am writing emails... (twice just while I was writing this...its making me crazy!) Using a laptop with a touchpad? codycuts wrote: > Can anyone help? My cursor is constantly jumping around the page while I am > writing emails... > (twice just while I was writing this...its making me crazy!) Does it jump in other programs? Do you have a microphone connected and turned on? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center...

Anyway to Combine Customer Deposits
Using Money 2006 Small Business, how can I combine customer deposits? ie, in my company receivables section there’s an option to “Receive payment for Invoice” but you can only group together payments for the same customer. If I receive payment for $500 from one customer and $400 from another how can my total deposit equal $900? I'm not speaking from experience (I haven't tried this yet) but I have been researching this a bit, and think I've figured out how to get around some of MMSB shortcomings. First off (as discussed in the Money 2005 thread) when you receive a payme...

why does data appear to be wrapped around in a .txt file?
data was entered in Excel and saved as a prn file and then changes were made and file was saved. Using Windows explorer, I renamed the file as .txt. When the file is opened by my customer they see that the file looks like it is wrapped around to the next line. They can't use this and I can't fix it. Any ideas? Thanks What are they opening it with? If a word processor, the rows of data are simply too wide to fit within the margins. They may be able to accommodate the width by changing Page Layout to Landscape. OTOH, they can open the file in a Text Editor (like Notepad) and not ha...

Export-CSV
Why does Export-CSV put quotes around everything? Sure, this is a valid CSV, technically, but it feels like a clunky implmentation. Only values with delimiters within them should have to be quoted. Is there a way to turn this off and make it more RFC4180 compliant? Thanks. -- Kevin Buchan kevin.buchan@troutman[nospam]sanders.com Because its easier to quote everything rather than not. You can have whitespace, embedded quotes, etc. C/C++ has taken this same approach when it comes to several concepts one would be enums and the trailing ',' which is ignored bec...

Is there anyway to change exact cell height.
I am working on using Excel to print out an exact spacing for a terminal strip. I have used to 72 points to an inch to get the exact spacing but excel wont alow me to enter the exact number when I specify it, I notice it only allows the exact pixel by increments of 1. Is there anyway around this so I can type ine the exact number for the cell height? Hi AFAIK you can only define it in pixels -- Regards Frank Kabel Frankfurt, Germany "nascar24" <nascar24@discussions.microsoft.com> schrieb im Newsbeitrag news:B54455A5-628E-4FB4-9F55-7EFE307815A4@microsoft.com... > I am ...

is there anyway to access funcion written in c by vc++
hi Is this is possible to access ,a function written in C by VC++. Arbind >Is this is possible to access ,a function written in C by VC++. Yes. I suspect you need to know about using extern "C" to tell the compiler that your function is 'C' rather than C++. For example: extern "C" int MyFn( int p1 ); You often use it like this: extern "C" { #include "Your_C_Header.g" } Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq >#include "Your_C_Header.g" or even #include "Your_C_Header.h" who put that g key in the wa...

Wrapping clipart around text
When i word i can wrap my text around my pictures is there any way of doing the same in Publisher 2000. -- Thank you for reading my post. Hopefully you can answer my querie On the toolbar there is a hand holding a script. This is the wrap tool. Select the object, select the tool. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Shazza" <shazzalowe@hotmail.co.uk> wrote in message news:474A689F-DC18-4545-A73B-1E2CDDB34633@microsoft.com... > When i word i can wrap my text around my pictures is there any way of d...

Is there anyway to restore Default GAL
Is there anyway to restore the Default GAL after deleting it? You can recreate the Default GAL under the Global Address Lists container and apply the appropriate filter, from within Exchange System Manager. Regards, Colby -- Please do not send e-mail directly to this alias. This alias is for newsgroup purposes only. This posting is provided "AS IS" with no warranties, and confers no rights. "Stu" <mscedragon@hotmail.com> wrote in message news:0c2101c4a269$0f70e820$a501280a@phx.gbl... > Is there anyway to restore the Default GAL after deleting > it? ...

Is there anyway for my Outlook to e-mail me when a Calender event is coming up?
Instead of popping up a box, is there anyway I could have it e-mail me at two different e-mail addresses letting me know that something is coming up? -- Nocturnal @ http://www.randomfix.com See if any of the addins here help: http://www.slipstick.com/addins/calendar.htm#remind "Nocturnal" <nocturnal@anonymous.com> wrote in message news:OA5h$R58FHA.3952@TK2MSFTNGP12.phx.gbl... > Instead of popping up a box, is there anyway I could have it e-mail me at > two different e-mail addresses letting me know that something is coming > up? You have my software Send ...

Who writes this crap anyway?
I want to know if any of the developers use this program to manage anything more than their allowance? Microsoft should use Money 2005 for all of their accounting at Microsoft! I'm sure they would then ask for a refund too!!!!!!!!!!!!!!!!!!!!!! >-----Original Message----- >I want to know if any of the developers use this program to manage anything >more than their allowance? > > >. > I think I'll hold off on buying Money 2005 until there a service pack and a fix to the online issues. It's only been out a few weeks, but it looks like the beta testin...

avoid margin around full-page background color,achieve bleed?
Tried by setting full 8.5x11 margin in set-up, and by making background color tiff much larger than page, but still prints-out w/ c.1/4" white margin around all design color. How to avoid. Can we make bleed. elisa <elisa@discussions.microsoft.com> was very recently heard to utter: > Tried by setting full 8.5x11 margin in set-up, and by making > background color tiff much larger than page, but still prints-out w/ > c.1/4" white margin around all design color. There isn't a limit on the maximum length of post you can make. Therefore, you are allowed to write us...