Access a cell's Formula, rather than Value

Hello all:

I would like to modify this formula so that I use the formula of the cell
specified by x,
rather than the value stored in the cell.

=CHOOSE(x, Sheet1!A1, Sheet2!A1)


Thanks,
John



0
none89 (807)
5/18/2007 7:21:33 PM
excel 39879 articles. 2 followers. Follow

5 Replies
423 Views

Similar Articles

[PageSpeed] 36

Not possible, you would need VBA for this. I can't see how by using CHOOSE 
you would be able to do this even if it was possible to get the formula to 
replace x since x should be an index number either 1 or 2 (in this case) if 
you have 2 values? Or have I totally misunderstood?



-- 
Regards,

Peo Sjoblom


"John Broderick" <none@none.com> wrote in message 
news:e1RJAHYmHHA.4552@TK2MSFTNGP04.phx.gbl...
> Hello all:
>
> I would like to modify this formula so that I use the formula of the cell
> specified by x,
> rather than the value stored in the cell.
>
> =CHOOSE(x, Sheet1!A1, Sheet2!A1)
>
>
> Thanks,
> John
>
>
> 


0
terre081 (3244)
5/18/2007 7:33:53 PM
Peo,

thanks for replying. Let me clarify:

x would be 1 or 2 based on the contents of another cell.
For example in Sheet3!A1 I have the formula:
=CHOOSE(A10, Sheet1!A1, Sheet2!A1)

If A10=1, I want the cell cotaining this formula to use the formula that
Sheet1!A1 has.
If A10=2, I want the cell cotaining this formula to use the formula that
Sheet2!A1 has.

Regards,
John


"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:u$qp%23NYmHHA.3588@TK2MSFTNGP02.phx.gbl...
> Not possible, you would need VBA for this. I can't see how by using CHOOSE
> you would be able to do this even if it was possible to get the formula to
> replace x since x should be an index number either 1 or 2 (in this case)
if
> you have 2 values? Or have I totally misunderstood?
>
>
>
> -- 
> Regards,
>
> Peo Sjoblom
>
>
> "John Broderick" <none@none.com> wrote in message
> news:e1RJAHYmHHA.4552@TK2MSFTNGP04.phx.gbl...
> > Hello all:
> >
> > I would like to modify this formula so that I use the formula of the
cell
> > specified by x,
> > rather than the value stored in the cell.
> >
> > =CHOOSE(x, Sheet1!A1, Sheet2!A1)
> >
> >
> > Thanks,
> > John
> >
> >
> >
>
>


0
none89 (807)
5/18/2007 7:46:59 PM
It should work *exactly* like that, just as you have now!

What problem are you having?
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"John Broderick" <none@none.com> wrote in message 
news:ufWDPVYmHHA.1216@TK2MSFTNGP03.phx.gbl...
> Peo,
>
> thanks for replying. Let me clarify:
>
> x would be 1 or 2 based on the contents of another cell.
> For example in Sheet3!A1 I have the formula:
> =CHOOSE(A10, Sheet1!A1, Sheet2!A1)
>
> If A10=1, I want the cell cotaining this formula to use the formula that
> Sheet1!A1 has.
> If A10=2, I want the cell cotaining this formula to use the formula that
> Sheet2!A1 has.
>
> Regards,
> John
>
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:u$qp%23NYmHHA.3588@TK2MSFTNGP02.phx.gbl...
>> Not possible, you would need VBA for this. I can't see how by using 
>> CHOOSE
>> you would be able to do this even if it was possible to get the formula 
>> to
>> replace x since x should be an index number either 1 or 2 (in this case)
> if
>> you have 2 values? Or have I totally misunderstood?
>>
>>
>>
>> -- 
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "John Broderick" <none@none.com> wrote in message
>> news:e1RJAHYmHHA.4552@TK2MSFTNGP04.phx.gbl...
>> > Hello all:
>> >
>> > I would like to modify this formula so that I use the formula of the
> cell
>> > specified by x,
>> > rather than the value stored in the cell.
>> >
>> > =CHOOSE(x, Sheet1!A1, Sheet2!A1)
>> >
>> >
>> > Thanks,
>> > John
>> >
>> >
>> >
>>
>>
>
> 


0
ragdyer1 (4060)
5/18/2007 8:24:15 PM
Ahh!  I see, unfortunately you would still need something that would 
evaluate the cell to return, however you can hard code formulas in the 
CHOOSE formula like

=CHOOSE(A10, SUM(Sheet1!D1:D5), Sheet2!A1)

as an example so basically you could use the formula that is in Sheet1!A1 
and so on. If not you would need a UDF written in VBA


-- 
Regards,

Peo Sjoblom



"John Broderick" <none@none.com> wrote in message 
news:ufWDPVYmHHA.1216@TK2MSFTNGP03.phx.gbl...
> Peo,
>
> thanks for replying. Let me clarify:
>
> x would be 1 or 2 based on the contents of another cell.
> For example in Sheet3!A1 I have the formula:
> =CHOOSE(A10, Sheet1!A1, Sheet2!A1)
>
> If A10=1, I want the cell cotaining this formula to use the formula that
> Sheet1!A1 has.
> If A10=2, I want the cell cotaining this formula to use the formula that
> Sheet2!A1 has.
>
> Regards,
> John
>
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:u$qp%23NYmHHA.3588@TK2MSFTNGP02.phx.gbl...
>> Not possible, you would need VBA for this. I can't see how by using 
>> CHOOSE
>> you would be able to do this even if it was possible to get the formula 
>> to
>> replace x since x should be an index number either 1 or 2 (in this case)
> if
>> you have 2 values? Or have I totally misunderstood?
>>
>>
>>
>> -- 
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "John Broderick" <none@none.com> wrote in message
>> news:e1RJAHYmHHA.4552@TK2MSFTNGP04.phx.gbl...
>> > Hello all:
>> >
>> > I would like to modify this formula so that I use the formula of the
> cell
>> > specified by x,
>> > rather than the value stored in the cell.
>> >
>> > =CHOOSE(x, Sheet1!A1, Sheet2!A1)
>> >
>> >
>> > Thanks,
>> > John
>> >
>> >
>> >
>>
>>
>
> 


0
terre081 (3244)
5/18/2007 8:30:33 PM
On May 18, 2:46 pm, "John Broderick" <n...@none.com> wrote:
> Peo,
>
> thanks for replying. Let me clarify:
>
> x would be 1 or 2 based on the contents of another cell.
> For example in Sheet3!A1 I have the formula:
> =CHOOSE(A10, Sheet1!A1, Sheet2!A1)
>
> If A10=1, I want the cell cotaining this formula to use the formula that
> Sheet1!A1 has.
> If A10=2, I want the cell cotaining this formula to use the formula that
> Sheet2!A1 has.
>
> Regards,
> John
>
> "Peo Sjoblom" <terr...@mvps.org> wrote in message
>
> news:u$qp%23NYmHHA.3588@TK2MSFTNGP02.phx.gbl...
>
>
>
> > Not possible, you would need VBA for this. I can't see how by using CHOOSE
> > you would be able to do this even if it was possible to get the formula to
> > replace x since x should be an index number either 1 or 2 (in this case)
> if
> > you have 2 values? Or have I totally misunderstood?
>
> > --
> > Regards,
>
> > Peo Sjoblom
>
> > "John Broderick" <n...@none.com> wrote in message
> >news:e1RJAHYmHHA.4552@TK2MSFTNGP04.phx.gbl...
> > > Hello all:
>
> > > I would like to modify this formula so that I use the formula of the
> cell
> > > specified by x,
> > > rather than the value stored in the cell.
>
> > > =CHOOSE(x, Sheet1!A1, Sheet2!A1)
>
> > > Thanks,
> > > John- Hide quoted text -
>
> - Show quoted text -

Look at the INDEX & INDIRECT functions, you may have to chance that
way. The Indirect function uses the sheet names.

=INDIRECT(SheetName &"!e2:e57")



Dave

0
5/18/2007 8:33:52 PM
Reply:

Similar Artilces:

