Combining cells to create a formula

```Hi,

I have two cells that I want to combine to have a working formula

B1 = sum
B2 = d3
b3 = =b1&"("&b2&")"
D3 = 10

The result in b3 is :   sum(d3)

How do I get this to result in the actual value in D3.

I know I can simply write =D3, but the actual reason for combining the two cells is more complicated than explained here.

Thanks
```
 0
anonymous (74722)
1/28/2004 9:31:14 PM
excel.misc 78881 articles. 5 followers.

3 Replies
285 Views

Similar Articles

[PageSpeed] 42

```Hi

I think you are perhaps looking for the Indirect function
=INDIRECT("D"&ROW(3:3))
would return the value contained in D3
As you copy down, the formula would alter to 4, 5 etc. represent D4, D5 etc.

--
Regards
Roger Govier
"JYG" <anonymous@discussions.microsoft.com> wrote in message
news:91BE6186-1BE6-4F1B-849F-794003A144F8@microsoft.com...
> Hi,
>
> I have two cells that I want to combine to have a working formula
>
> B1 = sum
> B2 = d3
> b3 = =b1&"("&b2&")"
> D3 = 10
>
> The result in b3 is :   sum(d3)
>
> How do I get this to result in the actual value in D3.
>
> I know I can simply write =D3, but the actual reason for combining the two
cells is more complicated than explained here.
>
> Thanks

```
 0
roger1109 (65)
1/28/2004 9:49:09 PM
```Thanks Roger,
In fact I am trying QuoteCat from QuoteCat.com.

It's purpose is to provide quotes from the sock market.

Here is a sample to get Nortel's quote :

=QCAT|Yahoo!NT.TO\P

The \P is to get the price, there can be different
information that we can get.

Focusing on the part NT.TO wich represent the stock quote,
I want to have a formula that will take this information
from an adjacent cell. In that fashion I can simply copy
and paste all the quotes I want without have to retype the
hole thing for each quotes.

Exemple :
A               B
1    NT.TO         =QCAT|Yahoo!&A1&\P
2    AC.TO         =QCAT|Yahoo!&A2&\P
3    QTC.TO        =QCAT|Yahoo!&A3&\P

The results in column B should be the price of the stock
indicated in column A.

Thanks

>-----Original Message-----
>Hi
>
>I think you are perhaps looking for the Indirect function
>=INDIRECT("D"&ROW(3:3))
>would return the value contained in D3
>As you copy down, the formula would alter to 4, 5 etc.
represent D4, D5 etc.
>
>--
>Regards
>Roger Govier
>"JYG" <anonymous@discussions.microsoft.com> wrote in
message
>news:91BE6186-1BE6-4F1B-849F-794003A144F8@microsoft.com...
>> Hi,
>>
>> I have two cells that I want to combine to have a
working formula
>>
>> B1 = sum
>> B2 = d3
>> b3 = =b1&"("&b2&")"
>> D3 = 10
>>
>> The result in b3 is :   sum(d3)
>>
>> How do I get this to result in the actual value in D3.
>>
>> I know I can simply write =D3, but the actual reason
for combining the two
>cells is more complicated than explained here.
>>
>> Thanks
>
>
>.
>
```
 0
anonymous (74722)
1/28/2004 11:48:06 PM
```Hi

Try
=INDIRECT("QCAT|Yahoo!"&A1&"\P")

--
Regards
Roger Govier
"JYG" <anonymous@discussions.microsoft.com> wrote in message
news:667c01c3e5f9\$2d4aa570\$a601280a@phx.gbl...
> Thanks Roger,
> In fact I am trying QuoteCat from QuoteCat.com.
>
> It's purpose is to provide quotes from the sock market.
>
> Here is a sample to get Nortel's quote :
>
> =QCAT|Yahoo!NT.TO\P
>
> The \P is to get the price, there can be different
> information that we can get.
>
> Focusing on the part NT.TO wich represent the stock quote,
> I want to have a formula that will take this information
> from an adjacent cell. In that fashion I can simply copy
> and paste all the quotes I want without have to retype the
> hole thing for each quotes.
>
> Exemple :
>      A               B
> 1    NT.TO         =QCAT|Yahoo!&A1&\P
> 2    AC.TO         =QCAT|Yahoo!&A2&\P
> 3    QTC.TO        =QCAT|Yahoo!&A3&\P
>
> The results in column B should be the price of the stock
> indicated in column A.
>
> Thanks
>
>
> >-----Original Message-----
> >Hi
> >
> >I think you are perhaps looking for the Indirect function
> >=INDIRECT("D"&ROW(3:3))
> >would return the value contained in D3
> >As you copy down, the formula would alter to 4, 5 etc.
> represent D4, D5 etc.
> >
> >--
> >Regards
> >Roger Govier
> >"JYG" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:91BE6186-1BE6-4F1B-849F-794003A144F8@microsoft.com...
> >> Hi,
> >>
> >> I have two cells that I want to combine to have a
> working formula
> >>
> >> B1 = sum
> >> B2 = d3
> >> b3 = =b1&"("&b2&")"
> >> D3 = 10
> >>
> >> The result in b3 is :   sum(d3)
> >>
> >> How do I get this to result in the actual value in D3.
> >>
> >> I know I can simply write =D3, but the actual reason
> for combining the two
> >cells is more complicated than explained here.
> >>
> >> Thanks
> >
> >
> >.
> >

```
 0
roger1109 (65)
1/29/2004 11:44:58 AM

Similar Artilces:

How can I create an invoicing database in Excel?
I would like to know how to create an invoicing database, or register in Excel. (Invoice + tax = total) Are there any templates free online? thanks How about a web search? http://office.microsoft.com/en-us/templates/default.aspx search for invoice -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Vcollins" <Vcollins@discussions.microsoft.com> wrote in message news:C46E9FE2-0B38-4468-9B00-126B7EE5C9A2@microsoft.com... >I would like to know how to create an invoicing database, or register in &...

I am looking for a cell formula that would recover the spreadsheet name and display it in a cell - more or less as "=CELL("row";A1) function is able to return the line number of cell A1 or as "=CELL("filename";C18)" will return the full path of the Excel workbook housing cell C18... Thanks a lot - Alain79 See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "Alain79" <desaivresanospam@free.fr> wrote in message news:ddsce3\$94f\$1@s1.news.oleane.net... > I am looking for a cell formula that would recover the spre...

Writing formula for excel worksheet
Item Rate1 Rate2 Rate3 A 3 4 6 B 8 5 23 C 56 5 78 Above is my excel sheet. I want to write some formula so that in every row cell having minimum rate should come bold. Like For Item A - Rate1(3) for Item B - Rate2(5) For Item C - Rate2 (5) There may be a neater way but this works. Somewhere in you sheet in a cell type =min(A1:a4), I used L1 Change this to watever range you are trying to evaluate. Then select this range and click format - conditional format - cell value - is equal to =(\$L\$1) and apply your bold font. The lowest value in the range will then become bold. Mike "Gaur...

sum values from range of cells if cells correcponding have the sam
hiya, i have a small project and i've spent too much time on this already - can't figure it out myself. What i need to do is to create a formula which will find the same values in column A and will sum the corresponding to them values from columns B:E. this is a sort of result i want to get: A B C D E F RESULT: 1 A1 1 5 2 3 A1 31 2 B5 2 6 3 3 A2 19 3 C1 3 7 4 3 B1 22 4 A1 4 8 5 3 B5 37 5 B5 5 9 6 3 C1 33 6 C2 6 1 6 3 C2 16 7 A2 7 2 7 3 8 B1 8 3 8 3 9 C1 9 4 0 3 I would appreciate any ideas how to do this. Thanks and regards, Assume in G1 down are the unique item...

I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. I am using 2003 Publisher. I created the book "Tri-Puzzles for Genesis - I"...

Creating Duplicate Info
I have five cells of info that need to be added to over 2000 other sku's. Example I have A, B, C, D, E and 1 - 2000 I need it to be 1A, 1B, 1C, 1D, 1E, 2A, 2B etc. I was wondering is there a faster way instead of insterting 5 lines 2000 times? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201003/1 You can do it easily with code. Sub AAA() Dim Letters(1 To 5) As String Dim LNdx As Long Dim N As Long Dim RowNum As Long Letters(1) = "A" Letters(2) = "B" Letters(3) = "C" ...

