Help: Seting the value of another cell with a formula

I know that there's a way to make a cell a particular value based on the
entries of a range, or array of cells, but is it possible to do the reverse,
using only one formula in a cell.

Here's what I'm trying to do:

What I would like to do is set one of a range of cell to have a value based
on the value in A2.
Example: Cell A1 has a value of  2, A2 has a value of 2007.  I want A10 to
equal A1, i.e.:2
               Cell A1 has a value of  2, A2 has a value of 2008.  I want
A11 to equal A1, i.e.:2
               Cell A1 has a value of  2, A2 has a value of 2009.  I want
A12 to equal A1, i.e.:2

I know that this can be done by filling a range of cells with the proper
formulas, but what I'm looking for is a formula that will set the value of a
third cell, based on the value of some other cell.  Something like

If I try: =IF(E9=0,"",E15=E9), but his simply returns a value of "False", It
doesn't set the value of E15 to the value of E9 which is what I want.

Let me know if you have any suggestions.  Thanks.
-- 
Dab

Cut off: yourhead to respond


0
10/17/2003 2:03:40 AM
excel 39879 articles. 2 followers. Follow

2 Replies
421 Views

Similar Articles

[PageSpeed] 41

Dab,

The model of a spreadsheet is that a cell contains a value (text or number)
or a formula that returns a value.  A cell can't change another cell.  Only
a macro can.  Or you.

In your example, you seem to want A10 to be what's in A1 when A2 is 2007,
2008 or 2009.  Putting =A1 in A10 will do that.  But I don't think that's
what you want.

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Dab" <yourheaddabuckto@hotmail.com> wrote in message
news:OYFdjLFlDHA.3320@tk2msftngp13.phx.gbl...
> I know that there's a way to make a cell a particular value based on the
> entries of a range, or array of cells, but is it possible to do the
reverse,
> using only one formula in a cell.
>
> Here's what I'm trying to do:
>
> What I would like to do is set one of a range of cell to have a value
based
> on the value in A2.
> Example: Cell A1 has a value of  2, A2 has a value of 2007.  I want A10 to
> equal A1, i.e.:2
>                Cell A1 has a value of  2, A2 has a value of 2008.  I want
> A11 to equal A1, i.e.:2
>                Cell A1 has a value of  2, A2 has a value of 2009.  I want
> A12 to equal A1, i.e.:2
>
> I know that this can be done by filling a range of cells with the proper
> formulas, but what I'm looking for is a formula that will set the value of
a
> third cell, based on the value of some other cell.  Something like
>
> If I try: =IF(E9=0,"",E15=E9), but his simply returns a value of "False",
It
> doesn't set the value of E15 to the value of E9 which is what I want.
>
> Let me know if you have any suggestions.  Thanks.
> -- 
> Dab
>
> Cut off: yourhead to respond
>
>


0
nowhere8060 (363)
10/17/2003 2:16:50 AM
Dab,

I meant to say "you seem to want A10 to be what's in A1 regardless of what's
in A2."  The formula will do that.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" <nowhere@verizon.net> wrote in message
news:%23eR59SFlDHA.684@TK2MSFTNGP09.phx.gbl...
> Dab,
>
> The model of a spreadsheet is that a cell contains a value (text or
number)
> or a formula that returns a value.  A cell can't change another cell.
Only
> a macro can.  Or you.
>
> In your example, you seem to want A10 to be what's in A1 when A2 is 2007,
> 2008 or 2009.  Putting =A1 in A10 will do that.  But I don't think that's
> what you want.
>
> -- 
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "Dab" <yourheaddabuckto@hotmail.com> wrote in message
> news:OYFdjLFlDHA.3320@tk2msftngp13.phx.gbl...
> > I know that there's a way to make a cell a particular value based on the
> > entries of a range, or array of cells, but is it possible to do the
> reverse,
> > using only one formula in a cell.
> >
> > Here's what I'm trying to do:
> >
> > What I would like to do is set one of a range of cell to have a value
> based
> > on the value in A2.
> > Example: Cell A1 has a value of  2, A2 has a value of 2007.  I want A10
to
> > equal A1, i.e.:2
> >                Cell A1 has a value of  2, A2 has a value of 2008.  I
want
> > A11 to equal A1, i.e.:2
> >                Cell A1 has a value of  2, A2 has a value of 2009.  I
want
> > A12 to equal A1, i.e.:2
> >
> > I know that this can be done by filling a range of cells with the proper
> > formulas, but what I'm looking for is a formula that will set the value
of
> a
> > third cell, based on the value of some other cell.  Something like
> >
> > If I try: =IF(E9=0,"",E15=E9), but his simply returns a value of
"False",
> It
> > doesn't set the value of E15 to the value of E9 which is what I want.
> >
> > Let me know if you have any suggestions.  Thanks.
> > -- 
> > Dab
> >
> > Cut off: yourhead to respond
> >
> >
>
>


0
nowhere8060 (363)
10/17/2003 7:33:59 PM
Reply:

Similar Artilces:

Auto Fill Cells
I am building an inpatient log for a nursing home. I need to auto fill the patient's name and address from their ID # Example in column C (ID#) 255 - would automatically fill in Column D (last name), Column E(first name), Column F(middle initial), and Column G(address) At this point - all of the cells are blank. Sounds nice. Where is the data coming from? Take a look at these links for some ideas of how you can go about doing this: http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlFunctions03.html#IndexMatch http://www.contextures.com/xlFunc...

comments in a cell #2
HYCH, Just a question? is there any way of using some vba that will add coments to a cell and have the comment box appear as it does if i add my own comments manually, would like the comment hidden until the cell is selected and then have it show up! obviously i know this happens when you manually process comments, but i would like the comment box to appear and display values that are in another worksheet within the same workbook. hope that makes sense. Steve Steve wrote: > > HYCH, > > Just a question? > > is there any way of using some vba that will add coments t...

shading cells that are not consecutive
Is it possbile to shade cells B10-16 and D14-8 and F2 and etc and forma them as a group? Thank yo -- Message posted from http://www.ExcelForum.com Use Ctrl to select all the rages you want selected then do th formatting you wish. Either that or choose one cell and do the formatting the way you want Then copy it and select the other cells and go to edit: paste special formats -- Message posted from http://www.ExcelForum.com ...

Replacing cell with text
Hi, I want to know if it is possible to do the following: Imagine I have a legend in which I say which text corresponds to whic letters ex: John -> J Harry -> H Susan -> S and what I want to do is when entering J, H or S in a cell, it wil change to the corresponding text. Is there a way of doing it without having to run a macro ? Thanks in advance Pedr ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Go to Tools > AutoCorrect Options (XL2002) and insert new...

Formula for counting specific cells which are greater than 1
I'm looking for a formula that will count specific cells (not a range - A17, A27, A37 and so on) that will have a certain value (above $1.00 to make it easy). So if the cell has a value of $1.00 or less it will not count in the total; If it is above $1.00 if will count. If A17 is above $1.00, A27 is $0.50 & A37 is $3.00 the count will be 2. ...

Help #4
I have a column with values yes no as mentioned below i want 1 if value is yes 0 if value is no in next column yes no yes yes no no yes yes Hi kiran In B1 use this formula and copy down =IF(A1="yes",1,0) -- Regards Ron de Bruin http://www.rondebruin.nl "kiran" <kiran@discussions.microsoft.com> wrote in message news:F3785964-28CB-4D7B-843B-6F684C00370B@microsoft.com... >I have a column with values yes no as mentioned below i want 1 if value is > yes 0 if value is no in next column > yes > no > yes > yes > no > no > yes > yes > ...

Please help!
Hello to all! I recently installed CRM 3.0 professional edition on Windows 2003 server standard edition. Now my manager wants to have sample database Adventure Works on the same server. How can I install Adventure works on already installed CRM 3.0 ? Or I have to reinstall whole CRM ? If yes, how can I specify during the installation that I need sample database to be installed ? This is really urgent question, and any help will be highly appreciated! thank you!!! Ozz, The AdventureWorks sample db isn't so much "installed" as it's run. It's an ..exe which you run...

Outlook Calendar help
Hello Outlook Folks, I use Outlook to keep reservations at a small motel. It's been working well for me for several years. I recently had a minor drive crash. The company the "repaired" it turned it into a major disaster. I have 2 drives, they formatted both drives for me. Now I have a few days work ahead of me to get back to where I was. Now, my question: I have pretty good backups on CD. Where does Outlook keep the calendar file? If I can pick & place this file I can get my reservations back and keep alot of people happy. Is this even possible? Any help is appreciate...

Dynamicly change spreadsheet tab names depending on cell value
Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 for each day of the month) These tabs are named - 1,2,3 e.t.c Is it possible to define rule that would ad "!" to day which is weekend day. In other words is it possible to dynamicly change tab name depending from value of cell ? If this is possbile how do i do this ? Right-click on the spreadsheet tab, select View Code and paste this in:- Private Sub Worksheet_Activate() ActiveSheet.Name = Range("A1").Value End Sub To test this I put the date in cell B2 and this formula into A1:- =TEX...

Need Help Enabling Flags in OWA for Public Folder
Hello-- I set up a public folders (not default public folders) with permissions for access to only a limited group via OWA. Everything is working great except no one, including the Domain Administrator can "flag" a post. The Flag Column header is grayed out and when you click on it, it merely depresses. Any advice on how to enable the flags would be appreciated. Many thanks, sd On Wed, 4 Oct 2006 09:45:02 -0700, razor <razor@discussions.microsoft.com> wrote: >Hello-- > >I set up a public folders (not default public folders) with permissions for >access to ...

Smart-shape to dumb-shape? Help with cross functional flow shape?
Greetings! For my purposes, I'd like to use the cross functional shape to build up swimlanes that don't change no mater how or what kind of shape is dropped on them. Using a background page is somewhat inconvenient for my pupose. Can you tell me how to kill the code or events that cause the cross-functional shape to interact with other shapes while leaving its other behaviour intact? (in particular, I don't want other shapes to become attached to a lane nor do I want the lane to change its width) Thanks in advance! The quick solution is to go to Tools > Options > S...

formatted values from single equation
I would really like to have a Excel spreadsheet with two worksheets, the first sheet has raw data (16 across, 100's down) and what I would like to do on the second sheet is add to the first row, 16 equations (lets say for now y=x). then underneath this the formatted data where x is cell A1 on the first worksheet and y is on the second worksheet. Obviously you would just normally in each cell on worksheet 2 type the equation in (='worksheet1'!A1), but I would like to show people the equation being used. Is this possible, does this message make any sense?? Nice -- mr_nice! ---...

IF Formula 05-17-10
I'm needing a formula that will do the following... If cell A1 is 1 it shows with the word Developing. If A1 is 2 it shows with the word Performing. If A1 is 3 it shows with the word Leading. Thanks you could put this formula in b1 =CHOOSE(A1,"Developing","Performing","Leading") -- Gary Keramidas Excel 2003 "Redroc" <Redroc@discussions.microsoft.com> wrote in message news:7C464B73-6438-4BD7-8002-DC494926E073@microsoft.com... > I'm needing a formula that will do the following... > > If cell A1 is 1 i...

Another Countif??
I have a spreadsheet containing various fields which is used to monito the amount of telephone calls logged by members of my department. I've used the countif function to break this down to calls logged b each individual. The next column I would like to add is the amount o calls *completed* by each individual. The completed column onl contains a true or false value. So at the moment it would read:- Name Calls Completed JOHN 5 ??? Not sure if I've explained this well but what I'm aiming for is t count Johns calls in one column(done) and in the next co...

substract cell F from cell H and total into cell I
i am new to using excel. i need to be able to put in pay price in cell F and selling price into cell H and get total in cell i. so it would have to substract cell F from cell H and put total into cell i. how do i do this? currency only Assuming your data is in Row 1, put this formula in I1 and press Enter.........be sure and type it just as shown, as the leading equal sign is what tells Excel that the following characters are a formula. =H1-F1 Vaya con Dios, Chuck, CABGx3 "vadarpug" <vadarpug@discussions.microsoft.com> wrote in message news:617768AC-D261-47AE-861A-E6BFD...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

Need help saving a file!
Well let me start off by saying that I feel lost using the Office 97 programs! I had to make a new resume so I got myself a template off of the site here. It opened up in publisher so that's where I created my resume. I've put a lot of time into it and now realize that I think I made a huge mistake! How the heck do I save it as a more "acceptable" file without screwing up the format??? (for uploading as an attachment) Or is that just not possible? I've tried saving it as a word document, but like I said, screws up the whole format. I'm really lost, don...

Change a cell's value using a button
Hi - I know its possible but have no idea how to do it - How do I set up a cell with two buttons attached to it that increase and decrease the value in the cell? I.e. if I have a cell with 10% in it and hit the "up" button, the number goes up by 0.5% and down by the same amount if I hit the "down" button? I am pretty good in Excel but dont know anything about macros - but Im really not sure where you find this function. Thanks for your help in advance anyone! -- Ashley No macros required. Use a spin button control rather than 2 buttons. Follow these steps: 1. Go to...

I need complete idiot proof excel setup database help. please
I need to learn all about Excel and how to make a database.. I have read and read and done practise tutorials but I just cannot grasp the concept, it completely goes over my head everytime. I am quick to learn when I am being shown or told exactly what to do, can anyone help me learn all about Excel.. ? The first thing you need to know is that Excel is not a database application, and while it is commonly used for databases, it doesn't do them well. Expecting a database developed in XL to be "complete idiot proof" is setting yourself up for disaster. Without knowing any...

Converting Text Values to Dates
Is there a way to convert a column of text, date values (entered as 81096, for example) to valid date format, 8/10/96? I could not get the DATEVALUE function to do this. Hi! Select the range of cells in question. Goto Data>Text to Columns Click NEXT twice Select Column Data Format: DATE. Then choose the format from the drop down Finish Biff "Frank Winston" <FrankWinston@discussions.microsoft.com> wrote in message news:7CAF0A61-E8A7-4D18-B035-0D475E9A858C@microsoft.com... > Is there a way to convert a column of text, date values (entered as 81096, > for example...

User form Default value in text box
Hi all. Quick question. I have a user form in excel which has a text box which i want to have the value defaulted to the current date. I've looked at several posting on this site but haven't gotten it to work yet. Any ideas on how to set the text box with the current date as a default value? Any help or direction is appreciated. Thanks. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201006/1 Hi, Try this way Private Sub UserForm_Activate() TextBox1.Text = Date End Sub -- Mike When competing hypotheses are oth...

Calculate Formulas in Highlighted Cells Only--not whole sheet
In office 97 I was able to recalc only the cells highlighted (control + L) as opposed to the entire sheet or workbook. In Excel 2002, how do I recalc only the cells I have highlighted? Thanks I don't recall this shortcut in xl (any version). I'm guessing that you had a macro that did something special. In xl2002, you could use a macro like this (assigned to ctrl-shift-l) Option Explicit Sub testme() Selection.Calculate End Sub But I think I've read posts that calculating a single range can be bad--it can screw up the calculation dependencies. I think that Charles Will...

How do I copy my calendar from Exchange to another?
Hi, My department is becoming a company of its own so we're getting our own Exchange Server 2003. We're currently on our parent company's Exchange 2000 machine. Once our own Exchange 2003 server is up and running, how do I copy my calendar to the new machine? Thanks, Sam "Sam" <sam@globalwebcentral.com> wrote in news:OqAjOpW2DHA.1740 @TK2MSFTNGP12.phx.gbl: > Once our own Exchange 2003 server is up and running, how do I copy my > calendar to the new machine? One calendar? Export it to a pst file and import it into your new mailbox. Multiple calendars...

Need immediate help with Access to Outlook Please
This is my 3rd request.... Please, thank you.... I went through a windows update and was asked to reboot which I did. I had Outlook open and closed it before rebooting - it was opened to contacts. when I tried to access Outlook I got an error message saying "your personal address book could not be opened. the file is either not accessible or not a personal address book. choose retry to access your personal address book, or choose cancel to not open your personal address book with this application." I have no other application to open this address book with! my contac...

Dynamic Range Defined by Value of Cell
I am just getting started with dynamic ranges. I have data as follows: A B C Product1 qty cost Product2 qty cost .... ProductN qty cost TOTAL qtytot costtot The number of products varies. There is other data below this that is unrelated. How can I define a range dynamically that will always capture A:C and as many rows up and including the first time it finds "TOTAL" in column A? Any help greatly appreciated. Thank you. I don't know what you mean by "define a range" b...