format changes when copying from one cell to another
I'm sure this is simple, but it is not clear to me... I am copying the contents of a group of cells that exist on one worksheet to another. In Sheet2, I tell cell A1 that it should equal Sheet1's A1 value. That works fine. But if the cell is blank on Sheet1, it comes up as "0" on Sheet2. I figured it was probably assuming numeric formatting, but when I right-click > format cell on both Sheet1 and Sheet2, both show up as "general", so why would Sheet2 take a blank value and populate it with "0" ?? I gather than you are putting a formula in one...

HTTP Error 401.3
Hi, I hope someone can help me. CRM worked fine for a while, but suddenly everybody, except the CRMAdmin account gets the following error: HTTP Error 401.3 - Unauthorized: Access is denied due to an ACL set on the requested resource. The website is runnning on SBS2003 and i created the website myself before installing CRM. The folder where the webdata is stored is :\inetpub\MSCRM IUSR account does have the right permissions.. I cannot find any messages in the IIS website logfile I found some articles about this error, but i do not know where to start because they assume you have the w...

Move to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7 009601098-9 2004 000801098-...

Results from blank linked cells
I am linking cells from different worksheets in the same workbook, using the copy/paste/link cell method. How can I get a blank space (as opposed to the zero I am presently getting), in the destination, if the source cell is blank. I am linking a input sheet to several forms that must be sent out, but I don't want a form that will have a number of zeros in it. =if(sheet1!a1="","",sheet1!a1) If the linked cell looks empty, show empty, else show the value. Mr. Anolog wrote: > > I am linking cells from different worksheets in the same workbook, using the &g...

Outlook Web Access attachment problem
We have a program that will generate a .snp file and then email it to whatever recipient it needs to go to. If they receive the email in Outlook it will allow them to open the file properly with the Snapshot viewer. However, if they go to the same email in OWA and try to save the attachment, it attempts to save it as an .mdb file. Why would OWA ignore the original extension and try to give it a different one? "Joe Giddings" <j_nospam_giddings@thehammocksource.com> wrote in news:jI9gd.11726$ta5.6952@newsread3.news.atl.earthlink.net: > We have a program that will genera...

Can you paste to protected spreadsheet and skip over locked cells?
I have a spreadsheet that has some cells locked and others that are not. I need to refresh the data on the page every month. I want to be able to copy the new data and paste it into the spreadsheet. The catch is that I do not want the locked cells to be changed. Is there a way to paste so that Excel will ignore those locked cells and skip over them? At this time it says that cells are locked and does not paste anything. Thanks Webmonk wrote... >I have a spreadsheet that has some cells locked and others that are >not. I need to refresh the data on the page every month. I want to...

Case select returning error when cell contains #N/A : how must i avoid this error
Title says it all Thanks, Luc maybe this will do what you want Sub test() If Not IsError(Range("C1").Value) Then Select Case Range("C1").Value Case 1 MsgBox "1" Case 2 MsgBox "2" End Select End If End Sub -- Gary Keramidas Excel 2003 "Luc" <lferr@live.be> wrote in message news:7AB07996-F1EE-41C1-8D9D-8A7DA54CED91@microsoft.com... > Title says it all > > Thanks, > > Luc Thanks for your help !!!!! Luc "Gary Keramidas" <GKeramidasAtMSN.com> schreef in berich...

URL access
Is URL access possible in a view of "account" in CRM3.0? In addition, can you hand search condition? Hello, I searched the SDK for this, but only found out about URL addressable forms. Seems to me like you have to write a custom ASP.NET web application (grid) to write this behavior. Anyone else? Best regards, Merijn van Mourik "a-kun" wrote: > Is URL access possible in a view of "account" in CRM3.0? > In addition, can you hand search condition? ...

With a Query in Access 2007, How can I Create This Query
I need a query that will list all records in table 1 for which there are no auditor records (Table 3). Somehow, I need to use the relationship between tables 2 and 3 to find what's not in table 1. The following query gives me a list of all records that do have auditor records. I'm at a dead end on this one. Query SELECT PTOTNamesTbl.PTOTAuditingTherapist, PTOTAuditingTherapist.PTOTFirstName, PTOTAuditingTherapist.PTOTLastName, AuditDetailInitialEval.TherapistLastName, PTOTNamesTbl.PTOTFirstName, PTOTNamesTbl.PTOTLastName, AuditDetailInitialEval.Medicare, AuditDet...

How to slant cells in Excel on top of chart at about 45 degrees
Trying to make a chart and slant cells at the top at about a 45 degree angle with borders and still be able to type into it. Is this possible? ckricci Wrote: > Trying to make a chart and slant cells at the top at about a 45 degree > angle > with borders and still be able to type into it. Is this possible? HI ckricci Try Format > Cells > Alignment Tab and set the Orientation to 45 degress -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2...

Easy Formula?
I Have A Column A & B I Have A Formula At Column C =sum(a2*b2)+(a3*b3)+(a4*b4) Answer Is 31 I Want To Multiply A1 And B1 Up To The Last Range Of Data Add The Total Product The Problem Is: If Have A Lot Of Data In Column A & B, The Formula Is So Very Long Theres Any Easy Formula? A B 3 5 2 7 2 2 -- xtrmhyper ------------------------------------------------------------------------ xtrmhyper's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23851 View this thread: http://www.excelforum.com/showthread.php?threadid=3908...

lotus approach queries VS access queries.
Hi, We are migrationg from approach to access. My basic underastanding of the procedure is that the data has to be migrated and all the other features like forms and reports have to be recreated. Is 'Approach query' different from MS Access query? Can this be assumed to be replaced by Access query? cheers, Nuti ...

Access 2007 combobox will not "Auto expand"
I have a database converted from access 2003 to 2007. Now ALL comboxes will not allow the "autoexpand" functionality. Obviously I set this property to yes (both in the properties window AND programmatically). Most, but not all, of the comboboxes are populated by queries on ODBC coupled MSSQL tables. Again, in 2003, this worked fine. Anybody an idea ? Thanks in advance for any suggestions, Jos --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://www.shemes.com/ =- ...

validation list or combo box dependant on cell value
Am i able to determine the values shown in either a validation list or combo box being dependant on a value in another cell? ie: Cell A1 = BOB then validation or combo box would then base it's list from the named range (or whatever the solution may be) based on Bob. if i was to change A1 to ROY then it would also change the underlying list? I have tried everything that i think SHOULD work but that it pretty limited... thanks in advance rich I'd start with Debra Dalgleish's site: http://contextures.com/xlDataVal02.html Richard Edwards wrote: > > Am i able to deter...

odbc connection with access comma is not showing
I have made a ODBC connection between NAVISION and Access. When I run the query a number "23" shows up as "2300000". I think the comma is not showing maybe because of setting problem. I do not know how to fix this. In the Navsion database figures are correct. sounds like a ODBC driver problem. Probably due to precision see http://dynamicsuser.net/forums/t/6072.aspx Pieter "Remko Strik" <Remko Strik@discussions.microsoft.com> wrote in message news:F83F3871-DDCB-44E7-BC50-6CAD87659284@microsoft.com... >I have made a ODBC connection between NAVISION ...

worksheet labels based on cell results
How can I build a macro to use the contents of several cells in a column to label a corresponding number of worksheets with their contents. Ideally this would also build links to the tabs so that a user could click on a specific cell (in that column) and be redirected to the corresponding worksheet Thanks, Mitch Hi for labeling the tab try something like activesheet.name=activesheet.range("A1").value For the second question try using a Hyperlink (Insert - Hyperlink) -- Regards Frank Kabel Frankfurt, Germany Mitch wrote: > How can I build a macro to use the contents of ...

retain only rows with condition that cells in column H containing "AU"
I have a large database containing columns with one column specifies the product name. In one instances, I need to retain only rows with condition that cells in column H containing "AU" of the entire string in the cell. Delete those rows without it. The problem is , that the AU of the string can appear in any position, not a fixed position. Is there any simple way of doing it? Many Thanks! Regards, Bora Hi, You can use AutoFilter. Choose the command Data, Filter, AutoFilter. Then open the autofilter drop down in the column H and choose Custom and from the first drop down, t...

time formula question
This formula works great if the ending time is before 0:00. =IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)* {2;3;4})),"") $C$9:$C$11 is my starting time i.e. 20:00 $E$9:$E$11 is my ending time i.e. 04:30 How can I get this to work if A21 = 20:15? TIA, David Try this: =IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((($C$9:$C$11<$E$9:$E$11)*(A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)+(($C$9:$C$11>=$E$9:$E$11)*((A21>=$C$9:$C$11)+(A21<=$E$9:$E$11)))*{2;3;4})),"") HTH -- AP "David" <dfizer@r...

Cannot enter an array formula
I'm trying to help a user on a toshiba laptop with array formulas. For some reason he cannot create an array formula. The formula evaluates on everyone else's laptop but his. He has Windows 98 and Office XP Professional. Has anyone encountered this? Ryan If he can't create it then you wouldn't know that it doesn't evaluate, so which is it? What's the formula? and can he actually create it but it doesn't appear to have the right answer. If you hit F9 does the right answer appear? -- Regards Ken....................... Microsoft MVP - Excel ...

Sorting with Column has Formula
Hi everyone, I never imagined that the formula in the column would affect the sorting order in any way, but it does in my case. Below is the formula in that I have in Col I, and I'd like to sort it in Ascending order, but the result is that it sorts with all the empty rows on top and the one with the result from the formula at the bottom. I assume it consider the "I" in the "IF" function in the formula, but I'm not sure. Can anyone tell me how to fix this please? =IF(E2="","",IF(J2="X","Priority #1",IF(...

IN EXCEL, WHEN I CLICK ONA SINGLE CELL It HIGHLIGHTS WHOLE Page
please help Hi maybe: http://www.mvps.org/dmcritchie/excel/ghosting.txt -- Regards Frank Kabel Frankfurt, Germany "confused" <confused@discussions.microsoft.com> schrieb im Newsbeitrag news:36180425-96C2-4368-9DE0-3E60741154FF@microsoft.com... > please help Just click on a cell and nothing else? Or perhaps you mean click on a cell then as you move the cursor around other cells are selected like the cursor is stuck? If the former, you must have some event code which selects the cells or you are clicking on the gray box at intersect of rows and columns(top left above ro...

Help with formula #28
I have an excel spread sheet with formulas that work correctly, when I email them to another person and they open the attachment all looks ok. When they save the excel file to their PC and open it the formula cells now all have #Name in them. Anyone know what is causing this??? The error message gives us a clue: Excel can't find something - things to check: 1. Make sure the receivers all are using the same add-ins as the sender 2. Make sure that personal.xls is the same for the receivers as the sender 3. Make sure all defined names are the same -- Gary's Student "Play...

Formula so that response is >value
The same cell has two formulas, IF(D3>0,LOG(C3/F3),IF(D3<1,LOG(C3/10)). For example, if C3 is 1.0E+06, D3 is 100, then F3 will be 1.0E+03 (due to another formula), and the response for the first part should read 3.0. If D3 is 0, F3 will be <10 (due to another formula), and the response should read >5.0, not simply 5.0. I'm not sure I follow your formulas as described, but if you're looking to have your results display the ">" character along with the value, then maybe this example will lead you in the right direction: Let's say cell A1 contains ...

Linked Table Manager Doesn't Work in Access 2003
I recently upgraded to Access 2003 and have found that the menu option: Tools/Database Utilities/Linked Table Manager no longer works correctly for re-linking my front end database to the backend. No tables show in the table list. If I click “Select All” and then fill in the path to the backend database I get the following error message: Method ‘List’ of object ‘IfieldListWnd’ failed. As a work around I have to delete all the table links from the front-end and then File/Get External Data/Link Tables. Is anybody else having this problem? Thanks in advance for your help. "...

Help with cells auto formatting
Hi, I have posted a similar question before however I never really got this sorted so sorry for repeating myself. Basically I use two spreadsheets daily at work all with various information on and various formats in each Column. My problem is when I close the spreadsheet and reopen it the cells that are formatted as 'general' or 'number' turn into Euro currencies. Does anyone know why this happens or how I can stop it? The spreadsheets aren't stored locally they are stored on a serve that only myself and my boss can get onto and we both have the same p...