Referencing using data in a cell?

I have a couple of problems. Firstly, is there a way to make the data i
a cell equal to the name of a worksheet. For example, if a worksheet (i
the case of the project in question) is called "43" how would I go abou
making a cell contain the number 43, or whatever the name of that shee
is. Not sure if this can be done, but its worth a try.

Secondly how can I refer to a cell on another sheet, but where the nam
of that sheet is kept in a cell. For example, in the same case again, 
want data from a cell on sheet '42' cell 'AE6', so to make a cell i
the other sheet equal that, all I need to do is type ='42'!AE6 which i
easy enough. However, I want it to take the sheet name of '42' from th
value in a cell. So if the value in that cell is changed to '41' the
it will take data from sheet '41', cell 'AE6'.

Fairly complicated and I'm sorry if I'm not clear. It all comes down t
making things as automated as possible as people with less skill woul
be using this workbook. If I can help it, I would really like t
refrain from using VBA for simplicity and mainaintability reasons.

Thanks

--
altitude2
-----------------------------------------------------------------------
altitude2k's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1506
View this thread: http://www.excelforum.com/showthread.php?threadid=26685

0
10/6/2004 2:20:09 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
465 Views

Similar Articles

[PageSpeed] 50

Hi
#1: See:
http://www.xldynamic.com/source/xld.xlFAQ0002.html

#2: Use INDIRECT. e.g.
=INDIRECT("'" & A1 & "'!AE6")

--
Regards
Frank Kabel
Frankfurt, Germany

"altitude2k" <altitude2k.1dplbz@excelforum-nospam.com> schrieb im
Newsbeitrag news:altitude2k.1dplbz@excelforum-nospam.com...
>
> I have a couple of problems. Firstly, is there a way to make the data
in
> a cell equal to the name of a worksheet. For example, if a worksheet
(in
> the case of the project in question) is called "43" how would I go
about
> making a cell contain the number 43, or whatever the name of that
sheet
> is. Not sure if this can be done, but its worth a try.
>
> Secondly how can I refer to a cell on another sheet, but where the
name
> of that sheet is kept in a cell. For example, in the same case again,
I
> want data from a cell on sheet '42' cell 'AE6', so to make a cell in
> the other sheet equal that, all I need to do is type ='42'!AE6 which
is
> easy enough. However, I want it to take the sheet name of '42' from
the
> value in a cell. So if the value in that cell is changed to '41' then
> it will take data from sheet '41', cell 'AE6'.
>
> Fairly complicated and I'm sorry if I'm not clear. It all comes down
to
> making things as automated as possible as people with less skill
would
> be using this workbook. If I can help it, I would really like to
> refrain from using VBA for simplicity and mainaintability reasons.
>
> Thanks.
>
>
> --
> altitude2k
> ---------------------------------------------------------------------
---
> altitude2k's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=15061
> View this thread:
http://www.excelforum.com/showthread.php?threadid=266857
>

0
frank.kabel (11126)
10/6/2004 2:55:16 PM
Reply:

Similar Artilces:

Remote data access
As a new .net developer, I would like to know how a VB.net Windows application can access a SQL Server database residing on a web server. In other words, using the Visual Studio IDE, is there a way to develop a Visual Basic, Windows application that can access a SQL Server database over the internet. Thanks for suggestions, John C. John C. wrote: > As a new .net developer, I would like to know how a > VB.net Windows application can access a SQL Server > database residing on a web server. > > In other words, using the Visual Studio IDE, is there a > way to develop...

Data Validation #12
Hi. I have data validations in sheet1 as named range lists from sheet2. When I have deleted sheet2 and copy sheet2 from identical other workbook - my validations does is not work... I see Name ranges in copied worksheet, but when I use they in validation - I have error: "The Source currently evaluates an error. Do you wish continue?" Only after deleting of Named ranges in Sheet2 and recreating its, my validations works. It is possible after replacing of worksheet with named ranges for validations (named ranges names are identical) use copied worksheet without recreating ...

How can I insert a cell reference in a footer (eg for variable foo #2
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = .Range("A1").Text End With End Sub This code should go in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "wngg001" <wngg001@discussions.microsoft.com> wrote in message news:8A0F9D9E-269F-45CF-A6E3...

Data Entry Form 03-20-08
Hi, I created a form to use for viewing currently records and entering new data. This time, the form doesn't allow me to enter new data. In fact, all text boxes didn't let me to enter new information. Please help Thanks Chi Several things to check: In form properties: Allow Additions = Yes Allow Edits = Yes If the form's record source is a query, be sure the query is updatable. -- Dave Hargis, Microsoft Access MVP "Chi" wrote: > Hi, > > I created a form to use for viewing currently records and entering new data. > > This time, the ...

VBA
I have created code that inserts lines into a financial statement but need to have certain cells in other code stay constant. B1 in certain code must stay at B1 regardless of changes to the spreadsheet. Any suggestions?? --- Message posted from http://www.ExcelForum.com/ Use the INDIRECT function. E.g., =INDIRECT("B1") The B1 will remain so regardless of inserting and deleting rows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "bforster1 >" <<bforster1.1cmxzp@excelforum-nospam.com> wrote in m...

Using Windows fax
I have used the Windows fax console for receiving faxes for a while. I had Photoshop installed when I started using the fax system in Windows. I could VIEW and it would open Photoshop automatically. I deleted Photoshop and now have Photoshop Essentials 8. I now cannot VIEW anything because I don't have software to view .tif . I know that Essentials opens it, but I can't find anywhere in the fax console to tell it to go to Essentials. Please help. Chip wrote: > I have used the Windows fax console for receiving faxes for a while. I had > Photoshop ...

Distribute an add-in using a URL
Hi! I'm trying to get an .xla file to load up in the Add-ins from a URL. I receive the following message when I try to add the URL link.."You cannot use an Internet address here. Enter a path that points to a location on your computer or on the network." ...

Sorting lots of data
We are trying to sort through a csv file that we converted to an excel spreadsheet. the spreadsheet has over 40,000 entries which include mailing addresses that are in some cases duplicated. is there anyway to sort/filter the info to locate the duplicates and then remove them?? any help would be greatly appreciated. thank you for your responses. Hi use 'Data - Advanced Filters' and check 'unique entries' or use the following addin for this: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany miss lynar wrote: > We are trying to sort thr...

Stacking Data??
if I have data like this A B C D... 1 Type\Date 01/10/04 02/10/04 03/10/04... 2 T-X 100 50 100 3 T-Y 50 70 0 4 T-Z 400 0 400 ... .. ...

I have the template i want to use, however I am not sure how to us
an e-mail was sent to me with a spreadsheet I'm to complete. A co -worker helped me to create a template of that spreadsheet that I need to fill in but I don't have experience using templates. How do I get started, and how do I create a next page or sheet? I need to fill it in and than start the next page with the blank template...help Have you looked at the Excel help for templates? If so, at which stage did you have a problem? -- David Biddulph "mouse" <mouse@discussions.microsoft.com> wrote in message news:C9209715-1DD7-4771-B23F-03BB8F916A10@microsoft.com...

Trouble using resolution for Outlook error
I am recieving the error in Outlook mentioned on the following page: http://support.microsoft.com/default.aspx?scid=kb;en-us;822503 The instructions say to resolve the issue for an existing installation, specify the *.pst file by using the Mail icon in Control Panel. First of all, I do not know what *.pst file they are referring to. The following directory on my machine: C:\Documents and Settings\Nathan Sokalski\Local Settings\Application Data\Microsoft\Outlook Contains the following *.pst files: archive.pst njsokalski@hotmail.comHotmail-00000002.pst Outlook1.pst Outlook.pst I am ver...

order data by a rank
i need to create a form order by the militaty rank.This form is based in a query "qrystaff" with three fields: MemberID FullName and Rank.I´d like that the data were ordered firstly by MilitaryRank and secondly by FullName in alphabetical order.There is an easy way to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 When you create queries, you're given the option to specify the sort order under each field. If the fields aren't in the order from left-to-right in the query grid, you can add ...

Turn data #2
Is there a way to turn the data in an Excel spreadsheet so that what is on the top is now on the side and what is on the side is now on top? I need to export my spreadsheet into Access but, I can't get it to format correctly in its current orientation. ~Make a backup copy of your data to work from (just in case, so you don't lose data) ~Highlight the entire range of data; click >Edit >Copy ~Choose a new location, on a different sheet perhaps; click >Edit >Paste Special and select the Transpose box; click OK Does that do it for you? thank you so much. You are a genius...

how do i create a quiz using excel?
hey guys...i badly need to make a quiz using excel,,,i got no clue how to do it. the quiz will be a simple one, no drop downs and should display the scores after the quiz is taken.... please help! Andruu, What type of quiz are you looking to create? What format are the answers? Numbers? Text? One suggestion..... 1. Add questions *What is 2 x 2?* 2. Assign cells for the user to insert their answers. (format cells as text/numbers etc) *Cell: B2* 3. Either in hidden cells, or on a seperate sheet, put the correct answers 4. Assign a point value for each correct answer (can be different for ...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

Is there a work-a-round i can use for making a datagrid....
Hey all, I am trying to make a datagrid on a form that the user would input info, and once completed, it would be put in correct area's of the workbook. I cant use a datagrid as job doesn't alw downloads for net so i cant get the update needed to alw me to use datagrids( see post sugj: "Missing Control In Addin") is there a way i can use a text box or some other box or tool to do this? When you say "form", do you mean UserForm or worksheet set up to look like a data input form? If a UserForm, then yes, you can use TextBoxes, but you didn't supp...

Display of CRM data in SharePoint
Is is possible to allow non-CRM users the ability to view CRM data on SharePoint without using the CRM external connector? Thanks, -Rick M. Microsoft would require you to have a license per user I think ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Rick M" <RickM@discussions.microsoft.com> wrote in message news:B3E731A1-D059-4D49-B879-6025A1817FD8@microsoft.com... > Is is possible to allow non-CRM users the ability to view CRM data on > SharePoint without using the CRM external connector? > > Thanks, > -Rick...

Cell QA for Bob Phillips
Good afternoon Bob: In reference to your QA: (Which sheet was the active sheet when you right-clicked on the tab? It should have been Data Sheet). Yes, I did as you said, but I am not sure I placed the text string in the right place. I pasted it in View Code on the blank area to the right of "Project / Properties" section but for some reason it is not moving the text from "Data Sheet" to the "Activity Sheet". Is it possible to communicate with you directly so I can get this worked out? Thanks John ---------------------------------------------------...

Outlook Data File Not Closing Properly
I always get the following error message when starting Outlook 2002 (running Office XP and Windows XP) : "The data file 'outlook' was not closed properly. The file is being checked for problems." I get this message everytime (whether I hibernate or shut down) when I start Outlook. This checking of the file can take anywhere from 30 seconds to 5 minutes and is very frustrating. Anyone have any idea what the issue is ? This started happening just after I installed XP SP3. ...

using a macro question revisited
Concerned that the thread was bypassed after going 4 deep, I have reposted it with full includes. The way the macro works as currently written, it can return to multiple cells; but in this instance, I just need it to return either the sum of 2 randomly generated numbers between 1 and 6 (simulating a dice roll), or a single randomly generated number between 1 and 6, and then have the cell be stable after that is done (not constantly recalculating with every chnage made to the spreadsheet). "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message news:uBokaQVAGHA.2...

BP Installation Error
Someone post this question before:- MbfPackager data importation process has failed. please c:\DOCUME~1\ADMIN~1\LOCALS~1\Temp\MbfPackager.Import.NoMerge.Common.xml for more information. Problem: When installing Microsoft Business Portal 2.5. Can anyone help me on this error? I'm installing Business Portal 2.5 Feature Pack on Windows 2003 Standard Edition. And, the BP is reading the Great Plains 8.0 database at another server. Cheers, Emily ...

Using tild-n in e-mail
Using Win XP/OLK 2003, and NOT using Word as the editor, does anyone know how to insert the 'enya' (n with the tilde ~ over it) into an email? My users typically have default email set to HTML although some prefer plain text. Would that make a difference? Thank you, -Monica da-moe <amethystm@hotmail.com> wrote: > Using Win XP/OLK 2003, and NOT using Word as the editor, does anyone > know how to insert the 'enya' (n with the tilde ~ over it) into an > email? My users typically have default email set to HTML although > some prefer plain text. Would that mak...

how do i set up outlook using my existing internet email account?
I am trying to set up my existing internet email account with Outlook. It was previously set up, incorrectly. I have no idea what to do. On Mon, 27 Feb 2006 13:18:29 -0800, Clayton wrote: > I am trying to set up my existing internet email account with Outlook. It was > previously set up, incorrectly. I have no idea what to do. Ask your ISP. They should be able to talk you through. -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk ...

Access data
Is there a way to open an Access 97 database with Excel 97? Hi C, I have a hyperlink in my excel workbook that opens the access workbook, i use it for correction purposes. Would a hyperlink work? Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21287 View this thread: http://www.excelforum.com/showthread.php?threadid=492101 In Excel try Data>Import External Data>Import Data or New Database Query Gord Dibben Excel MVP On Fri, 9 Dec 2005 07:43:...