Hi i need help when i change the index range my formula gives a #ref error could someone please have a look and see where im going wrong please the K9 cell is the working formula but K10 doesnt work i need to change the range when i use the formula for real
The true range i need it to use is absense ot row 15 - 50
Thanks in advance
It may be easier for us to provide help if you explain what you are trying to have the formula do.
Spread the love, add to the Rep
"None of us are as smart as all of us."
i don't think the k9 cell is working like you want it to... the "condition" in your if statement is not actually a logical test, it's just returning the value in M26 which is 0 and will always be... so if is calling that false and defaulting to your hardcoded 14.64 / 24 value... your second formula is trying to return the 18th row of the array M13:M18 (which only has 6 rows... that's why you're getting the reference error, but again, fixing it does not look like it's going to actually do what you want.
what are you trying to accomplish with that formula?
Hope I could help - if your post has been solved don't forget to mark it as such.
Clicking the * below is a great way to say thanks!
"Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito
what i need is what the k9 cell is doing which is when i enter a number in cell G9 it searches Absence for number and if found puts the from time as the last finish time it finds
IE i type 1336 it looks for townsend in absence and returns a value as the last match it finds if no match returns value of 14:38
however i need so when i enter a number in G9 - G50 it only searches absence from row 15 - 50 not the whole page and still do the same return Last value or 14:38
so the formula does work in K9 but where it searches the all rows 1-50 i only want it to search 15 - 50
sorry if this sounds confusing i wish i knew how to make formulas
It's not that the formula you currently have is not returning a value if no match is found, but it's that the formula is returning zero because your formula is referencing an INDEX'd cell range that isn't correct.
In either case, I took a stab at it. Try this and see what corrections need to be made:
Please Login or Register to view this content.
Last edited by mcmahobt; 05-11-2015 at 11:01 AM.
Yeh with few adjustments that works thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks