Use worksheet number instead of name in formulas

Good morning everybody,

I have looked through a few groups and I haven't been able to find
what I need to do, so here goes:

is there a standard function/formula that allows you to reference the
work sheet number rather than the worksheet name when referencing a
cell in a different worksheet than you are currently in?

i.e.:

instead of this: "='My worksheet name'!A1"
do this: "=sheet(1)!A1"

If there is no standard, then I will just make the VBA, but no use
doing that if it is not necessary

Tim
0
tricard (4)
5/9/2008 1:35:08 PM
excel 39879 articles. 2 followers. Follow

2 Replies
229 Views

Similar Articles

[PageSpeed] 27

Only through VBA.


Gord Dibben  MS Excel MVP

On Fri, 9 May 2008 06:35:08 -0700 (PDT), Tim Ricard <tricard@gmail.com> wrote:

>Good morning everybody,
>
>I have looked through a few groups and I haven't been able to find
>what I need to do, so here goes:
>
>is there a standard function/formula that allows you to reference the
>work sheet number rather than the worksheet name when referencing a
>cell in a different worksheet than you are currently in?
>
>i.e.:
>
>instead of this: "='My worksheet name'!A1"
>do this: "=sheet(1)!A1"
>
>If there is no standard, then I will just make the VBA, but no use
>doing that if it is not necessary
>
>Tim

0
Gord
5/9/2008 4:34:03 PM
Thanks for the help Gord. I will get right on it :).

Tim
0
tricard (4)
5/9/2008 6:12:09 PM
Reply:

Similar Artilces:

formula-percrntage
i am trying to figure out how to put a formula in that will be able to get a percentage of a number in one cell with the answer in another cell without showing the percent rate that was used to get the answer. Any help would be appreciated. Thanks Defining a name for the percentage is one method. Select any cell and Insert>Name>Define>Name qwerty Refers to 13% and OK In a cell enter =A1*qwerty Users can always snoop in to find the name if they choose. And remember.........if users can see the original number they can always reverse-engineer from the formula results. In s...

Array Formula not updating
I have a quite a large array formula: {=(INDEX('[Plant Movements Entry.xls]Data'!$E$2:$E$9997,MAX(('[Plant Movements Entry.xls]Data'!$A$2:$A$9997=A3)*ROW('[Plant Movements Entry.xls]Data'!$A$2:$A$9997))-CELL("row",'[Plant Movements Entry.xls]Data'!$A$2)+1))} for some reason it wont update properly unless the other workbook which it refers to is open, if the other book is not open it returns error values. its strange because i have used the same formula in other books with out incident can any one shed some light on why this may be? Cheers The pr...

entering new data in a saved spreadsheet without losing formulas?
How do I do this? Hi Jackie, As long as you do not overwrite the formulas, then any new data will not affect the formula. I suspect though that there is more to this question. Are you concerned about writing new data into a cell that has a formula in it? The way to stop a cell's data being overwritten is to put protection onto it. This means taking protection off all the other cells otherwise you will not be able to write data to any cell. First select the whole worksheet concerned, then go to Format\Cells and select the protection tab and unselect the 'locked' tick box,...

Listboxes are Starting at the Bottom Instead of at the Top!
Good Day, I have a question as to why all of my listboxes, when clicked, display the first blank data (after the last alphabetical text), requiring a user to scroll up instead of scroll down. How can the listbox always start with top ("A") record. The listbox is getting its values from a source column containing data that is pasted into the column with code (different filtering macros create different lists in this column). Thank you, Eric You can "re-create" your lists using an array formula, like =INDEX(A:A,SMALL(ROW($2:$1000),ROW(2:2))) and don't forget to ...

How do I name categories of a pie chart
I am creating a pie chart of numeric values. I would like to add the names of each category. I don't seem to be able to find out exactly how to do it. HELP!! Right click within the chart, select "Format Data Series..." Go to "Data Labels" tab. Select "Series Name" or "Category Name" "RGC" wrote: > I am creating a pie chart of numeric values. I would like to add the names > of each category. I don't seem to be able to find out exactly how to do it. > HELP!! ...

Delete cell contents while retaining formulas
I want to delete cell contents from cells I select without deleting the formulas. How do I accomplish this? Thanks in advance.. Hi - select your range - hit F5, click 'Special' and check 'Constants' - now hit the DEL key -- Regards Frank Kabel Frankfurt, Germany "Dave A." <cyberayk@excite.com> schrieb im Newsbeitrag news:Oo4ybUJ2EHA.1144@TK2MSFTNGP09.phx.gbl... > I want to delete cell contents from cells I select without deleting the > formulas. How do I accomplish this? > > Thanks in advance.. > > ...

How to diffrentiate Text & number from a single cell????
Hi ther guyz............i a have small problem with the execl sheets........... my problem is e.g. i have alot of cells with a number and some text in it........and they both are in one/single cell (e.g. "878 queen st" in A1)....now what i want is to remove let's say "queen st" from each cell so that there is only number left in it.....................i cannot figure out any formula........if there is any..............need help imediately -- pack_card2000 ------------------------------------------------------------------------ pack_card2000's Profile: http://www...

Help with using data
Hi I would really appreciate some help with extracting data from Outlook. I have been building up a database of clients/prospectives, these are in various categories. I would like to extract/search on various fields as well as my own categories, all i can do with export is extract the whole lot to a tab delimited file. Or export categories to an rge file which I can't use. Any ideas out there? thanks Outlook's Search doesn't suffice? There are 3rd party search applications that might help. What sprcifically stops you from exporting to a format other than TSV? There are other...

Run-time error 438
I'm trying to find out why this code is not working. With Worksheets("Multi-period Code data").ListBox1 .Clear For FillCount = 1 To Worksheets("ClassCodes").Range("E1") .AddItem Worksheets("ClassCodes").Range("C1").Offset(FillCount, 0) Next FillCount .ListIndex = Worksheets("Multi-period Code data").Range("O1").Value End With Just to clarify, there is a sheet named "Multi-period Code data" and ListBox1 exists on the sheet. The err...

Real Programmers (TM) use MSFT C# not Linux languages (sez an expert)
Don't take my word for it, see what this nationally syndicated author and computer programming guru says... and note the reader's comment at the very end...about Linux...I think he's talking about Linux. Anyway the takeaway executive summary of this article is simple: C# rulz. RL http://programmingzen.com/2010/06/23/how-microsoft-is-changing-the-programm= ing-world/ How Microsoft is changing the programming world Posted on Jun 23rd, 2010 in .NET, Programming Languages | 47 comments Several years ago I knew a programmer, we=92ll call him Joe, who fancied himsel...

Visio Text (Number) Automation
Hi. I'm creating an Organization Chart for my company. I have the org charts already setup with names, shapes, etc; however, I would like to add the total number or employees in each group, which would add up to larger group, and finally all the way to the top which will have the total number for all employees. i would like to somehow automate this process like excel does wtih a formula (=techdept+accoutningdept) which would add the numbers i manually filled in the tech and accouting dept, but would add those number together for the group above that. Thanks. ...

How do you point to a named range in linked workbooks?
Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Is this what you're looking for...? In workbook 2 enter "=[Book1]Sheet1!$A$2" in cell A2. Do the same for cells B2 through K2 with the correct respective links. Hope this helps. -Chad "KG" wrote: > Workbook #1 has a range named "Gross Sales" in Sheet1 Cells A2:K2. How can I > point to this range so that I can link it to Sheet1 Cells2:K2 in Workbook #2? Or, if you don't like to ...

help with lookup formula (SHEET 1 attatched)
I need to make a formula using a lookup. The first spreadsheet is a inventory (ALO INVENTORY JP). The second (P&G 103) is a chart tha tells number of gallons of alcohol per foot/inch/fraction. On ALO INVENTORY JP I insert the # of feet, inches, and the fraction i cells R5 and T5. I want these numbers to lookup the number of gallon off of the P&G 103 worksheet. For example: P&G 103 (on inventory sheet) is 26ft 4 1/2 inches, i should look-up to be 34572 gal. for the 26'4" and 55 gal. for the 1/2" These two numbers should be added together and multiplied by the numbe ...

API TO get User Name
What is an Win32 call to get the user name in the form of COMPUTER\\User ? >What is an Win32 call to get the user name in the form of COMPUTER\\User ? Have a look at GetUserNameEx NameSamCompatible Dave Hi Michael, Yes, just as David provided, GetUserNameEx will help you to retrieve the user or other security principal associated with the calling thread. If you got a different token other than the current calling thread, you may first call ImpersonateLoggedOnUser by passing the token to impersonate the token in the current thread and then use GetUserNameEx to obtain the user ...

ROUNDED NUMBERS IN FORMULA'S
Can anybody help me to set up Excel so that it uses rounded numbers in formulas? I have got the cell formatted to 'show' the number rounded to two decimal places but the complete root number (shown in the formula bar), is used! Eg. 1.5642 (1.56 shown) x 4 = 6.2568 (6.25 shown) I want to multiply 1.56 x 4 = 6.24, without going into each individual cell and removing the unrequired numbers. I hope that somebody can help me - even if it is to tell me to stop trying because it's maybe not possible. I thank anybody in advance for any constructive response. bing1080 =round(A1...

Number of rows in a worksheet
I have a very large data file. It has 110,000 records approximately. want to import it into an excel worksheet. I did so using the Impor Text function and defining the field widths with !. Now the most I can seem to import onto a worrksheet is approximatel 65,500 records. The wizard then advises me to import the rest ont another data sheet, excluding the data already imported on to the firs sheet. When I try to do this, the wizard will only allow me to exclude th first 32,000 records from the second import and then will only impor approxiately 32,000 more records. In effect the second impor...

Excel formula problem SUMPRODUCT
Hi Guys, I have a problem with a formula adding up rows against 2 parameters. I am trying to add up rows on a report that 1) show that the actio against it is "open" and its target completion date is overdue. I have tried the following formula but it will not work. =SUMPRODUCT(--(H5:H47="OPEN")*(G5:G47="<TODAY())) This formula works if I substitute the date part with another tex parameter but will have nothing to do with dates even if I put in specific date that I know matches one from the column, I still get 0 Any ideas. Thanks in advance And -- Message ...

Query--using Access 2007
I have a tblAddress table with a lookup field called TypeofAddressID. A company may have many addresses with different TypeofAddressID—such as 1 = Business, 2 = Mailstop, 3 = PO Box, and so on. I need to pull out TypeofAddressID # 1. Then if a 1 is not available, give me the type that is (which may be the PO address or Mailstop). I tried DLookup, Xor, and IIf([TypeofAddressID]=1, 1, IIf([TypeofAddressID]=2, 2, IIf([TypeofAddressID]=3, 3, 4))), but it is listing all addresses per company instead of either or. Any suggestions? -- Message posted via http://www.accessmonster....

Can I copy formulas "as they are" when they are not static
Hi, I have numerous cells with formulas in them, and I would like to transfer them to other location without formulas changing. They need to be dynamic before and after, but not during this transfer Don't ask me why ;-) -Sirritys Find and replace all "=" with "~", move and change back -- Kind regards, Niek Otten Microsoft MVP - Excel "Sirritys" <aki.koikkalainen@hotmail.com> wrote in message news:1154937837.006411.154950@m73g2000cwd.googlegroups.com... | Hi, | | I have numerous cells with formulas in them, and I would like to | transfer them to...

Outlook 2003 error when trying to use WordMail
Hi, Outlook 2003 has recently started reporting the following error, when trying to use Word 2003 as the MAil Editor. I have tried all the uslal things including Uninstalling Office, clearing the main registry settings and re installing, but nothing seems to work, does anyone have any idea what is causing this and more importantly how to solve it: "Word is unavailable, not installed, or is not the same version as outlook. The outlook email word editor will be used instead. an OLE registration error occured. The program is not correctly installed run Setup program again" Gavin,...

Change Name of Command Button
How can I change the name of a command button to text entered in cell a1? -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27292 View this thread: http://www.excelforum.com/showthread.php?threadid=477556 try this highlight the cell edit-copy right click commandbutton while in design mode click <properties> highlight name now edit -pastespecial hit enter toggle design mode "comotoman" <comotoman.1x5sih_1129741514.3488@excelforum-nospam.com&g...

If formula that looks at multiple cells and values, and then calul
I'm trying to put together a production spreadsheet for mechanical manufacturing that figures out a length of one piece of material based on other dimensions. It's for storm shutter parts and the cut dimensions need to be calculated from the field measures. There are many types of tracks available and the blade length is dependant upon the finish height, track type and structure. I need the formula to look at 5 different cells that may or may not have data for the top track and only use the data from the one that's filled out. Same for the bottom track for a total of 10 ...

how can I enter 453. to show as 453 instead of 4.53 when using th.
I'm not sure how to use the decimal when entering numbers in excel. I want to use the fixed decimal space as to so when I just type in 453 it appears as 4.53 But when I want to enter 60 and I enter 60. I get .60 Any thoughts? Enter 60. with the decimal point at the end. In article <B343D637-7039-48CA-AB16-6B389D3A441A@microsoft.com>, "Scott" <Scott@discussions.microsoft.com> wrote: > I'm not sure how to use the decimal when entering numbers in excel. I want > to use the fixed decimal space as to so when I just type in 453 it appears as >...

Payee name field too small
When downloading transactions from my PNC Bank account, all check card purchases appear as, "CHECK CARD PURCHASE XXXXXXXX1583". Obviously the purchase amount appears as well however, unless I go to the banks website and pull up my statement, there is no way for me to know who the actual Payee is. Is there a way to make Money 2005 IGNORE these first 32 characters when reading the downloaded transactions? That way I would be able to set up a more efficient and accurate Payee list & Rules Manager without having to go to the web and look up each individual transaction. In ...

I cant use englisch function names in a swedich version of excel
Hi, Why can't I use Englisch named function names, like =ROUND(...) in a sheet when I have a Swedich version of excel. Excel will tell me the error "#Name?" when I open an Englisch version worksheet or if I try to enter an englisch function name. But if I write a macro with the englisch function name inside the code and I call that macro in the sheet the function will be translated to the swedich function name. Thanks Pelle Hi this is just the way Excel works. For a function translation see: http://www.contextures.com/functions.html -- Regards Frank Kabel Frankfurt, Germany ...