excel - nesting INDIRECT inside a VLOOKUP?

Is it possible to nest an INDIRECT function inside of a VLOOKUP?

Here's what I've got so far: 

Cell A27: 6-23-2004

Cell B27: 
=VLOOKUP(B$4,'6-23-2004'!$A$1:$C$70,3,FALSE)/60/60/24

I want to replace '6-23-2004' with INDIRECT($A27) so I can autofill
this formula across a large worksheet, and so when I adjust the date
range, it will dynamically update the information PROPERLY according to
the name (row 4 of same col) and date (col A of same row, referring to
a separate worksheet in the same workbook by that name) in the
formula.

I can't seem to get the syntax right. Is there a right syntax? I'm not
just trying to do something that isn't possible, am I? :confused:


---
Message posted from http://www.ExcelForum.com/

0
6/30/2004 6:46:50 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
554 Views

Similar Articles

[PageSpeed] 6

Try something like

=VLOOKUP(B$4,INDIRECT($A27&"!$A$1:$C$70"),3,FALSE)/60/60/24


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"brightshadow >" <<brightshadow.18og33@excelforum-nospam.com>
wrote in message
news:brightshadow.18og33@excelforum-nospam.com...
> Is it possible to nest an INDIRECT function inside of a
VLOOKUP?
>
> Here's what I've got so far:
>
> Cell A27: 6-23-2004
>
> Cell B27:
> =VLOOKUP(B$4,'6-23-2004'!$A$1:$C$70,3,FALSE)/60/60/24
>
> I want to replace '6-23-2004' with INDIRECT($A27) so I can
autofill
> this formula across a large worksheet, and so when I adjust the
date
> range, it will dynamically update the information PROPERLY
according to
> the name (row 4 of same col) and date (col A of same row,
referring to
> a separate worksheet in the same workbook by that name) in the
> formula.
>
> I can't seem to get the syntax right. Is there a right syntax?
I'm not
> just trying to do something that isn't possible, am I?
:confused:
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
chip1 (1821)
6/30/2004 6:58:16 PM
Reply:

Similar Artilces:

Corupt excel file ???
I have a excel file that seems to want to open, but then when it is done opening, There is no data or blank sheet. It goes though the possess and say's at the bottom ready opening but then there is nothing and the screen look's like I just opened Excel but no spread sheet. I then can go to file open another sheet and it works. (Not the one I wont) any body help. of coarse we don�t have a backup??? Email: bhogan@emediaofli.com --- Message posted from http://www.ExcelForum.com/ First, check to see if the file is actually loaded. Click on Window and see if it appears at the bottom o...

Can Excel generate a 3-D scatter plot?
I want to generate surface plots of the form (x,y,f(x,y)). Should I just go buy MathCAD, or can Excel manage this? Hi Andy Pope has 'tricked' one: http://andypope.info/charts/3drotate.htm But you probably go for MathCad :-) -- Regards Frank Kabel Frankfurt, Germany "zzzfizz" <zzzfizz@discussions.microsoft.com> schrieb im Newsbeitrag news:BA2BEAB6-63E1-46E7-816F-B282C590C3D9@microsoft.com... > I want to generate surface plots of the form (x,y,f(x,y)). Should I just go > buy MathCAD, or can Excel manage this? ...

Excel toolbar button
Is it possible to add a toolbar button to MS Excel programatiacally?, without opening excel?. Is it possible to add a toolbar button to MS Excel programatiacally?, without opening excel?, and link it to a macro that already exists in a file? ...

Excel VB-Copy formula down until adjacent cell (left) is blank?
Here is exactly what I am trying to do through VB in Excel: Weekly data pull fills colums A:G. Row count is always different. I am modifying the data pull through VB, and I have a VLOOKUP formula in cell H2. What I want VB to do is copy that formula down column H to the last row (with data) each week. I guess I want it to be dynamic so that as rows decrease/increase the formula is only copied down to the final row/record. I know someone out of this smart group will know how to do this! Thanks in advance! Tony (pseudo code) in a macro .... dim lngLastrow as long dim rngTarget...

Faulting application error in excel
I have a PC that keeps getting a crash in excel. It repeats with the same error each time in the event viewer: Faulting application excel.exe, version 11.0.8324.0, stamp 4bc93a36, faulting module excel.exe, version 11.0.8324.0, stamp 4bc93a36, debug? 0, fault address 0x000b035a. I have tried a repair install. A remove and reinstall. Along with steps to remove the excel profile. The crash happens about every other day and sometimes a couple times or more each day. Any ideas? Thank you, Jason This is a guess and may not work, but ... I've read (somewhere) that most causes of crash...

Excel VBA
Hi again, I have enclosed a timesheet which i want to break down the day activities by a percentage, so if my sheet says an activity took a hour then the day column would tell you percentage of a seven hou day. Also i want the date column date to change each day. Currently it work for one day but then the next day it changes all the above cells to th current date. I have attached the file Cheers Stev Attachment filename: book2.xls Download attachment: http://www.excelforum.com/attachment.php?postid=56396 -- Message posted from http://www.ExcelForum.com ...

In excel when inputting dates 10/4/04 (10/april/04) data converts.
In excel when inputting dates 10/4/04 (10/April/2004) excel converts this to 4/10/04 (4/October/2004) Where can I change the input characteristics. I have changed the date format to English (UK) and this works when I input the date in the American format mm/dd/yy but I want to input in the dd/mm/yy format I think I'd try chaning my windows regional settings. In win98, I'd get to it via: Windows start button|Settings|control Panel|regional settings applet Date Tab Change the short date format to dd/mm/yyyy (I like 4 digit dates--you don't have to use that part--but put it in d...

Excel 2002 Template Wizard
Doe anyone know if the Excel 2002 Template Wizard add-in will work in Excel 2003? I don't use xl2003, but I've seen posts that say that it does. John Fox wrote: > > Doe anyone know if the Excel 2002 Template Wizard add-in > will work in Excel 2003? -- Dave Peterson ec35720@msn.com ...

How to represent a column in Excel
Hi, everyone I currently have a formula. {=SUM(IF((Data!G2:G75="Verified")*(Data!K2:K75<=7),1,0))} However, the length of both column G and K are not fixed. The data is retrieved from a remote database. Is there any other way to represent it ? Thanks One quick-and-dirty way is to just do the whole column, x`like: > {=SUM(IF((Data!G2:G65536="Verified")*(Data!K2:K65536<=7),1,0))} It won't be appropriate for Excel 2007, but is there any chance at all you'll actually fill the sheet to row 65536? It's also considered somewhat sloppy, but you'...

Printing EXCEL items in WORD
Excel seems to work better for tables than WORD's tables; yet when a table created in EXCEL is copied and pasted into a WORD document I caannot get the gridlines to print. Even thouigh in EXCEL the print gridlines instructions are checked. What should I do? I've always apply borders--either in Excel or in Word. In Word: select a cell in the table Table|Select|Table Format|borders and shading|borders tab check the All icon. The gridlines in Word don't print. Word's help says to use borders. (I just checked.) Old Red One wrote: > > Excel seems to work bett...

Outlook
We are on Exchange 2007, Outlook 2007 and Excel 2007. A users sends a specific spreadsheet to multiple users 2-3 times a week. About once a week, one of the users tries to open the file and it is corrupted. The original file is 18K but after he attempts to open it, it reduces to 4K. If he tries to forward the message, it goes out corrupted. If he saves the file first, it is fine. But it still becomes corrupted if he tries to open from the attachment. We all use the same anti-virus and this is the only Excel document he has the problem with. He receives many from various source...

Why doesn't indirect work with a dynamic range created using offse
Hi Has anyone else experienced this issue or I am doing something wrong. I have a series of dynamic range names defined using offset. For example the name USCities is defined as =offset(AA1,0,0,Counta(AA:AA),1) and the name Europeancities is defined as =offset(AB1,0,0,counta(AB:AB),1) In column A, dataentry into the cells is limited to USCities or Europeancities. In column B, I want to limit data entry to the range name appearing in column A. For example if USCities is selected in A1, data entry in cell B1 is to be limited to those cells forming the USCities range name. I have tri...

Excel Add-In loading
Under Windows XP and Office 2003, I have installed an Add-In (xla) and copied it to the XLStart folder. I can load the Add-In fine, but when I close Excel, the menu item disappears. Is there any way to load the Add-In so that I don't have to manually load it each time I close and restart Excel? I did not have this problem on Windows 2000. -- HTH RP (remove nothere from the email address if mailing direct) "xlpuzzled" <xlpuzzled@discussions.microsoft.com> wrote in message news:3E300657-3184-4C41-A201-6D110E26D6C3@microsoft.com... > Under Windows XP and Of...

Excel 2007 Page numbering
Is there a way in 2007 to consectively number all the sheets beginning with one (1) until ....? Thank you in advance for your help. Do you mean consecutive page numbers when printing more than one worksheet? Select the sheets to print using CTRL + click or Shift + click Then set up the header or footer with &[Page] The selected sheets will print consecutively page-numbered. Gord Dibben MS Excel MVP On Fri, 30 Jan 2009 14:09:01 -0800, Dottie <Dottie@discussions.microsoft.com> wrote: >Is there a way in 2007 to consectively number all the sheets beginning with >one (1)...

How do I hide text beyond the last column in Excel?
In the last column of spreadsheet, if the text goes beyond the column boundary, how is the text truncated in the next cell. I know you can enter blanks in the next column, and that will achieve the desired effect, but that's not an optimal solution for us. Could you just hide all of the columns to the right of those cells? Does that help? *********** Regards, Ron "GetVigilant-Jon" wrote: > In the last column of spreadsheet, if the text goes beyond the column > boundary, how is the text truncated in the next cell. I know you can enter > blanks in the next colum...

Excel reporting alternatives
Hi, I am part of a team currently looking for alternatives to replace Vision XL which our Finance team uses for ad-hoc reporting in Great Plains version 8. Although I am still seeking clarification I believe that Vision XL will not work with GP 10 or Office 2007 which we will soon be upgrading too hence a need for a replacement. My team will be writing sql reports using visual studio etc but our Finance team are rather hoping that they can continue using some kind of excel reporting tool, any ideas would be most hopeful. Kind regards Matthew Carpenter Hi Mathew One of the benfits of ...

ayuda con formula excel
quiero saber se me puede ayudar en eso praticamente yo quiero una formula da enserir an la celda CANCELLARE (mira foto para entender) http://img20.imageshack.us/img20/2152/89861598.jpg [img=http://img20.imageshack.us/img20/2152/89861598.th.jpg] que quando uno presiona la tecla CANCELLARE se borre todo el contenido de la linea detras de CANCELLARE o vero ( C9-D9-E9-F9-G9-H9 ) y trambien se tendrian que borrar en la hoja (tabla de datos ) donde estan lo mismo valore de ( C9-D9-E9-F9-G9-H9 ) o vero ( A7-B7-C7-D7-E7- F7-G7 )( mira la foto abajo ) http://img20.imageshack.us/img20/9799/32649272.jp...

Data validation causing problems when using a data form in Excel 2
I have an Excel 2007 workbook that includes data validation set on a number of cells. When using a data form to enter data and I enter an invalid value on the form field corresponding to one of those cells I receive the validation error dialogue that prompts me to retry. I enter the correct data into that field on the form then close the form. My worksheet only has the data relating to the corrected field entered. All other data entered via the form is not entered onto the worksheet. Is this a bug in Excel 2007? I previously was using Excel 2002 and found that when using a ...

Outlook (and Excel) stops working after MSXML installation
I installed MSXML 4.0 as per the "critical updates" message on my W98 today. After re-boot, when I try to open Outlook I get message "Unable to open your defauilt mail folder - the information store could not be opened" and then "Would you like to open your default file system folder instead?" - this is no good and I can't find anything. I can't open Excel either, I get messages about Visual Basic and when I close it, I get message "This process will stop the debugger". I now have a shortcut on my desktop "Microsoft MXL 4.0 parser&...

Project data and time imported into Excel
Hi I am exporting date fields from Microsoft Project into Excel, the problem I am having is finding a way of getting rid of the time portion as it is interferring with the calculation. I understand that a date and time is stored as a double, the integer part is the date and the decimal part is the time. I would like to easily remove the decimal part of the imported part. Any help would be good. thanks daryl You might want to split the time and date into two separate columns. If your date/time is in A1, you can split them like this: B1: =INT(A1) C1: =MOD(A1,1) Format the cell...

Linking Access data to Excel
Hi folks, I have an Access 2000 database and need to use output from queries to populate worksheets in Excel (also 2000), to allow me to then carry out calculations on the data. I need to be able to overwrite the data, so built the spreadsheet formatting around the query output structure, and put all calculations in place, before creating links to the queries - I did this by selecting the query in Access, copying using Copy, then switching into Excel and creating links via Edit>Paste Special>Paste links. I initially built an empty database, i.e. built the structure, based on an...

use of Randbetween() in Excel
Hi to everyone. I had 10 columns in Excel. A1 B1 C1 ……J1 ..... … … ……… … … … ……… A50 B50 C50 j50 Each column uses the Randbetween() function to create integers 1-100, that is Randbetween(1,100). Is there anyway to use somehow the function so the integers in every row to be different? (I mean: A1 <> B1<> C1…….<>J1, A2 <> B2 <> C2…..<> J2,……………, A50 <> B50 <> C50 …….<> j50) Thank you. http://...

speedometer & excel 2003 / xp
I am using the speedometer combination chart as shown by Jon Peltier. Excellent tips! Is there any issue with this type of combination chart with Excel 200 / XP and saving in html? Thanks, Arthu ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Arthur - Funny you should ask. Excel XP and 2003 fixed the bug that allowed data labels to appear outside of the plot area. Actually, I'd been treating it as a feature that allowed data labels to appear outside of the plot...

Excel Precedents
I have a user who is unable to see the arrows when trying to use Precedents. Personally I have little to no experiance in Excel. We've tryed moving in and out of the program, running Detect and Repair, and choosing another excel file. She is the only one with this problem. Can anyone shed any insight on why this might be happening? Thanks in advance. If she has Hide All selected under Tools, Options, View, Objects the arrows would not appear. I don't know of anything else that would cause it -- Jim "Joshua Bright" <Joshua Bright@discussions.microsoft.com&g...

Excel 2002 -> Problem with calculated fields in Pivottable
Hi, I have created a Pivot table and use a calculated field. Now I want to eleminate the "0" of this calculated field. Normally I would drag the field into the page area. But this does not work the normal way. I don't want to change the original database. Does anyone know the possibilty of using a calculated field in the page area of a pivot table? Or another way to avoid the zeros ? Thanks foru your help ...