RETAIL MARK UP FORMULA
does excel have retail mark up formula built in? No. You just use the formula. Selling = Cost * (1 + Markup%) -- Regards, Fred Please reply to newsgroup, not e-mail "jimbo" <jimbo@discussions.microsoft.com> wrote in message news:F862FB6B-C25F-483F-A480-F3F7C9971068@microsoft.com... > does excel have retail mark up formula built in? ...

Excel example-formula C4+C5 adds before data in C5 Can I get it t.
I'm trying to create a formula (for example): In cell D5 I want it to add C4+C5. I would like it to work only after there is data in C5, but not before. How can I get it to work only when there is data in C4 & C5? Right now its showing what I have in C4. =if(count(c4:c5)<2,"Not enough values!",c4+c5) New Excel User wrote: > > I'm trying to create a formula (for example): > In cell D5 I want it to add C4+C5. I would like it to work only after there > is data in C5, but not before. How can I get it to work only when there is > data in C4 & C5? R...

How to create "Space Bar hit" action setting?
Hello All, I have this slide with a long animation on a slide that is off mousclick transition. With a hit on the space bar the animation jumps to end. As the presentation is with mouse controlled navigation I do not want users to use the space bar (or they start hitting through the slide sequence) to end the animation. So, is there a way to 'end animation sequence' (like a space bar hit) as an action setting on an object? Thanx, Guurt ...

Paste Several Values into one Cell
Hi all. I have data in several cells (I2:GJ2) on one row. I want to copy all of this data into one cell as it appears now (in a particular sequence). Is this possible? Thx in advance. Not sure if this is what you want but to concatenate data from several cells into 1 cell, use formula =Concatenate(I2," ",H2," ",G2) just add more cell references with commas and " " to give a space between each cell value for all the cells you want. "sgarrett" <anonymous@discussions.microsoft.com> wrote in message news:110F5183-D1CE-4C5A-A7F8-90D01E500FBE@micr...

trouble with copying a cell to other cells.
Info adds +1 (so 3oo goes to 301) and in the same function, 2 stays a 2 when copied to another cell. How do I mark what changes and what doesn't? I found the info posted on a different post. by BenjieLop for Copying a formula 9/20/2004 I want to copy a formula from one cell to several others, it is a basic formula =c5*d12 when i copy and paste the formula changes..( =c6*d13 and so on...) I want the second part d12 to change but i want to anchor the first part somehow so the c5 is constant in every formula c5*d13 c5*d14 etc.. The formula =\$C\$5*D12 will do the trick ...

More Formula Help
Hi again, I am trying to reconfigure my formula so that if A2 is EXACTLY equal to any cell in the range B2 to B5, display a 1 in cell C2, else display a 0 in cell C2. Am I close with these formulas??? =SUM(EXACT(A2,"*"&\$B\$2:\$B\$5&"*"),1,0) =IF(EXACT(A2,"*"&\$B\$2:\$B\$5&"*"),1,0) ...

What is the formula payment for \$48,800 for 30 years at 8% Interes
Please some give me the formular 48,400 8 % 30 years =SUM(48,800*8/12) ??? The payment is 358.08 but how can figure it I need the correct formula to get the monthly payment? I have a computer class THIS IS URGENT!!!!!!!!!!!!!!!!!! "Help I need the answer asap!!!" <Help I need the answer asap!!!@discussions.microsoft.com> wrote: > THIS IS URGENT!!!!!!!!!!!!!!!!!! You are more likely to get the answer you need quickly if you take the time to form your question clearly and without typos. > 48,400 > 8 % > 30 years > =SUM(48,800*8/12) ??? > The pa...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

Delete record(s) in other cells based on value of one cell.
What I have is a protected sheet, where all columns except for 3 are protected. I cannot delete the entire row with protection turned on. So, what I need is to be able to delete the value in these three columns, only when the value in column A is "d". Any clues? Protection must remain on, users are using this spreadsheet and I cannot allow them to have access to the formulas in these other columns. Help...Micheal Provide a macro that unprotects the worksheet, does the work and reprotects the worksheet is one way around the problem. MPope wrote: > > What I have is a ...

