Dynamically Reference and Select Column

Sythius

New Member
Joined
Jul 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am having a crazy amount of difficulty getting a formula to work for my needs.

I am making a multiple dependent dropdown list in excel, and that part is not a problem anymore.

The difficult part I ran into was when I decided to make the data populate automatically based off of a single table. As such, I have no named ranges currently, and would like to avoid using any if at all possible.

The extra tricky part is that I am trying to eliminate any blanks in the dropdown menu, which I have done successfully, and I have literally one last problem.

The formula I have pasted below works, but is based off of a specific column, and as such, only provides the amount of lines that column has, not the column that is identified in the earlier part of the formula from the match. I am looking to alter that "Year!$G:$G" part of the COUNTIF section of the formula to cause it to reference the specific entire column that is determined during the match process.

Any assistance would be GREATLY appreciated, as I, and all those I know are unable to crack the case on this one.

Thank you kindly in advance.

=OFFSET(Year!$A$1,1,MATCH('Special Vehicle Lookup'!$B$21,Year!$1:$1,0)-1,COUNTIF(Year!$G:$G,"*?*"),1)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Sythius

New Member
Joined
Jul 2, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I just wanted to say I managed to figure this out. It was this.

=OFFSET(Year!$A$1,1,MATCH('Special Vehicle Lookup'!$B$21,Year!1:1,0)*1,COUNTIF(INDEX(Year!$B$1:$AZ$500,0,MATCH('Special Vehicle Lookup'!$B$21,Year!1:1,0)),"*?*")-1,1)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,965
Messages
5,627,899
Members
416,281
Latest member
Olawunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top