how do I convert text string into a cell reference

I have a spreadsheet with multiple pages (a summary sheet, plus multiple 
single sheets with common format data for different products). In the summary 
sheet, I want to keep the cell reference the same, but change the page 
reference according to the column that the data is in. That way I can change 
a cell at the top of the column to pull up the right data.  I can create the 
cell reference OK in text form using Concatenate , but cannot see how to 
convert the resultant text string to get back to the real data. Any ideas ??
0
Davis (19)
7/21/2005 4:45:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
706 Views

Similar Articles

[PageSpeed] 11

You can use the Indirect function to return a reference. For example, if 
cell C1 contains a sheet name, the following formula will return the 
value in cell D5 on that sheet:

   =INDIRECT("'"&C1&"'!D5")

Dave Davis wrote:
> I have a spreadsheet with multiple pages (a summary sheet, plus multiple 
> single sheets with common format data for different products). In the summary 
> sheet, I want to keep the cell reference the same, but change the page 
> reference according to the column that the data is in. That way I can change 
> a cell at the top of the column to pull up the right data.  I can create the 
> cell reference OK in text form using Concatenate , but cannot see how to 
> convert the resultant text string to get back to the real data. Any ideas ??


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
7/22/2005 1:05:43 AM
Many Thanks Debra, this works well. I had tried INDIRECT but obviously could 
not quite get the syntax right. Thanks for your help,

"Debra Dalgleish" wrote:

> You can use the Indirect function to return a reference. For example, if 
> cell C1 contains a sheet name, the following formula will return the 
> value in cell D5 on that sheet:
> 
>    =INDIRECT("'"&C1&"'!D5")
> 
> Dave Davis wrote:
> > I have a spreadsheet with multiple pages (a summary sheet, plus multiple 
> > single sheets with common format data for different products). In the summary 
> > sheet, I want to keep the cell reference the same, but change the page 
> > reference according to the column that the data is in. That way I can change 
> > a cell at the top of the column to pull up the right data.  I can create the 
> > cell reference OK in text form using Concatenate , but cannot see how to 
> > convert the resultant text string to get back to the real data. Any ideas ??
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
DaveDavis (1)
7/22/2005 8:20:01 AM
Reply:

Similar Artilces:

Can somebody help me? convert vba code to function
Pls. Do you know how to convert vba code function, into a query function to use in a text field in a a report? Thenks in advance. br Nino Pls. Do you know how to convert vba code function, into a query function to use in a text field in a report? Thanks in advance. br Nino Nino wrote: > Pls. Do you know how to convert vba code function, into a query > function to use in a text field in a report? Generally speaking any public VBA function can be used in queries or TerxtBox ControlSources (even cusotm ones). -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... R...

Convert from Paradox database?
Is there a way to convert data from a Paradox database to an Access database? .. See the recent thread in this newsgroup under the subject Conversion from Paradox to Access The first message was dated 19 March. On Tue, 28 Mar 2006 09:38:56 -0700, az-willie <sclause@npole.com> wrote: >Is there a way to convert data from a Paradox database to an Access >database? > >. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. ...

How to convert #N/A to 0 using Match?
Hi - - Here is my formula. What changes do I need to make to return a "0" instead of "#N/A"? =INDEX('Oct 09 Discrepancy'!$A$1:$C$875, MATCH("0120 Count",'Oct 09 Discrepancy'!$A$1:$A$875,), MATCH("Counts",'Oct 09 Discrepancy'!$A$1:$C$1,)) Try testing your formula with ISNA() =IF(ISNA(your_formula), 0, your_formula) If you are using Excel 2007 try =IFERROR(your_formula,0) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Wildwood" <Wildwood@discussions.micro...

Cell Entry Display Issue
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel There is a problem when I set preferences to deselect &quot;Double-click allows editing directly in the cell&quot;. <br><br>When I type a new entry in a cell it at only appears in the formula bar and the cell looks blank. I am now noticing that if I do a formula entry that refers to another cell, it does partially display the top have of the formula I am entering in the cell approximately a few rows below my entry cell, sort of floating over the spreadsheet, not actually in a cell...(a font issue ma...

circular reference problem
Hi all I am new to .NET and webservice so I wish someone can shed some light on me. I have a Project class and a Product class, the Project can contain multiple Products (as an ArrayList). In my WebMethod I Initialize my project as following: <WebMethod()> Public Function ProjectInitial() As Project 'Public Function ProjectInitial(ByVal inputProduct As Product) As Project Dim myProject As New Project myProject = InitialProject("Test Project") Dim myProduct As New Product myProduct.cost = 100 myProduct.masterQty = 1000 ...

Change cell in formula based on date
I would like to have a formula that draws from different cells depending on the current date. For instance, on the first of the month, it will draw from A1, and on the second it will draw from A2, etc. I just experimented with this and it worked (note: I used column b instead of a) =INDIRECT("b"&DAY(NOW())) "Skillet" wrote: > I would like to have a formula that draws from different cells depending on > the current date. For instance, on the first of the month, it will draw from > A1, and on the second it will draw from A2, etc. One way: =INDEX...

Convert AOL Email and Contacts
I am trying to help someone convert from AOL (finally!). We tried to use the Intellisync program with AOL that would sync the contacts with Outlook, but it didn't map fields correctly, so that turned out to be a mess. I couldn't find anything to convert the email files. Is there any kind of program or a way to use Access or other to convert contacts? Is there any solution for moving the email to Outlook? Thanks! try exporting to excel file format and then import, mapping the fields correctly. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours C...

Center Text
Hello all, I have what would seem to be a very small problem, but I am unable to answer it myself. I have a report that I am trying to create and each record can have one of three categories. If there are multiple records for, let's say, Group1, then I want a text box to span both records and center the text within the report. This would be the equivalent of a rowspan in html. I have no idea where to start. Am I missing something really simple? The data is structured as the following: Group1 | Record 1 | Some cool info Group1 | Record 2 | Some cool info Group 2 | R...

How do I convert a line chart to a vertical line chart in Excel
data: yr_2007 yr_2008 yr_2009 spring 20 75 35 summer 60 25 80 fall 55 40 100 x : year y: value of count want convet to: x: value of count y: year x If I understand you correctly, a XY Scatter chart would be the more appropriate chart. Even though it and the Line chart look similar, the former offers a numeric x axis. On Wed, 19 Aug 2009 20:09:01 -0700, Gerty <Gerty@discussions.microsoft.com> wrote: > data: > yr_2007 yr_2008 yr_2009 >spring 20 75 35 >summer 60 25 80 >fall 55 40 100 > >x : year ...

Convert
Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...

Opening Workbook to a specific cell
I share a workbook with other users. We would like the workbook to always open with the cursor at the last edited cell for all users. How can we set a workbook up so that no matter who opens it, the cursor is always on the last edited cell? Hi this would require VBA (using an event procedure and somethere storing this last cell information). I'm sure this will get complicated in a shared workbook so I wouldn't do this honestly -- Regards Frank Kabel Frankfurt, Germany Steve Arrants wrote: > I share a workbook with other users. We would like the workbook to > always open with ...

Problem converting from Money 99 to Money 2004
I purchased a new computer with Money 2004 pre-installed. I've tried several times to open my backup file from Money 99 in 2004 and the program just freezes up & I get the "Prgram Not Responding" message. I've tried to re- install Money 2004 with no luck. I'm willing to put Money 99 on my new computer so I don't have to go back & use my old computer, but that came pre- installed. Any idea where I could get a disk with Money 99 or how to fix the problem converting to 2004? Please help! In microsoft.public.money, <allenj868@yahoo.com> wrote: ...

Publisher 07
I'm trying to create a cover page for a booklet and have Text Boxes that I would like to rotate around a chip images. I see the option to flip 90 degrees, but is there a way to just rotate the box a bit 45 degree, etc. Thanks ****************************************************** Charliec Right-click the text box, format text box, size tab. There is a rotation option in this dialogue. -- Mary Sauer http://msauer.mvps.org/ "Charliec" <charliec@invalid.address.com> wrote in message news:3b94b4t3a4oquvheiap777dtkhe0pba5c8@4ax.com... > I'm trying to create a cove...

Altering read/write facility of individual cells
I would like to make a cell unwritable (i.e. a user cannot input any data into it) until spefic line of text is entered into the cell above it, is this possible in Excek, and is fo how. Many Thanks Nick Nick Read wrote: > I would like to make a cell unwritable (i.e. a user > cannot input any data into it) until spefic line of text > is entered into the cell above it, is this possible in > Excek, and is fo how. > > Many Thanks > > Nick Not natively AFAIK - you'll have to write a VB script I would think..... -- Registered Linux User no 240308 Just waiting...

Spilting 1 cells contents into 5 columns over an entry of 1 to five characters
Can you guide me please I have one cell that will hve anything up to a 5 digit number in this, I what to place the respective digit in to separate columns ie. If I enter ABCDE in the receiving cell, I use the MID(Cell,3,1) which will return me the C A | B | C | D | E | F | 1 |ABCDE| A | B | C | D | E | Which is acheived with Cell B1 =MID($A$1,1,1) Cell C1 =MID($A$1,2,1) Cell D1 =MID($A$1,3,1) Cell E1 =MID($A$1,4,1) Cell F1 =MID($A$1,5,1) Gets me the result I want when the ...

Convert SqlDataSource to SqlConnection
Hi All, From [1], I was not able to populate a <asp:Label> solely from the body using a <asp:SqlDataSource> (it required VB code in the page_load). This caused me grief since I want a clean solution that does work one way(all <asp:...>) or another (VB in page_load). I'm now attempting to perform everything using VB in page_load. I copied the Data Source property from the SqlDataSource to map it to a SqlConnection (line breaks added for readability): Data Source=.\SQLEXPRESS;AttachDbFilename= |DataDirectory|\Products.mdf; Integrated Secu...

CDialog caption text size
I'm sure this must be easy, but for some reason I can't figure it... I want to change the titlebar (caption) of a dialog depending on the current system font size used. E.g., in XP you can use standard old style Windows fonts, or chunky XP fonts. The text clearly takes up a different amount of room depending on which is used. How do you tell? I've tried: CString titleStr = _T("Whatever 123"); CRect rect; GetClientRect(&rect); CDC* pDC = GetWindowDC(); CFont* pOldFont = pDC->SelectObject(GetFont()); CSize size = pDC->GetOutputTex...

Converting files
How can I convert Money 2003 files to Quicken "QIF" files In microsoft.public.money, barry wrote: >How can I convert Money 2003 files to Quicken "QIF" files File->Export Depends on the version. For M03 -> Q04 there is a data conversion utility that you can find on quicken.com It doesn't do everything, but is helpful if you want to switch. B. "barry" <anonymous@discussions.microsoft.com> wrote in message news:078601c3d964$fac3f890$a101280a@phx.gbl... > How can I convert Money 2003 files to Quicken "QIF" files Of course, that ...

Format Cell contents as I.P. address
Is there a way to force the contents of a cell to conform to the format of an I.P. address (e.g. nnn.nnn.nnn.nnn) ? I looked in the tutorial format cell and found custom but no useful tutorial for how to do it. Thanks -- Lionel B. Dyck <>< AIM ID: lbdyck Yahoo IM: lbdyck Homepage http://www.lbdsoftware.com/ Blog: http://randommgmt.blogspot.com/ Custom ###"."###"."###"."### i.e. 123456789123 will be formatted as 123.456.789.123 Just as long as you realize that the number is still the original number you entered. Gord Dibben MS Excel MVP On ...

Copying Info Into Numerous Cells
I have just completed creating and entering data into thousands of worksheets. Now someone has decided they want to change and add columns to my worksheets. Is there any way to have data copied from 1 cell to another in several worksheets at a time, even if the data is different? If there isn't...I'm going to have a melt-down right here at work! :eek: -- calimari ------------------------------------------------------------------------ calimari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24537 View this thread: http://www.excelforum.com/showthr...

Convert
I am trying to get the 'CONVERT' function on MS Excell 2000 to work. I wish to convert metres to miles. I enabled the Analysis ToolPak by going to TOOLS/ADD INS and put a tick in the Analysis ToolPak box. in a cell I typed CONVERT(100, "m", 'mi") but it didn't work. I also entered 100 in cell A5 and then tried CONVERT(A5), "m", 'mi")) with no result. Could someone please put me on the right track?. Keith Partridge ...

Text To Dates
I have trouble with Excel converting text to dates. For example: I set the row to text and enter Jan 02, Feb 02 ... Dec 02; I copy the entries Jan 02 through Dec 02 and paste them in the same row (Which I previously set to text); I replace the 02 with 03 expecting to have Jan 03, Feb 03 ... Dec 03; However, Excel, in its infinite wisdom, converts my new items to 3 Jan 04, 3 Feb 04 ... 3 Dec 04. How can I get Excel to accept my change and keep the fields as Jan 03 etc. An easy fix is to use the month fill, but I have had similar problems in the past and wondered if what I can do to fix...

What program can i use to convert extentions jpg to csv. ensofort.
How can i convert extentions like jpg and gif to a different extention like csv. Can you do this in Excel? You have a picture of text and you want to import it into excel? If yes, you need an OCR program (Optical Character Recognition). A lot of scanners come with software, or you can purchase it separately. There may even be free/shareware versions that you can download. http://www.shareware.com might be your next stop. John wrote: > > How can i convert extentions like jpg and gif to a different extention like > csv. > Can you do this in Excel? -- Dave Peterson ec3572...

Get part of string
I have an asp.net application that dumps out a GridView from a datasource. I want to access the controls on the rows via Javascript at the client rather than use Ajax. For example I have a control id below as example of a table row: gvChecks_ctl02_txtClearedAmount I am using js below but it only gives me text up to the first underscore. I want to be able to dig out everything between the 2 underscores. In above example I need to return 'ctl02'. Can someon help? Thanks. var sid = obj.id; var unloc = sid.indexOf('_', 0); var vrowid = sid.substr(0, unloc);...

Cell drag versus item drag in clistctrl
Hi there, I trap LVN_BEGINDRAG to get the event when the user tries to drag an item from my listctrl. However, this only traps if the user actually clicks on the item text and starts to drag it, not if he/she simply start to drag a cell. Is there a way to trap the event when the user starts to dray that cell, even if the mouse starting drag position was not over the item's text? Thank you. Yes but it involves you doing the drag and drop process yourself. With that said there are two ways you can do that. One is to use OLE (CDataSource, and CDropTarget) which comes in handy if you wa...