I would like to shorten the results returned to the relevant red columns only. Obviously these will be different all over the place. Is it possible to do this with index, match. I know it is possible with offset but I cannot do this because of the processing overhead. Is what I'm after achievable? Great article on de-referencing results from index, match which I have to share for the hell of it.
1.png
If it's been helpful please mark as helpful
This seems to do it:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I read your question 3 times and looked at the workbook, but I don't understand what you want.
I must be overlooking something crucial.
Would you like to fill in manually what a formula should calculate and explain that based on the other data on your sheet.
@Hans: I’m guessing he just wants to lose all the #REF errors. That's what I addressed. Seems to work rather than producing a column full of errors.
Another optionFormula:Please Login or Register to view this content.
If that is the case please try in J2:Originally Posted by TMS
Formula:Please Login or Register to view this content.
Maybe just:Formula:Please Login or Register to view this content.
Thanks for the rep.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
j4=IFERROR(SUMIF($B$5:$B$115,$I$3,INDEX($C$5:$G$115,,MATCH(SUBSTITUTE($I4,$C$3,"")+0,$C$4:$G$4,0))),"")
copy down
Thx. for the rep.
Thanks for your input more. Amazes me how incredibly malleable the functions are. Also it's a double-edged sword as i remember the 90's the info was not out there. It still is lacking in some areas from Microsoft where they don't provide the info to work this out. Thankfully these forums are so helpful.Thx. for the rep
Workbook attached with all solutions from kind guru's for help of poor tortured souls in the future.
Glad to help & thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks