#### Convert text string in format of cell reference to a cell reference

Is there any way of converting a text string in the format of a valid cell reference i.e. " 'Sheet1'!F23 " to an actaul cell reference that could be used in a formula?

 0
anonymous (74722)
12/16/2003 8:46:05 AM
excel.misc 78881 articles. 5 followers.

1 Replies
311 Views

Similar Articles

[PageSpeed] 55

Frank

You could use the INDIRECT() function. This will use the value in the cell
as a cell reference. So if Merges!A1 was in B2, INDIRECT(B2) would return
the value in Merges!A1

Andy.

"Frank" <anonymous@discussions.microsoft.com> wrote in message
news:3EAAD6E9-C337-4BF6-B721-A7C75D5D99EA@microsoft.com...
> Is there any way of converting a text string in the format of a valid cell
reference i.e. " 'Sheet1'!F23 " to an actaul cell reference that could be
used in a formula?


 0
andy1646 (82)
12/16/2003 8:52:15 AM
 Reply:

Similar Artilces:

Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...

Outlook 2003
Is there a way to force all incoming email to be received as plain text in OL-2003? TIA -- howard How about: "Tools | Options | E-Mail Options | Read all standard mail in plain text"? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/computers/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Howard Cross" <no-spam@No-Spam.com> wrote in message ...

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

Convert single colum/multiple rows to multiple colums.
Hi, I have a .dat file when opened with Excel it has 1 column and 7 rows per entry. I would like to delete some rows and convert the rest to something like. Any chance this can be done. It's quite large. 51,793 rows. TIA Jeff Col 1 Col 2 Col 3 Name Date Lenny Kravitz - 2000 - Greatest Hits -- Table: {2} { "music" "Name", "06/04/2008", "Lenny Kravitz - 2000 - Greatest Hits", }, --Table: {3} etc. etc. ...

To write living will do I need special format
I just need to change and update a living trust You should consult someone with the appropriate legal knowledge in the jurisdiction in which you are domiciled. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "belladonna" <belladonna@discussions.microsoft.com> wrote in message news:F25A64CB-355F-44E9-A005-16AA61DA15A7@microsoft.com... > I just need to change and update a living trust ...

Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...

How do I set the number format to Base 12?
I would like to change the number format on my spreadsheet from Base 10 to Base 12, eg. 12 bottles makes up 1 case. Therefore, if I were adding up three different cells 9 bottles + 11 bottles + 6 bottles, my result should be 2 cases 2 bottles if possible 2.2 in a case column. See http://www.cpearson.com/excel/fractional.htm for details. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Andrew Moore" <AndrewMoore@discussions.microsoft.com> wrote in message news:893CABE9-37D7-4E6B-8A7E-A5E679C8C824@microsoft.com... >...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

How to use structured reference (ListColumns) in VBA
I want to do something simple...loop through a range retrieving values one at time and taking action on that value. This range is an Excel 2007 table column. Easy enough...BUT I want to use structured references to a table column by NAME, not numbers. I don't want to define new names, just use the table column headings. I've found ways of doing it with quotes, but that's not structured....the names in quotes do not change if the table heading is changed by the user. The formulas change if the table column heading is changed, but I want it to change in VBA to...

Save As using contents of Cell in Name
Can someone help with code that changes the "save As" file name to combination of the original File Name + the contents of a cell, an saves file in same folder as the original. E.G. Original File Name= Timesheet Cells B1= Location Cell C1 = Date in format 3-5-2004 Cell A1 = concatenate(B1,C1) Fle Name will therefore be "Timesheet Location 3-5-2004" The other problem is with the date. When concatenating, how do I forma the date to avoid it looking like "Location 38051" Any help much appreciated Nic -- Message posted from http://www.ExcelForum.com Hi for c...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

Conditional Formatting w/ a List/Icons
I am trying to allow someone to select "Green", "Yellow" or "Red" from a list and the cell to display a green/yellow/red icon appropriately. Or, if possible, the user could just select the icon (instead of selecting text). Is this possible? Use Data Validation for the list. Type in Red, Yellow, Green as the list. This give the user the list to select from. Use Conditional Formatting for the fill part. Set three conditions, If Cell Value-"Green" (select a green fill), etc.. -- If this helps, please remember to click yes. "...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

