Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with HLookup - Sometimes works, sometimes blank.
Afternoon All,
Due to concerns that the ladies of this world will find me less attractive if I pull out my remaining hair, I shall pop this one up and see if anyone has got any ideas... I'm trying to use HLookup to lookup the prices of products in a table to the left of where I am doing my calculations. Going down the page was the dates, and each product could have a different price on each date. The idea was that the user would type in the product code and the formula would perform the calculations including the price. Originally, I used HLOOKUP as part of the formula but it was inconcistent. It would work for a while, and then randomly (usually when I changed the value in another cell - an unrelated one), it would go blank. I would then have to go and delete the formula and then press undo and hey presto, it was back to working. The rather messy solution I came up with was to put the HLOOKUP in another column and then reference that cell (and hide the cell when the user was involved). Just wonding if anyone else had had anything similiar or if people have any ideas, Cheers Reg |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with HLookup - Sometimes works, sometimes blank.
"Regnab" wrote:
.. Originally, I used HLOOKUP as part of the formula but it was inconsistent. It would work for a while, and then randomly (usually when I changed the value in another cell - an unrelated one), it would go blank. I would then have to go and delete the formula and then press undo and hey presto, it was back to working. Just a thought on the part above .. is your book calc mode set to automatic ? (or it may be inadvertently set to manual calc mode) To quickly check / change calc mode: Click Tools Options Calculation tab Check Automatic OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with HLookup - Sometimes works, sometimes blank.
Yeah - it's set to Automatic. All the other formula's are updating etc
as per usual... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with HLookup - Sometimes works, sometimes blank.
"Regnab" wrote:
Yeah - it's set to Automatic. All the other formula's are updating etc as per usual... Welcome back! Other than checking the data consistency (between the lookup values and the top row reference values in the hlookup's table array, eg: check whether it's a case of real numbers being looked up/compared with text numbers - or the other way around; check for any extra white spaces if its text strings being compared - TRIM can be used to make the matching more robust), I don't have further ideas on your subject line. Hang around awhile for insights from others. Btw, did the suggested incrementer for the hlookup's row_index_num work for you in my response to your other post ( HLOOKUP - Adjusting the lookup value...) ? Perhaps you could throw a feedback line over there? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Blank cells | Excel Discussion (Misc queries) | |||
Count if not blank... | Excel Discussion (Misc queries) | |||
Generating truly blank cells | Excel Worksheet Functions | |||
Counting blank and filled cells within a range. | Excel Discussion (Misc queries) | |||
Problem with Array Formulas and ISNUMBER | Excel Worksheet Functions |