HLookup, multiple tables and range names in other cells

Hello,

I am beginning to discover Excel with the aid of various textbooks but
I am stumped on one problem and don't wish to embark on VBA custom
functions unless I have to.

I am intending to use HLookup with multiple tables (over 15) in the
same worksheet. So here is my problem,

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

The 'table_array' part can be a range or the name of a range, if I
have a cell that is calculated to produce the name of a range eg. an
INDEX and MATCH formula, how can I get 'table_array' to accept this
without errors?

eg.=HLOOKUP(A12,C5,2,TRUE)

where the cell C5 displays the name of a data range Model_1,
corresponding to a range D1:H2.
The reason i'm trying to do this is that cell C5 will change range
names depending on what data I have put into the spreadsheet.

Any ideas on getting round this problem in any way?

Thanks in advance,

Matt Harris

Leeds, W. Yorkshire
Great Britain
0
pre1mh (1)
8/26/2004 3:21:50 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
612 Views

Similar Articles

[PageSpeed] 31

Hi Matt

if i'm understanding correctly try

=HLOOKUP(A12,Indirect(C5),2,TRUE)

Cheers
JulieD

"Matt Harris" <pre1mh@leeds.ac.uk> wrote in message
news:75f10a18.0408260721.7f6881c6@posting.google.com...
> Hello,
>
> I am beginning to discover Excel with the aid of various textbooks but
> I am stumped on one problem and don't wish to embark on VBA custom
> functions unless I have to.
>
> I am intending to use HLookup with multiple tables (over 15) in the
> same worksheet. So here is my problem,
>
> =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
>
> The 'table_array' part can be a range or the name of a range, if I
> have a cell that is calculated to produce the name of a range eg. an
> INDEX and MATCH formula, how can I get 'table_array' to accept this
> without errors?
>
> eg.=HLOOKUP(A12,C5,2,TRUE)
>
> where the cell C5 displays the name of a data range Model_1,
> corresponding to a range D1:H2.
> The reason i'm trying to do this is that cell C5 will change range
> names depending on what data I have put into the spreadsheet.
>
> Any ideas on getting round this problem in any way?
>
> Thanks in advance,
>
> Matt Harris
>
> Leeds, W. Yorkshire
> Great Britain


0
JulieD1 (2295)
8/26/2004 3:24:41 PM
Use INDIRECT as follows:

=HLOOKUP(A12,INDIRECT(C5),2,TRUE

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

0
8/26/2004 3:35:03 PM
Reply:

Similar Artilces:

multiple users send from common email address
Hello, I have several users from support department (exchange 2003). Although they have each his own email address, I would like to let them send email from a common address such as support@company.com from their outlook clients. Really, they could use the "reply to" field, but is there some simpler way? On the other hand, I would like all of them to receive messages at support@company.com address. Is it possible? many thanks in advance. O. Ondrej: To get them all to receive mail at support@yourdomain.com, just create a distribution group in Active Directory Users and Co...

Adding columns to item table
Are there any incompatibility issues I would need to worry about in adding additional columns to tables in the database? I'm already using most of the available fields in the item table and I would like a place to store each items ASIN assigned from amazon. I don't need to be able to read or write this in either the POS or the Manager, I just need to be able to access it through SQL queries. I would like to know the same thing. Has anybody done that without problems? <tfitts@gmail.com> wrote in message news:ce05743c-7ca2-49cc-89da-815d91c77e4d@1g2000prd.googlegroups.com......

table of contents
The whole tab thing just works like it's broken in Pub 2000. No matter how many times I reset the tabs for the table of contents, it goes back to mish mash when I open it.. I then have to reset each line individually because it's not smart enough to do the whole page. The last line doesn't have a chapter number so it won't stay in line with the others at the right edge. It's a real pain in the neck. After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Asussertown <DROPsussertown@adelphia.net>... > The whole tab thing just works like it'...

How do I use the connection string to connect to a table on the database that I am working on.
I have two related tables. and one form of which one of the tables is a subform and i want to write the data on the subform to a textfile. I used this code to connect to the table but because I have already opened the MDB. I get the error that the file is already openned. objConn.CursorLocation = adUseClient objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Documents and Settings\exds006\Desktop\Electronic LIV;" & "Persist Security Info=False" objConn.Open Is there another way of accessing th...

be able to print indivually different labels on multiple label sh.
hi i want to be able to use publisher to print individual labels on a multiple label sheet, any ideas regards greg Do you mean different addresses on the labels or be able to print one label as needed and tell it which label you want to use? (I do the latter in Word.) -- JoAnn Paules MVP Microsoft [Publisher] "Greg" <Greg@discussions.microsoft.com> wrote in message news:740CB634-47E8-4EEC-BE75-768932495A30@microsoft.com... > hi > i want to be able to use publisher to print individual labels on a > multiple > label sheet, > any ideas > regards > g...

Word/Outlook convert content to a table when forwarding msgs
How do I prevent Word/Outlook from converting the content of a forwarded message to a table (actually, a bunch of nested tables) when I forward a message? Outlook/Word doesn't do this. Apparently the original message was already formatted this way. If you are convince that is not the case (look at the HTML source of the received message), then you'll have to provide some configuration details for us to work with and steps to reproduce. Also, does this happen to all messages or just this one? -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outloo...

company name change
Hello Our company has recently changes names. We already have two email domains currently in use, one set as primary for everything and another which was used in the past. We have one defined SMTP address, which is primary for all users. We also have another which was used int he past, but is not listed as a SMTP address in EXCH system mgr, recipients policies. Of course, I have a new domain to add as our primary smtp address and will need to make the current smtp address secondary for users. In EXCH system mgr, I do see CCMAIL and MS properties which appear to be our old email domain...

how do i change the color of a cell auotmatically?
I am desiging a data shee and i want to changes the color of a cell based on the valuse of anthor cell. ex. if "cell m34" is equal to 0 then "cell p21" is white, if cell m34 less than 1548, then "cell P21" is yellow Can this be done? If so, please help me out. Hi! Select cell P21 Goto Format>Conditional Formatting In the dropdown select Formula is Enter this formula in the box: =AND(M34<>"",M34=0) Click the Format button and select the style to apply then click OK. Click the Add button In Condition 2 Formula is: =AND(M34>=1,M34<=154...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

How to skip cells with text strings.
What should I add to the code below to cause it to skip a cell tha contains a text string in the cell above it? I need it to only work o numbers in the cell above it. If ActiveCell.Offset(rowoffset:=-1) <> "" Then ActiveCell = ActiveCell.Offset(rowoffset:=-1) + 1 Application.SendKeys ("{Enter}") End If Thanks so much. mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?thread...

Diffrent cell on diffrent date?
I�m going to TRY and explain this the best I can. For work when we do our cash out at the end of the night we use a exce sheet. This sheet is to add up our change, checks, credit cards etc etc. etc. When we're done we print it out and than type the figure into another excel sheet. I pretty much know how to combine the sheets so instead of typing the numbers into SS1 and than entering i into SS2 I can have the numbers going from SS1 to SS2 automatically But my question is that how do I set it up so when I type the info i SS1 that it will go to in to SS2 but to have the cells change by dat...

Excel Formula Guidance. Formula need to determine if cell is popul
Hi, Hopefully someone will be able to provide some guidance. I have been tasked with setting up some spreadsheets at work to application coming into my department and the number of days it takes for us to deal with them. To work this out is easy enough using formula =NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $ referencing bank holidays listed in the spreadsheet and B4 received date and E4 being the completed date. I now need to include some extra lines as I need to be able to monitor written enquiries as well so the headings would read Application ...

graph data from multiple worksheets
I have about 10 worksheets with data in them, I would like to put it all on one chart, how do I do it? I cannot fit it all in one worksheet. Produce the chart from the data from your first workshet. Go to your second worksheet, select the relevant data, copy, go to your chart, edit/ paste special, and accept the relevant option to add a new data series or add data to an existing series. Alternatively having produced your original chart, go to Source Data, and either adding data to an existing series or adding a new series will allow you to select the relevant data from whichever work...

HTML_Control Range name
I have a problem with one of my macros where I am copy an entire worksheet (not by range) to another workbook. However, every other worksheet it seems comes across with an message indicating that that range name HTML_Control already exists. I had run a piece of code in my worksheet to outline all range names and it appears to be some kind of range name....and to my surprise there are a whole bunch of other names as well. Can someone tell me if there is any specific purpose for it? Hi, Do you have controls on the worksheet? If so try renaimng them to something other than commandbutton1...

Report Name Customer
Hi We have created a custom report in Visual Studio which shows all the service activities per customer in CRM. I now would like to display the customer name which was used in the filter as the heading of the report. If no customer was selected the heading should be blank. Do I need to use CRM_FilterText to achieve this? I have attempted to insert a parameter without success. I would really appreciate some guidance in how to achieve this. Many Thanks Mark no you can easily achieve this with a table group heading "Mark Braithwaite" <MarkBraithwaite@discussions.microsoft....

Excel Table to Individual product list and spend
Hi, Basically I have an Excel data table and I have a Product name drop down list. I want to create a formula or macro so when a Product is selected, it would output the list of companies and their spend with the product. Below is a sample but the real spreadsheet has thousands of vendor names and spend for each product and there are probably ten product names altogether. Company Product1 Product2 Product3 Product4 Total ABC 7,009 8,000 15,009 Key 805,036 805,036 PC 56,016 57,470 568 114,054 Water 3,090 651 298,250 301,991 Total 66,115 863,157 306,818 -...

Coping numbers to next blank cell on another worksheet
I am just learning Excel (need SIMPLE help) but any help would be greatly appreciated. My OS is XL Professional and I have MS Excel 2002 SP2. I need to copy calculated numbers (I got this much done on my own) in specific cells on worksheet # 1 to the next blank cells in existing columns of numbers used in calculations on other worksheets in the same workbook. I other words, I have a formula that calculates numbers and places the results in the same column and row each time it is used. Those calculated numbers need to be copied and used on different worksheets in the same workbook. While...

problem with printing import of word table
Running Vista Ultimate 64-bit; office pro 2007 suite: How do I get rid of the boxes when trying to publishing? I need to import a table created in word 2007. I can get the text box set for margins and size, but I cannot find the command to keep the boxes surrounding each row and column of the table from printing. I apologize in advance for seeming so ignorant, but this is the first time trying this kind of operation. Your help is totally appreciated in advance. Thanks Do you mean the borders? High-light the entire table, Lines, more lines, select the pre-set grid, expand the colo...

Dimension Update and Pivot table report
I have an Excel (03) Report file which uses Analysis Services (SP4) Cube. Works fine still using AS 2000 not 2005. When I make any changes to Dimension and process Dim. and Cube Excel file doesn't takes my Dim changes... I have to take (drag) the changed Dim. back to field list and bring it back to report.... then only it works.....problem is we have 100s of Excel Cube Reports. Error I get: The item Could not be found in OLAP Cube. Thank you - Ashok ...

Pivot Table Help #2
I have a sheet that I use to keep track daily of the value of inventory by day, and grouped by week. The value of the inventory is tracked by three different subgroups. (Below is an example). On my piviot table the values are grouped by type and by week. So what I get is a five day average of each Type by week, which is exactly what I want. My averages are as follows: M $796091.00 O $615069.00 R $3180669.00 =================== Grand Totals $1530609.00 The grand totals are where I need help. The grand total is an average of the avaerages. Is it possible to display a sum of the av...

Word 2007
I have created several tables in a Word document. I would like to know if there is a way to copy the table formatting from one table to the next while still mainiting the information that is currently in the table. Well, one way you could do this would be to:- - click in the Table - TableTools - Design contextual tab - Table Styles group - choose a Style in here and then apply the same Style to the other Tables that you have. If my comments have helped please hit Yes. Thanks. "Sammy" wrote: > I have created several tables in a Word d...

How to save custom cell formatting?
When I goto Format - Cells - Custom and type my own format. For e.g. #,###,##0.00000 The format mentioned above is available for current file only. How do I make it available to other files (and new ones) as well? ...

Need to access selective cells from Excel file stored at Sharepoin
I need to link my PPT 2007 slide with an Excel 2003/2007 sheet data stored at Sharepoint 2007. Excel sheet is having lots of cells/rows - hundreds, but i want only selective ranges to be visible in PPT slide. Moreover, if can apply some calculations prior to rendering the date, is also required. Any suggestions please? ...

how do i copy rows to a new worksheet by a cell value
Hi, I have a worksheet with many rows of values. Where a cell in a particular column is "Yes" I wish to copy the values of that row to a different worksheet. Is this something suited to a macro? Many thanks for any assistance. Nibbs without a macro, use data>filter>autofilter>filter on yes>copy -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Nibbs" <Nibbs@discussions.microsoft.com> wrote in message news:144D7242-BAFC-43B4-98EA-EC95657832B2@microsoft.com... > Hi, > > I have a worksheet with many rows of values. Where a cell ...

Offline access to multiple mailboxes
I currently use O2003 to open multiple mailboxes. 1 primary and 2 secondary. I cannot figure out a way to make any of the folders in the secondary mailboxes available offline. Is it possible without creating seperate profiles? Thanks No, you need to create separate profiles & OST files. Rick wrote: > I currently use O2003 to open multiple mailboxes. 1 > primary and 2 secondary. I cannot figure out a way to > make any of the folders in the secondary mailboxes > available offline. Is it possible without creating > seperate profiles? > > Thanks Add the mailb...