Batch converting leads to accounts
Is there a way to convert leads to accounts in batches? I need to convert up to 500 each day and don't have the time to go through them one at a time. Dave Lagergren On Sat, 26 Feb 2005 01:28:44 GMT, "Who Cares!" <whocares@freetidet.org> wrote: >Is there a way to convert leads to accounts in batches? I need to >convert up to 500 each day and don't have the time to go through them >one at a time. Only way is through the SDK. Write some code that loops through your leads and creates accounts, contacts and opportunities as needed and marks the lead as qu...

VISIO 2007 -Text direction
can some one tell me how to change text to be type in vertically. Under tools, options there is no regional tab or under format text the change text direction command does not work. "kgbrat" <kgbrat@discussions.microsoft.com> wrote in message news:2DBF18B5-E1C8-4493-8BEF-F7D4C1538781@microsoft.com... > can some one tell me how to change text to be type in vertically. Under > tools, options there is no regional tab or under format text the change > text > direction command does not work. You can use the Text Tool (The A with an circular arrow around it) and gr...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

conditional format 04-15-10
Hi, I want the color of the text in cell B1 to change depending on the value in cell A1 How can this be done? I know I can do it with conditionale format for the cell itself but not for another one. Thanks JP Try this. Assume you want B1 text to change colour when the value in A1 is 5, select B1 and go to conditional formatting. In the condition, select 'Formula is' then in the empty space, type =A1=5 choose the font colour that you want. "Jean-Paul" wrote: > Hi, > > I want the color of the text in cell B1 to change depending on th...

Special format
How can I change the format from numerical to English?? e.g.: 420 to Four Hundred and Twenty Only It is called typing - UNBELIEVABLE or SEARCH and REPLACE but MS Office 2002 Chin support numerical to chinese: 123456789 -----> = &#19968;&#20740;&#20108;&#21315;&#19977;&#30334;&#22235;&#21313;&#20116;&= #33836;&#20845;&#21315;&#19971;&#30334;&#20843;&#21313;&#20061; =3D.=3D" Can anyone help~~ >-----Original Message----- >It is called typing - UNBELIEVABLE > >or SEARCH and REPLACE > > >. > ...

reply formatting in Outlook 2010
I set up my normal.email.dotm file to have 12 pts below each paragraph, which helps formatting issues when sending emails to people using gmail and other programs that interpret paragraph breaks differently than Outlook. That works just fine with new emails. However, when I reply to an email, even an html email, the formatting is not applied. I can fix this by choosing Format Text and then setting the spacing as per above, but this is a pain in the neck. Why don't reply emails use the same template? Is there a way to template replies so i can use the same formatting? thanks in advance, ...

Concatenating cells but excluding blanks
Hello, I am trying to create a result field, concatenating populated cells from the previous 12 columns on that line, but excluding blank cells and putting a * delimiting character between each instance - please find below a 4 column example. ID 1 2 3 4 Result Z A C D A*C*D Y B C B*C X A B D A*B*D Each of the 10,000 lines of the spreadsheet is different - there are at least 5 blank cells on each line Any help gratefully received. I am working in Excel 2007 Many thanks. Bob Try this: http://img690.imageshack.us/img690/5826/nonamee.png Micky "Bob Fr...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...

Bypass Circular Reference?
I track employee training hours on an individual basis. I have one cel that is dedicated to their total hours. As of now, if an employee ha had a training session, say .25 hours, I manually add the .25 to thei total. Let's say their total before the session was 25.50 hours. The an employee receives new training of .25 hours, so, obviously, thei new total will be 25.75 hours. If an employee has had training, I hav a column that I log an "X" in to indicate they have participate. Thi "X" is transposed to another sheet to provide an overview of ou companies training. Here ...

Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...

Exchange 2K: Controlling incoming email formats....
Question... AFAIK (and correct me if I am wrong), Exchange natively only lets you control message formats for messages that are downloaded to POP\IMAP clients. For security reasons, I would like Exchange to convert all incoming\outgoing email from\to external domains, as well as INTRA-domain email , to plain text format. In other words, I do not want to see anymore html email on my server going in and out, just plain text. Is there a setting for this for Exchange to handle this natively? If not, what (if any) 3rd party solutions are out there for this? Thanks, George Here's the Re...

Text in column causing SUMPRODUCT error
Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that pa...