A different HLOOKUP problem #REF!
Folks,
I had to move a table in my worksheet that is used in a HLOOKUP function. The worksheet is attached. I am now getting a different error that I cannot see: #REF!
Please open attachment to see what I mean and help me to understand what is wrong.
Thanks
HLookup-problem.xlsm
I had to move a table in my worksheet that is used in a HLOOKUP function. The worksheet is attached. I am now getting a different error that I cannot see: #REF!
Please open attachment to see what I mean and help me to understand what is wrong.
Thanks
HLookup-problem.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The reason I selected your soultion was it was returning the dates I was expecting. The other solution did not.
Thanks to all.
Thanks to all.
Actually, I just compared my solution with ssaqibh's solution, and both give same results....
Added a date row and other changes. Attachement included. Here's the formula for the second row of your important dates (as an example.)
=IF(ISERROR(HLOOKUP($B$2,$
="if the whole formula is invalide then nothing", "if the formula delivers zero then nothing", "deliver results."
The ROW($b$2) gives the row number of your month headers. The 16 gives the row number of your results. By using these variables you can move stuff around without breaking it.
lvbarnes
Copy-of-HLookup-problem.xlsm
=IF(ISERROR(HLOOKUP($B$2,$
="if the whole formula is invalide then nothing", "if the formula delivers zero then nothing", "deliver results."
The ROW($b$2) gives the row number of your month headers. The 16 gives the row number of your results. By using these variables you can move stuff around without breaking it.
lvbarnes
Copy-of-HLookup-problem.xlsm
ASKER
Folks,
The best solution of the three was lvbarnes. I revisited the other two solutions and determined that the row() - was wrong for both, giving me dates such as 01/12/2010 for the 12th month in addition to the correct date of 12/3/2010. Once I adjusted for the row count I got the information I was looking for. Ivbarnes's solution worked as presented without any changes.
I truly thought and believed that the solution I accepted was the best at the time and hopefully, no one took offense to my decision. I'll try harder to avoid these situations going forward. My time is limited here at work so I am rushed.
Thanks to all and I appreciate the Experts at Expert Exchange very much!
The best solution of the three was lvbarnes. I revisited the other two solutions and determined that the row() - was wrong for both, giving me dates such as 01/12/2010 for the 12th month in addition to the correct date of 12/3/2010. Once I adjusted for the row count I got the information I was looking for. Ivbarnes's solution worked as presented without any changes.
I truly thought and believed that the solution I accepted was the best at the time and hopefully, no one took offense to my decision. I'll try harder to avoid these situations going forward. My time is limited here at work so I am rushed.
Thanks to all and I appreciate the Experts at Expert Exchange very much!
I still don't see a difference... but hey...that's just me.
=IF($B$2="","",IFERROR(IF(
ROWS($A$9:$A9)+1 <-- Changed to ROW($B$2)+(A16-1),
,FALSE)=0,"",HLOOKUP($B$2,
Below the calendar date table he had 3 rows for the important dates. When the above formula was dragged down the ROWS($A$9:$A9)+1 returned the same date from the first row. I added a row number column to his resultset and changed the +1 to a (Result number -1 ) so that it was dynamic and he could add/remove rows. (Although the important dates will most likely by turned into one date string at some point.)
ROWS($A$9:$A9)+1 <-- Changed to ROW($B$2)+(A16-1),
,FALSE)=0,"",HLOOKUP($B$2,
Below the calendar date table he had 3 rows for the important dates. When the above formula was dragged down the ROWS($A$9:$A9)+1 returned the same date from the first row. I added a row number column to his resultset and changed the +1 to a (Result number -1 ) so that it was dynamic and he could add/remove rows. (Although the important dates will most likely by turned into one date string at some point.)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for everyones continued interest.
I hope I have not affended anyone - I've always tried to be fair and honest.
I hope I have not affended anyone - I've always tried to be fair and honest.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
fh_freese: No worries. Debating solutions is one of the ways that EE members learn and the EE users get the benefit. I didn't look at NB_VC's solution but, because he brought it up I have and learned something from it. I've never used a row range before and he did. His use of that row range eliminates my need for the additional results column.
Yes, fh_freeze, lvbarnes is correct. It's just a point of discussion and I was just looking to clarify and understand why you say some solutions work and others don't. Please don't think that you have offended anyone.
ASKER
Thanks to all of you!
Yikes!!!
NB_VC
791,247 Expert Points
57,560 Points this month
NB_VC
791,247 Expert Points
57,560 Points this month
ASKER
Folks,
This morning I determined that there were indeed three solutions. In fairness to all is there a way to change the point distribution?
This morning I determined that there were indeed three solutions. In fairness to all is there a way to change the point distribution?
Click on the yellow tag called "Request attention" at the bottom right corner of your question
ASKER
thanks to all
I've corrected my error in distributing points
I've corrected my error in distributing points
=IF($B$2="","",IFERROR(IF(