newbie trouble with formula
Hi all..could you please help me with the following formula? =if(weekday(B2-1)=1,B2-3,B2-1) B2 is 7/31/2008. I'm very new at excel, please forgive my lack of knowledge. Thanks! -- jenn k There's nothing wrong with your formula so you'll need to explain the logic of what you want to do. Here's what your formula is saying: If B2-1 = Sunday, subtract 3 days from B2 otherwise subtract 1 day from B2. Is that what you want it to do? -- Biff Microsoft Excel MVP "jenn k" <jennk@discussions.microsoft.com> wrote in message news:BE60D6AD-D058-4631-BC33-3522EF...

Creating a batch file for Inventory Adjustments
I'd like to import the results of our physical inventory into an Inventory Adjustment batch. Can someone explain how to do this? The batch will consist of approx 1,000 records. Are you using a stock count schedule? if so, there is no way to do this directly. You could directly import the data into the SQL table - Ask your partner for assistance in this if you are not a DBA. HS "MrMark" <MrMark@discussions.microsoft.com> wrote in message news:B88E26CD-2503-4844-9AA9-94003208556A@microsoft.com... > I'd like to import the results of our physical inventory into...

Breaking Formulas using Pivot Table
I have Tab1 that has data in it in the form of a pivot table. I then have another sheet, Tab2, that pulls data from Tab1. This all works, but when I try to copy Tab2 into a new workbook (so that I can e-mail only that tab) a bunch of my formulas in Tab2 fail. If I refresh the pivot tables they work again or if I do a Replace "=" with "=" it seems to trick it into working (but some percent formatting changes to dollar formatting for some reason). I just want to know if there is an easy way to make the new workbook without breaking the formulas in my old one. Thanks in adv...

Copy / Paste Merged Cells
Using Excel 2000, is is possible to copy and paste a range which has merged cells such that the pasted data displays only values. When using paste special and the "formula" option, merged cells will paste into the destination without problems but when using paste special with the "value" option, merged cells will not paste. Is there a way to do this easily and if so, what is it? Thx. Maybe you can copy from the formula bar and paste into the formula bar??? LPS wrote: > > Using Excel 2000, is is possible to copy and paste a range > which has merged cells...

create an update query
How do I update the disount field for all customers who do not already have a discount? On Sun, 11 Apr 2010 17:15:01 -0700, kim <kim@discussions.microsoft.com> wrote: >How do I update the disount field for all customers who do not already have a >discount? You can see your table structure - we cannot. Guessing that there is a field in the table Customers named Discount, you can create an update query with a criterion Is Null on the discount field to find all records with blank (empty) discounts. If the discount defaults to 0 you can use a criteron =0 ...

Format Cell Problem #2
Hi, Sorry for all these dumb questions, but I never had any problems with Excel 97. This one is apparently just too complicated for me at my old age. Anyway, thanks for all the help; really appreciate it. So, using Excel 2007, and XP I select a cell that has a number in it with 5 decimal places. I go to Format Cells, and select Number, and tell it to go to 4 decimal places. It won't, and continues to show 5 decimal places. This is also true if I try it on a column of numbers. What am I not doing right ? (I am sure that what appears in the cell is truly a number, and not text.) T...

Cell fill color changes by itself
I have a user who has excel worksheets that they use fill colors. These are colors that they use all the time. Suddenly when she opens the sheets, the colors change by themselves even though no one has changed them. When another user opens the same document, they are the correct colors. It seems random as sometimes the document shows the correct fill colors. ...

Cell Phone/Laptop Client/CRM User Sync Architectural Question...
First let me apologize for this question. I am sure that there is something very simple and obvious that I am missing, but I am told that the only dumb question is the unasked one (said with tongue deeply in cheek!).... Current Situation: Small 25 person company Everyone has account on SBS server; i.e., active directory account All have CRM accounts (CRM V3 with all upgrades) Most have laptops with Outlook 2007/CRM client (with all upgrades) Most have Windows Mobile x (5 or 6) cell phones that sync with Exchange We Want: All of us want to be able to have contact info in our phones for each...

Finding max array value of variable cell range
Hi All, I'm currently stuck in trying to find out to get a max array value from a variable cell range, the data is divided in 5 collums, and the variable cell range should be dependent upon the first column. the maximum value should be available from column c to e. the first colums has blanks in between of variable spaces. I need to get the maximal amount of the array based upon the id code in first column (including the row of the id code, excluding the row of the second id code)... example: A B C D E 12341 data data data etc... 12341 42343 23432 etc.... does ...