[SOLVED] Matrix vertical and horizontal lookup?


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 27th February 2014, 22:43
ciapul12's Avatar
ciapul12 ciapul12 is offline
Junior Member
MS Office Version: MS Office 2007
MS Office Skill Level: New to MS Office
 
Join Date: Jan 2014
Location: UK
Age: 34
Posts: 6
Posts Per Day: 0.00
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (4) ciapul12 is on a distinguished road
England
Default Matrix vertical and horizontal lookup?
     Excel: Functions   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: Functions   

Hi all.
Is it possible to have a horizontal and vertical lookup in the matrix?
what I am looking for is to have lookup that would be able to look for red and yellow cells and if one is found then it would lookup for the column and row and bring the procedure title as well as employee name.
Better explenation is in the sample workbook so please take a look.
I appreciate your help.
Thank You
Dan.
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.



The Code Cage Advertisment
Advertisement
  #2  
Old 7th March 2014, 11:21
Roger Govier's Avatar
Roger Govier Roger Govier is offline
Solution Centre Professional
MS Office Version: MS Office 97, 2003, 2007, 2010, 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Jan 2009
Location: Abergavenny, Wales, UK
Posts: 222
Posts Per Day: 0.07
My Top Tip Count: 0
Thanks to others: 0
Thanked 47 Times in 47 Posts
Reputation: 985 (20) Roger Govier is a splendid one to beholdRoger Govier is a splendid one to beholdRoger Govier is a splendid one to beholdRoger Govier is a splendid one to beholdRoger Govier is a splendid one to beholdRoger Govier is a splendid one to beholdRoger Govier is a splendid one to beholdRoger Govier is a splendid one to behold
Wales
Default Re: Matrix vertical and horizontal lookup?

------ Register to get rid of these "In Post" ads! ------


Hi

It is almost impossible to answer your question as there is no indication of what the two columns of dates refer to under each course, nor what the reference date for comparison is.

Equally this data layout with Merged cells in row 2, does not lend itslef easily to any form of Index and Match.

I would recast my data into a Normalised format as I have shown on Sheet3.
You may want more date columns dependent upon the significance of your dates, but
I have entered a comparison date in cell B1, and then formulae in the table which calculate whether the date in column B is past that date.

If you were always wanting to compare to Today, then you could enter =TODAY() in B1

Then, you can summarise the data with a Pivot Table as I have shown.
By making the column Show, a Report Filter, you can suppress any data for employees who are within limits.
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.
__________________
Regards - Roger Govier, Microsoft Excel MVP



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
horizontal, lookup, matrix, vertical

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 07:43.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.16395 seconds
  • Memory Usage 6,320KB
  • Queries Executed 18 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (2)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (2)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (2)post_thanks_box
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (2)post_thanks_postbit_info
  • (2)postbit
  • (2)postbit_attachment
  • (2)postbit_ctoptip
  • (2)postbit_onlinestatus
  • (9)postbit_reputation
  • (2)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (4)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • reputation_power
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • error_fetch
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete