1. ## Hlookup (Excel 2000)

Complicated Lookup
In the totals sheet (Quarterly Updates) I need a HLookup statement that will return the correct value from the respective dealer (Dealer 1, Dealer 2, Dealer 3, Dealer 4, etc.) for the Year and the Quarter. In the attached sample I have a need for a HLookup statement that will lookup both the Year in cell C1 and the Quarter in cell C2 for the respective sheet and then return the appropriate value. How do I write a statement in cell F71 (Quarterly Updates) that will look up the correct value for the respective Dealer? I was also hoping to place a drop down box displaying the years 2000

2. ## Re: Hlookup (Excel 2000)

Trey the following humongous formula in F71:

=OFFSET(INDIRECT("'"&F\$69&"'!A70"),MATCH(\$A71,\$A\$7 1:\$A\$74,0),MATCH(VALUE(\$C\$1),INDIRECT("'"&F\$69&"'! 68:68"),0)+\$C\$2-2)

It uses a combination of MATCH, OFFSET and INDIRECT to be able to refer to the correct sheet. You can fill the formula down and right. I have attached a modified version of your workbook with combo boxes from the Control Toolbox. They only work with the years 2001-2006 and with Quarters 1 thru 4, I didn't include options for 2000 and for Total.

Note: the cells for Dealer 3 etc. display #REF since there are no sheets in your workbook for those dealers.

3. ## Re: Hlookup (Excel 2000)

You are brilliant. Can you tell me how to adjust the toolbox to include Total and 2000? Also, how can I resize the box in C1 and change the font? You are amazing.

4. ## Re: Hlookup (Excel 2000)

I'll have to think a bit about the first question.

About the second one: display the Control Toolbox. Click the Design Mode button (the first one, with triangle, ruler and pencil). You can now select a combo box by clicking on it, and move and resize it with the mouse. Hold down the Alt key while dragging to align to cell borders. Click the Properties button on the Toolbox (the second button) to set the font etc. When you're done, turn off the Design Mode button again. (You can't use the combo boxes as long as the Design Mode button is on.)

5. ## Re: Hlookup (Excel 2000)

Can I have 'Total' just be #5?

6. ## Re: Hlookup (Excel 2000)

In the attached version, I have added Total to the list fill range for the Quarter combo box, and adjusted the formula.

7. ## Re: Hlookup (Excel 2000)

Can you send the code to the combo boxes. Everytime I have attempted to build on the original spreadsheet, the combo box is empty, thus resulting in errors.

8. ## Re: Hlookup (Excel 2000)

Put the list of years somewhere on a worksheet (it may be another worksheet than the one that contains the combo box). In the workbook I attached, the list is in B87:B92.
Select View | Toolbars and make sure that Control Toolbox is checked.
Place a combo box from the Toolbox on your worksheet. This will automatically turn on Design Mode (the first button on the Toolbox will be "down"/"on")
Click the Properties button on the Toolbox.
You'll see the properties of the Toolbox.
Set the LinkedCell property to the address of the cell that should contain the selected year, in the demo it is C1.
Set the ListFillRange property to the address of the list of years: B87:B92, or if it is on another sheet, for example 'Other Sheet'!A1:A6
You can move and resize the combo box; keep Alt down to align it to cell borders.
To test the combo box, turn off Design Mode.

It's similar for the quarter; in the workbook I attached, the list of quarters plus Total is in C87:C91; this will be the ListFillRange, and the LinkedCell is C2.

9. ## Re: Hlookup (Excel 2000)

Thank you. Once again you have taught me something new.

10. ## Re: Hlookup (Excel 2000)

Can you help me with the formula in C71 in the attached file. The real spreadsheet is beautiful, but I can't get this formula to work. Thank you.

11. ## Re: Hlookup (Excel 2000)

You don't provide any indication what the formula should do.

12. ## Re: Hlookup (Excel 2000)

The formula in C71 should do the same as the Offset formula used in cell B71(which is the Domestic Segment). However, it should pick up data from sheet, Cindy, and the range begins on cell A114 (INT Segment). Everytime I rewrite the range to correlate with the Int Segment, I get an error message. Thank you.

13. ## Re: Hlookup (Excel 2000)

This?

=OFFSET(INDIRECT("'" & C\$69 & "'!A113"),MATCH(\$A71,\$A\$71:\$A\$110,0),MATCH(VALUE(\$ C\$1),INDIRECT("'" & C\$69 & "'!111:111"),0)+IF(\$C\$2="Total",5,\$C\$2)-2)

A113 is the cell in the same position with respect to Int as A70 is with respect to Domestic

14. ## Re: Hlookup (Excel 2000)

Yes, Bingo! Thank you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•