Using INDIRECT within functions

I learned a bit from JLathman in a previous post but tried to use the same 
methodology with this formula (also learned from this Group to get the last 
entered value in a column) without much luck. 

The original formula was this with 2009 sheet being static:

=LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536)

Trying to make it dynamic I tried the following without success:

=LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & 
"!$H$3:$H$65536)"))

Did I miss something or are the double quotes after the <> screwing this up. 
 If so, what's the correct way around it!

Thanks in advance!


0
Utf
1/2/2010 2:54:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
745 Views

Similar Articles

[PageSpeed] 58

This seems ok, tested here:
=LOOKUP(2,1/(INDIRECT("'"&O1&"'!H3:H65536")<>""),INDIRECT("'"&O1&"'!H3:H65536"))
where input in O1 is:  2009
voila? hit the YES below

You wrap INDIRECT to resolve the range bit of it
-- 
Max
Singapore
--- 
"Brian" wrote:
> I learned a bit from JLathman in a previous post but tried to use the same 
> methodology with this formula (also learned from this Group to get the last 
> entered value in a column) without much luck. 
> 
> The original formula was this with 2009 sheet being static:
> 
> =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536)
> 
> Trying to make it dynamic I tried the following without success:
> 
> =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & 
> "!$H$3:$H$65536)"))
> 
> Did I miss something or are the double quotes after the <> screwing this up. 
>  If so, what's the correct way around it!
> 
> Thanks in advance!
> 
> 
0
Utf
1/2/2010 3:19:01 AM
Thanks - didn't realize it should be used twice in the formula.  It makes 
sense though.

"Max" wrote:

> This seems ok, tested here:
> =LOOKUP(2,1/(INDIRECT("'"&O1&"'!H3:H65536")<>""),INDIRECT("'"&O1&"'!H3:H65536"))
> where input in O1 is:  2009
> voila? hit the YES below
> 
> You wrap INDIRECT to resolve the range bit of it
> -- 
> Max
> Singapore
> --- 
> "Brian" wrote:
> > I learned a bit from JLathman in a previous post but tried to use the same 
> > methodology with this formula (also learned from this Group to get the last 
> > entered value in a column) without much luck. 
> > 
> > The original formula was this with 2009 sheet being static:
> > 
> > =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536)
> > 
> > Trying to make it dynamic I tried the following without success:
> > 
> > =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & 
> > "!$H$3:$H$65536)"))
> > 
> > Did I miss something or are the double quotes after the <> screwing this up. 
> >  If so, what's the correct way around it!
> > 
> > Thanks in advance!
> > 
> > 
0
Utf
1/2/2010 3:29:01 AM
Welcome. As mentioned, we wrap INDIRECT to resolve the ranges part of it, 
where ranges would be textstrings formed via concats (using the & operator) 
pointing to cells housing the sheetnames, etc for flexibility.
-- 
Max
Singapore
--- 
"Brian" wrote:
> Thanks - didn't realize it should be used twice in the formula.  It makes 
> sense though.
> 
> "Max" wrote:
> 
> > This seems ok, tested here:
> > =LOOKUP(2,1/(INDIRECT("'"&O1&"'!H3:H65536")<>""),INDIRECT("'"&O1&"'!H3:H65536"))
> > where input in O1 is:  2009
> > voila? hit the YES below
> > 
> > You wrap INDIRECT to resolve the range bit of it
> > -- 
> > Max
> > Singapore
> > --- 
> > "Brian" wrote:
> > > I learned a bit from JLathman in a previous post but tried to use the same 
> > > methodology with this formula (also learned from this Group to get the last 
> > > entered value in a column) without much luck. 
> > > 
> > > The original formula was this with 2009 sheet being static:
> > > 
> > > =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536)
> > > 
> > > Trying to make it dynamic I tried the following without success:
> > > 
> > > =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & 
> > > "!$H$3:$H$65536)"))
> > > 
> > > Did I miss something or are the double quotes after the <> screwing this up. 
> > >  If so, what's the correct way around it!
> > > 
> > > Thanks in advance!
> > > 
> > > 
0
Utf
1/2/2010 3:36:01 AM
>=LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536)

Are you wanting the last entry in the range of a *specific* data type? Do 
you want the last numeric value in the range? Do you want the last text 
value in the range? Or, as your formula will do, do want the last entry in 
the range whether it's numeric or text?

If you want a *specific* data type then there are more efficient ways to do 
it.

-- 
Biff
Microsoft Excel MVP


"Brian" <Brian@discussions.microsoft.com> wrote in message 
news:20DC53EB-5EF6-4CC9-8214-E563D1B96666@microsoft.com...
>I learned a bit from JLathman in a previous post but tried to use the same
> methodology with this formula (also learned from this Group to get the 
> last
> entered value in a column) without much luck.
>
> The original formula was this with 2009 sheet being static:
>
> =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536)
>
> Trying to make it dynamic I tried the following without success:
>
> =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 &
> "!$H$3:$H$65536)"))
>
> Did I miss something or are the double quotes after the <> screwing this 
> up.
> If so, what's the correct way around it!
>
> Thanks in advance!
>
> 


0
T
1/2/2010 3:53:25 AM
Reply:

Similar Artilces:

Changing default printer using VBA
I want to put a statement in my code that changes the default printer to what I need it to be. Please, what is that statement? Richard Mogy In Access 2002 or later, this is trivially easy. Just set the Printer object, e.g.: Set Printer = Printers(0) or Set Printer = Printers("Microsoft XPS Document Writer") To reset to the default Windows printer: Set Printer = Nothing For earlier versions it is more involved. See: http://www.members.shaw.ca/AlbertKallal/msaccess/printch2k.zip or: http://www.members.shaw.ca/AlbertKallal/msaccess/printch97.zip for Access ...

Combo Box Not in List function 11-27-09
I have a combo box that lists the option (limited to list) from a table called tblALLRECCS which has only one field called RECS. How do I write a code in Not In List event of the combo box so that any new entries that is not in the list will be added to the RECS in the tblALLRECCS, after prompting the user with a message box whether he/she wants to add the new item (string) ? Can some one help. Thanks in advance Leo Hi - This is from A2000 - I doubt it has changed much. When you open the event procedure for the NotInList event, you will see that there are two parame...

Autocomplete function
Help! I use excel to enter patients and type of procedure (consultation xray, capd, etc). I like it when the autocomplete function works t automatically type the word just above, but if I leave a cell empty the next cell won't autocomplete. What can I do to make autocomplet work even if there are empty cells above after other entries in th same column? I hope someone can understand my explanation and hel me -- Drsall ----------------------------------------------------------------------- Drsally's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2409 View t...

Problems using the "Arranged By: Conversation" option
Hello, I have Outlook 2007 and have a problem getting Outlook to group conversations the way I want. I haven't found an answer from my web searches so I thought I'd ask here. Sometimes Outlook creates two conversations for the same thread if the thread title contains "Re:" at the beginning. For example, if the thread title is "How do I do X?" and then changes to "Re: How do I do X?" then there will be two conversation groups I need to look at in order to read the entire thread. How can I make Outlook put them all in the same conversation group?...

alignment within text boxes
within a text box, how do I keep the text lines in the heading flush left and make the body justified? I make the heading flush left, then highlight body and justuify it-- but when I do-- the heading becomes justified. Select the heading (high-light) click left justify. -- Mary Sauer http://msauer.mvps.org/ "rebecca1227" <rebecca1227@discussions.microsoft.com> wrote in message news:A63868C4-1905-42FB-B2A2-E6F55F9CF8C5@microsoft.com... > within a text box, how do I keep the text lines in the heading flush left and > make the body justified? I make the heading flus...

Convert Function Result To Plain Text
I want to copy the result of a concatenate function to another cell as plain text (cell contents is the textual result not the function statement). I can't seem to find the function that does that. Can you steer me to it please. There is no such a function (think it over: it's impossible), it can be done with Copy/PasteSpecial-Values! -- Regards! Stefi „"Questor"” ezt írta: > I want to copy the result of a concatenate function to another cell as plain > text (cell contents is the textual result not the function statement). I > can'...

Vlookup And Lookup Functions
Is there any work-around to get VLOOKUP and LOOKUP functions to retur the first cell that exceeds the lookup_value as opposed to the las cell that is less than the lookup_value? Thanks for a response, Chuckles12 -- Chuckles12 ----------------------------------------------------------------------- Chuckles123's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=27806 If the data is sorted descending, VLOOKUP will work. Otherwise, with the data sorted ascending, =INDEX(A:A,MATCH(Target,A:A)...

How do I disable the "save as" function in Excel
I want to prevent user that is using the file to save it in another location in order to distribute it. The sheets are already protected with locked & unlocked cells. The file is also in a protected limited access folder to specific users. The users that is using the file run it from a shortcut. Hi if the user can copy the file using the Windows Explorer not really a chance to prevent this kind of copying -- Regards Frank Kabel Frankfurt, Germany Max wrote: > I want to prevent user that is using the file to save it in another > location in order to distribute it. The sheets ar...

using ASR for disaster recovery
Hi I am running exchange 2003 sp1 on windows 2003. I want to know if i create a ASR disk and backup file of my exchange 2003 server (is alos a DC) in the event that i have a OS disater where i need to rebuild the server, can i use the ASR method to restore my server at the time i created the ASR disk and backup file? I know i still have to backup up my exchagne database, but if i can quickly rebuild my server exaclty the way it was at the time of the ASR backup file creation then all i would need to do is restore my data. Can this be done? ASR backs up the whole system partition (u...

Form or Function help, please?
I've got an Excel worksheet set up where there are two columns for each day of the month. There are ten rows. A user should put an X (I'm using a colored-fill) in just ONE box in each column every day. The user should be able to change where to put the X, but shouldn't be able to enter more than one single sole solitary X in each column. I have no idea how to go about this. Right now, the sheet allows the user to enter anything they want in all ten columns. I also want to limit what the user can enter into the box they choose -- I'd prefer for them to choose a box ...

Using information from Contacts
I find it very frustrating to figure out how Outlook XP is handling my contact information. I have it set to use 'Contacts' and I keep all info in there very tidy, filed in a particular way etc. When I start to enter a name in the 'To' field of a new message, Outlook suggests a number of possibilities based on what I've typed so far, but those possibilities often include several entries which are NOT in my Contacts folder, and which I don't actually want on my computer - for example they might be old email addresses which have been replaced. The Contact concer...

relay within domain with fake user accounts
Hi All, Our system: 1 - DC - Server 2000 SP4 1 - Email Server 2003 w/Exchange 2003 all SP's our up to date. Trend Micro SMB as our anti virus / spam software running on the DC This is a very simple set up. I mentioned trend micro because this is ware I find some issues with exchange. Looking at trends virus logs I see that the exchange server is sending email within our domain with fake user names. Examples are info@mydomain.com, admin@mydomain.com, user@mydomain.com. Of course these user names don't exist in our active directory. Luckily trend catches these emails because they cont...

login on drill down report using ie
We are using a SQL Server embedded connection and a specific user name and password for the Report Server. Meaning not Windows Authentication. When we use a report URL as the drill down from one report to another and click on the link, we get the Report Server logon asking for User Name and Password . Once we enter these the drill down report works fine. We have tried to include the user name (dsu) and password (dsp) in the URL below but cannot get it to work and need assistance in structuring the Hyperlink expression. Also we would like to hide the address information so ...

Is there a way to use VBA to set up a VBA password in another workbook?
Hi everyone, Is there a way to use VBA to set up a VBA password in another workbook? I know there is way to put VBA module and sub procedure into another workbook using VBA, but can I password protect them using VBA as well? AS Not really. The only attempts I've seen all rely on Sendkeys and that's not really reliable. Excel 009 wrote: > > Hi everyone, > > Is there a way to use VBA to set up a VBA password in another workbook? > > I know there is way to put VBA module and sub procedure into another > workbook using VBA, but can I password protect them usi...

I'm looking for a To Do List template to use with Outlook.
I need to keep track of my tasks on a day to day basis and I'm looking for a template that I can use or modify to help me. An Excel spreadsheet will probably provide the best usage because it will allow me to sort and change my data around. Here is a standard To Do List that Microsoft offers as an Excel template: http://office.microsoft.com/en-us/templates/TC010185861033.aspx -- John Mansfield http://cellmatrix.net "Sharon J." wrote: > I need to keep track of my tasks on a day to day basis and I'm looking for a > template that I can use or modify to help ...

Update Statusbar of PowerPoint and Visio 2007 using VB.Net
I am able to update Statusbar of Word and Excel 2007 using VB.Net. But i am unable to do this for PowerPoint and Visio 2007. Is there anyway to update Statusbar of PowerPoint and Visio 2007 using VB.Net? Thanks in advance, Regards Pretty sure you can't in PP. I don't use Visio -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "Keith Howard" wrote: > I am able to update Statusbar of Word and Excel 2007 using VB.Net. But i am > unable to do this fo...

VLOOKUP a cell reference using < and >
In sheet "Stat1", each cell in column C contains a whole number from 1-50. In sheet "Stat2", column A contains a letter and columns C & E are used to create a numeric range. Each letter in Col A is a coder representing the range specified in cols C & E. For instance: A C E A 1 2 B 3 5 C 6 9 D 10 15 I would like to use VLOOKUP (or whatever is appropriate) to find the appropriate letter code (from Stat2 col A) which represents the numeric range (Stat2 cols C & E) for the number in Stat1 col C. The result will be placed in Sta...

Spammers using Outlook to email-bomb sites
I am running Outlook 2002 SP3. I am finding that I get frequent emails containing the headers: Return-Receipt-To and Disposition-Notification-To Every time Outlook downloads a message from my POP3 server containing these headers it spits back an email to the given address. Basically, this gives spammers the ability to use my PC to send an email to anyone they want and I can do nothing to stop it other than switching to another email client. (I found that Eudora does not have this problem.) Is Outlook a hopeless case or is there something I can do to stop this activity? By th...

Disable USB ports using group policies
I am using windows 2003 server std edition SP1. I am trying to apply group policies for disabling the USB ports. I downloaded the scripts for disabling the USB ports & saved it as .adm file. Stored that file under c:\windows\inf folder. Under group policies i imported this policy & disabled the USB drives. I applied the permissions for the appropriate computers. Now when i login from my client i can see the policy get applied(usbstor value under registry changes to 4), i tried connecting my usb drive, it didnt detect it. Later i got a new flash drive & connected it to the cl...

Need for sharing user's calendar for answering service using OWA
I have a requirement to allow a user's calendar to be shared over OWA for purposes of being a publishing author on another user's calendar. So I set the necessary permissions on the calendar object of the user's calendar I wanted to share in outlook to publishing author and assigned that permission to a new user. Then I pointed my browser to https://server/exchange/user/calendar and signed in as the new user who is to receive these new permissions to be able to log into the other user's calendar where "user" in the above url is the user who is granting these p...

attaching unique id to each mail sent out through CRM while using quick Camapaign for a selected list of contacts
Microsoft CRM : how to attach unique id to each mail sent out through CRM while using quick Camapaign for a selected list of contacts? The ID should be different for each contact. Also , i need to have a mechanism to include a mail template from a set of available templates while the quick campaign is created. Any help will be appreciated. ...

Functions in VBA
In a particular situation, I wanted to calculate several values on a worksheet through a macro. But, I find that some of the vital functions are not available in VBA. For example - LEN() is not available in VBA. Is there any workaround for this? Second question - again on fundamental VBA. A1=10 A2=15 B1=5 B2=20 Sometimes, when I check through VBA if Range("A1")+Range("A2") = Range("B1")+Range("B2") this condition evaluates to false, because either of the values would be something like 25.000000000000000178 or something like that. (Then I check the va...

How come there are more replies when using a newsreader
I was wondering how come there are more replies in this group when using a newsreader. I now like using a newsreader to view this group but prefer posting in the web based interface. I have noticed that recently there are a lot more replies to topics that are not showing up in the web based interface but are showing up in the newsreader. Does anyone know if this is related to Microsoft starting to close down the newsgroups or is there another reason. Thanks in advance for the replies. It's due to longstanding (i.e., well over a year) synchronization issues (i.e., the sl...

how could i know how many color have been used in a bitmap?
for those 8bit or less color bitmaps, file content contains a palette table, how could i know how many colors have been used and which color has used? now i can get the HBITMAP handle. When you say " how can I get the HBITMAP handle" give some context. Is this bitmap in a file? Short of enumerating every pixel, there is no way to tell how many colors of the palette have been actually used. joe On 8 May 2007 05:48:48 -0700, thinktwice <memorialday@gmail.com> wrote: >for those 8bit or less color bitmaps, file content contains a palette >table, how could i know h...

wrong Display Name name being used
When comments were made in my thread, they read baker128. I am not that person, but my email was notified that I had a comment about my question. I sent in a note and was told to sign out then sign back in again. No luck. Any ideas what is going on? -- d&d In article <E1F118AB-67D0-4943-B883-3DC06102652D@microsoft.com>, Baker128 wrote: > When comments were made in my thread, they read baker128. I am not that > person, but my email was notified that I had a comment about my question. I > sent in a note and was told to sign out then sign back in again. ...