INDIRECT function error

Hi 

I have a strange problem that I can't work out.

In sheet 1 A1 - A10, I have a list of Surnames.
Sheet 2 to sheet 11 are named according to this list.

(all these sheets have been copied from a Template sheet)

If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant 
sheet.

One thing I noticed is that if I manually insert a sheet and reference the 
name in the INDIRECT function, the formula works. It doesn't seem to work for 
the sheets that have been copied

Any ideas?


0
2/21/2005 8:29:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
513 Views

Similar Articles

[PageSpeed] 9

Hi Anthony,

> If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant 
> sheet.

If you have spaces in your sheet names, you'll need single quotes around the 
name, so something like the following should work:

=INDIRECT("'"&A1&"'!D20")

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk


0
2/21/2005 10:47:03 AM
Thanks Stephen, that worked a treat

Infact, I do have spaces in my Sheet Names. I really thought that the 
INDIRECT function would 'lookup' exactly, spaces included. Obviously not!

Thanks for your help

"Stephen Bullen" wrote:

> Hi Anthony,
> 
> > If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant 
> > sheet.
> 
> If you have spaces in your sheet names, you'll need single quotes around the 
> name, so something like the following should work:
> 
> =INDIRECT("'"&A1&"'!D20")
> 
> Regards
> 
> Stephen Bullen
> Microsoft MVP - Excel
> www.oaltd.co.uk
> 
> 
> 
0
2/21/2005 11:03:02 AM
No, it won't, because the space is the intersection operator. 

If you have a column named July and a row named Sales, and you write July
Sales, it means the intersection of the July and Sales ranges.

On Mon, 21 Feb 2005 03:03:02 -0800, "Anthony Slater"
<AnthonySlater@discussions.microsoft.com> wrote:

>Thanks Stephen, that worked a treat
>
>Infact, I do have spaces in my Sheet Names. I really thought that the 
>INDIRECT function would 'lookup' exactly, spaces included. Obviously not!
>
>Thanks for your help
>
>"Stephen Bullen" wrote:
>
>> Hi Anthony,
>> 
>> > If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant
>> > sheet.
>> 
>> If you have spaces in your sheet names, you'll need single quotes around
the 
>> name, so something like the following should work:
>> 
>> =INDIRECT("'"&A1&"'!D20")
>> 
>> Regards
>> 
>> Stephen Bullen
>> Microsoft MVP - Excel
>> www.oaltd.co.uk
>> 
>> 
>> 

0
anonymous (74722)
2/21/2005 6:26:39 PM
Reply:

Similar Artilces:

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

Upgrading
I want to upgrade from Microsoft Money Financial Suite OEM Edition Version 11.0 to latest version but on trying to open the money file in Microsoft Money 2007 I get the following error message: File was created by an incompatible version Question 1: As the old version was pre-loaded into the Computer when I bought it in India, I don't know which country US/Canada/UK/Australia this software belongs to. So how can I confirm which country is it for? Question 2: And, how can I upgrade it to a version of Money from US or UK. -- Kapil ...

need custom cut and paste functions
Hello, I once wrote here about a problem I had cutting and pasting where columns would turn to "REF!" after a cut and paste. I would work around it by copying, pasting and then manually deleting instead. I thought turning everything in the sheet to absolute references would solve the problem but it didn't so now I am thinking of a different solution. Could someone tell me what I need to do to write my own cut and paste functions which would basically copy the selection and then on a paste it would paste and then delete the original selection from where it was copied from...

VBA Error Message "Compile Error...."
I used a macro from Ron's website to mail each worksheet to the email address in cell a1. I ran this macro and it worked perfectly for one workbook BUT... when I tried to use it for another workbook I got the follwing error message "Microsoft Visual Basic Compile Error Can't find project or library Ok Help". Help me please... do I have to check some more references in the VBA tools? Thanks, Steve Hi Steve Have you set the reference to Outlook in that other workbook also ? You must do that in every workbook or you can use Late binding. See my site for a example --...

Error 1606 Money Service Pack 2002
I have tried all of the suggested fixes that I've seen on these boards for the error 1606. I now have no money program, I can not reinstall the money program and the error still pops up in my Turbo Tax and it is driving me crazy! I can not delete the service pack from the add or remove programs applet. I would very much like to finish the taxes before the 15Th. Does someone know what is going on? I've deleted registry keys, done clean boots, tried to eliminate every vestige of the program that can be found yet, when I try to reinstall from the disk, I'm stymied by Error...

OWA Error for http links
links inside a message body always generate an asp redirect code that does not execute at all thus giving an http 500 error - page not found. Example on a link to http://www.nightlight.org within a mail body. http://orange/exchweb/bin/redir.asp? URL=http://www.nightlight.org/ Exchange Server 2003 running on Server 2000 SP4 PLease help. ...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

Error message #5
Excel 2000 - Try to save workbook as Excel 97 - 2000 & 5.0/95. Receive error message " An error occurred and this feature is no longer functioning properly. Would you like to repair the feature now?" If you say YES I get an "Internal Error 2709." Does anyone know how to determine what feature the error message is referring to and how to repair the issue randy ...

Error creating new task
Error .. "Could not complete operation. One or more parameter values are not vaild" ...

Public folder issue, "messaging interface has returned an unknown error"
I'm running a SBS2003 domain and just added a public folder and a customized form for entry into the public folder. All of our clients (all XP Pro, Outlook 2003 with all updates) can get to that folder and use the form without any difficulty except for one. On one computer I get the classic "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." error message. I have tried a repair install, no change. I have tried making a new profile, no change. I have tried adding / removing his PST file (currently he's not even using a pst file...

Function doesn't run
In my spreadsheet, I have the following function =VLookup(K16, zips, 2) However, instead of returning a result, the function remains in the cell. How do I fix this problem? Format the cell as General and re-enter the formula (F2, ENTER) -- Kind regards, Niek Otten Microsoft MVP - Excel "Justin" <jmeyer@incrementaladvantage.com> wrote in message news:1165596899.059148.31580@80g2000cwy.googlegroups.com... | In my spreadsheet, I have the following function | =VLookup(K16, zips, 2) | However, instead of returning a result, the function remains in the | cell. How do I fix th...

Outlook 2007 error event id: 35
Per hour i receive about 12'000 of these errors in the event viewer on my vista laptop. And usualy my outlook at some point just goes into the "not responding" mode. Then I have to kill the process from the task manager to be able to reopen outlook just to see it freeze again after a couple of seconds. I tried the whole stop indexing service and reinstall index service procedure to no avail. Does anyone else have a solution to this very anoying problem? Fred putzhilfe@gmail.com <putzhilfe@gmail.com> wrote: > Per hour i receive about 12'000 of these errors in the...

Error message when signing into Great Plains
I have a problem when setting up MS Great Plain. When I log into a company (E.G. company ABC), I receive an error message says "Decrypt Job Cost Creation Failed", I press OK, and the program just continues to run. Is there anyone know what is this message is about and how to resolve this problem?? Thank you very much This error is generated by the Wennsoft Products dictionary. Most times it relates to not installing the Wennsoft product from the SETUP.EXE. This is necessary as it installs and registers a couple DLL files, specifically WSREGKEYM.DLL and WSWRAPPER.DLL Refer to th...

Word 2007 Error message
Hello, I'm currently using Works Suite 2006. I am attempting to intall the Word 2007 upgrade to no avail as I receiving the following error message: SET-UP ERROR D:\wordR.ww\osetup.DLL digital signature does not validate or is not present I would appreciate any advice and/or suggestions so that I can install Word 2007. Ciao, Jerry Sorry Jerry but this is Microsoft Access forum. You need to post this question to a different group to find any useful help. "Jerry T" <Jerry T@discussions.microsoft.com> wrote in message news:B45E64C3-D080-405E-9159-CB3E7F73AFFC@micr...

Personal Folder Error when opening Outook 2002
When opening Outlook 2002, I receive the message that the Personal folder was not closed properly and needs to be checked. I allow the check to run, and all is fine. But this error message reappears after I close and reopen Outlook. Please Reply. thanks. ...

"could not fetch new headers " ERROR
i get the error " could not fetch new headers in the inbox of IMA server.The operation was cancelled ", while using my outlook 2000 o win98se mach. config: smtp/imap thro internet mail a/c option pl try to provide solutions to it. the error message pops up say every 5 min whenever outlook is on. thnks n regards gir ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com aggiridharan <aggiridharan.149yy0@outlookforum.com> wrote: > i get the error " coul...

Paid Sales Transaction Removal error message
Hola Familia When I try to do run a "Paid Sales Transaction Removal" against a specify customer (Microsoft Dynamics GP menu >> Tools >> Routines >> Sales >> Paid Transaction Removal) in Dynamics GP 10 with SQL 2005, I get the following error: [Microsoft] [SQL Native Client] [SQL Server] Violation of PRIMARY KEY constraint ‘PKRM30101’. Cannot insert duplicate key in object 'dbo.RM30101'. I searched for the error message in Customer Source Knowledge base and found Article ID : 861084, which I followed and it showed the duplicate document (invoice...

Error while backup SystemState via DPM 2010
Hi.. we backup the SystemState of our Server via DPM 2010. Since we're upgrade from DPM 2007 RTM to DPM 2010RC (now we use DPM 2010 RTM) on the Server DPM is hosted a eventlog error occured every day after the backup is finished: Source: FilterManager, Event ID: 3 "Filter Manager failed to attach to volume '\Device\HarddiskVolume....'. This volume will be unavailable for filtering until a reboot. The final status was 0xc03a001c." Server Version: Windows Server 2008 R2 x64 any ideas? Alex ...

absolutely cannot resolve timeout error
OL 2002 (10.6515.6626) SP3 Win XP HE Follow-up to: *microsoft.public.outlook* (this message is cross-posted to the newsgroups that appear in the headers. To reply, just click "Reply" in news client and it will go to the follow-up newsgroup *microsoft.public.outlook*, where u can follow the thread. Thanks.) Hi, I am constantly receiving error message: Task 'smtp server name - Sending and Receiving' reported error (0x8004210A): 'The operation timed out waiting for a response from the receiving (POP) server. If you continue to receive this message, contact your s...

Error trying to make an account default
I have a new computer and my outlook 2003 is not letting me assign a default account to send and receive. I get this error: "The Specicied Account could not be found. It might have been deleted." I have four accounnts setup and have tried removing the accounts and recreating them and still get the error. when the account has been made, it tests fine. None of the four can be made default. Thanks in Advance! ...

ESM 2003 info store Error on WinXP PC
ESM 2003 on Windows XP SP1a PC: Installed exchange system manager onto a windows xp sp 1a pc. I can run ESM fine, yet not administer public folders. When I access a public folder's properties, select the Permissions Tab and click "client permissions" button, I get an error-- The information store could not be opened. The logon to the Microsoft Exchange Server computer failed. MAPI 1.0 ID no: 80040111-0286-00000000 ID no: c105000 Exchange System Manager OK. Any ideas to resolve? Using ESM on server (even through RDC) works fine! Thanks! ~Terry ...

Can I have spell check ignore errors and alert me to real words?
Instead of stopping on misspellings and skipping real words, is there a way to stop on correct spellings to find actual words in a bunch of gibberish? Sort of an "UNspell check". For example, pass over "JJKSXO" but show me "SPELL". No, there is no way of doing that regards, Peo Sjoblom "JenLynFish" wrote: > Instead of stopping on misspellings and skipping real words, is there a way > to stop on correct spellings to find actual words in a bunch of gibberish? > Sort of an "UNspell check". For example, pass over "JJKSX...

Today Function
how is this function called in the code? I want to use in in an update query that is coded to a button. thanks Hey Dave, I hope I'm understanding what your asking for but I think this is what you are looking for: Today() HTH, Shane Dave wrote: >how is this function called in the code? >I want to use in in an update query that is coded to a button. > >thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200706/1 Well I think it is either Today() or Date() not sure which and not sure how to use it in the code. I ...

Adding a button with a function on protected sheets.
How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks There's several ways to do this. 1. Instead of straight vlookups, use =IF(ISBLANK(VLOOKUP(....)),"",VLOOKUP(....)) wher...