Retrieving and Highlighting Between tables and a Cell

[ozop102]

I have a table im trying to do two things in. I am intending the user to enter parameters into the following cells

Q32 - selects rank
Q33 - user enters a date

From that I need the following to happen:

1. Enter into cell Q35, the year of the next applicable board
2. highlight the row in the appropriate table

There are three tables, one for each rank possibility and each table list the year of the next board based on a date range. The users date range entered in Q33 should fall between two dates in the corresponding table.

I have attempted to conditionally format using the OR function but cant get the intersection to work and correctly highlight the appropriate cell.

I havent been able to figure out how to retrieve the appropriate year into the cell either.

The tables are copy and pasted from an external source and are updated each year. We have no control over the formatting, we just have to work with what we get. [TABLE="width: 989"]
<tbody>[TR]
[TD]The following is how the the tables are organized and the date range comes to us as a single field. There are 1 or 3 columns associated with each possible rank.[TABLE="width: 989"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]If your DOR to Captain is:[/TD]
[TD="colspan: 7"]You are forecasted to meet the following boards:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Major[/TD]
[TD="colspan: 3"]Lt Col[/TD]
[TD="colspan: 3"]Colonel[/TD]
[/TR]
[TR]
[TD]01 Jan 2014 - 31 Dec 2014[/TD]
[TD]2018[/TD]
[TD]2022[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2028[/TD]
[TD]2029[/TD]
[TD]2030[/TD]
[/TR]
[TR]
[TD]01 Jan 2015 - 31 Dec 2015[/TD]
[TD]2019[/TD]
[TD]2023[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[TD]2029[/TD]
[TD]2030[/TD]
[TD]2031[/TD]
[/TR]
[TR]
[TD]1 Jan 2016 - 31 Dec 2016[/TD]
[TD]2020[/TD]
[TD]2024[/TD]
[TD]2025[/TD]
[TD]2026[/TD]
[TD]2030[/TD]
[TD]2031[/TD]
[TD]2032[/TD]
[/TR]
[TR]
[TD]1 Jan 2017 - 31 Dec 2017[/TD]
[TD]2021[/TD]
[TD]2025[/TD]
[TD]2026[/TD]
[TD]2027[/TD]
[TD]2031[/TD]
[TD]2032[/TD]
[TD]2033[/TD]
[/TR]
[TR]
[TD]1 Jan 2018 - 31 Dec 2018[/TD]
[TD]2022[/TD]
[TD]2026[/TD]
[TD]2027[/TD]
[TD]2028[/TD]
[TD]2032[/TD]
[TD]2033[/TD]
[TD]2034[/TD]
[/TR]
[TR]
[TD]1 Jan 2019 - 31 Dec 2019[/TD]
[TD]2023[/TD]
[TD]2027[/TD]
[TD]2028[/TD]
[TD]2029[/TD]
[TD]2033[/TD]
[TD]2034[/TD]
[TD]2035[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I have the file here below. If someone can help me out and steer me in the right direction id be very greatfull!
[/TD]
[TD="colspan: 7"][/TD]
[/TR]
</tbody>[/TABLE]
https://drive.google.com/open?id=1m5fPg73rfk9oWAOKsYWM5pGSsDxmzgD6

 

[James006]

Hello,

The best solution is to properly redesign your 3 tables into a single clean database ...

with NO MERGED CELLS ...

Otherwise, your headaches will just be terrible ... :wink:

 

[ozop102]

Yea, I had a feeling it would over complicate things. Ive split the cells to show two dates and I added a cell that returns a 1 if the user date falls between a range. Still not quite sure what to use to format and return the value I need.

 

[James006]

Splitting the cells to show two dates ... It is very good ...

But you will have to dive deeper into the logic of your Tables in order to create a Database ...

See https://www.pcworld.com/article/3234335/excel-databases-creating-relational-tables.html

Hope this will help

 

[ozop102]

Thank you. That is a good resource and it will help me later on for sure! The issue is that this table however is not a product I have control over. It is provided to us from another source. Since it is always in the same format I can make a macro that will split the field and create two columns for the date range.

For this tho, I added a hidden column that calculates a 1 or 0 if the provided user date falls within a row. I am now able to use vlookup to extract the date I need and display it. Ill post the document tonight so maybe someone with more knowledge can tell me if there is a better way...I feel I took the long way around using nested if statements.

Now my question still remains on how to highlight the entire row that corresponds to the user date. If I use vlookup, I only highlight the matching cell, how can I have it highlight the row of the table?

Again, the table design is copy and pasted from another source. I cant change how they provide me the table.

 

[James006]

Hello,

Fully understand your constraint of using external tables from a source you are not controlling ...

Your first macro should consider these tables as raw inputs ... and transform them into a useful database located in a dedicated new worksheet ...

At this stage ... you will find yourself on the ' start line ' ...

Hope this clarifies