Select Table Range based on cell in another sheet

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121
Hey Guys, back again.
I have what I know is very simple but for some reason I am just drawing a blank today.

Basically I am tryiing to select a column range in a table based on the value of a cell in another sheet.

So I am trying to replace the value of "Replace-Me" in the code below to get the value of sheet OHR cell F2.

VBA Code:
Range("BaseTable[REPLACE-ME]").Select

in other words if sheet OHR cell F2 value is miscellaneaus1 then the code would get that value as the table column to select

Thanks in advance, this is my favorite message board and yall are always so awesome.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Range("BaseTable[" & sheets("OHR").range("F2").value & "]").Select
 

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121
How about
VBA Code:
Range("BaseTable[" & sheets("OHR").range("F2").value & "]").Select
Still erroring out, I thought for sure that was it
 

Attachments

  • 2020-09-28_08h58_16.png
    2020-09-28_08h58_16.png
    17.1 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
What line gives the error?
 

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121

ADVERTISEMENT

The one we are trying to get to work,
 

Attachments

  • 2020-09-28_09h05_23.png
    2020-09-28_09h05_23.png
    6 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Is BaseTable a proper table, rather than a range?
If so, is it on the Remote Basses sheet?
 

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121

ADVERTISEMENT

Yep, Proper Table and the sheet name it resides on is "Remote Bases" and the range that I am trying to reference for it is on the sheet "OHR"
 

Attachments

  • 2020-09-28_09h09_36.png
    2020-09-28_09h09_36.png
    5.3 KB · Views: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
In that case check for typos between F2 & the actual column headers, along with leading/trailing spaces.
 

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121
In that case check for typos between F2 & the actual column headers, along with leading/trailing spaces.
LOL, OMGosh I just made that so much harder than it needed to be............


F1 NOT F2

Now it works, thanks Fluff!!!!!!!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,976
Messages
5,599,143
Members
414,292
Latest member
kingshuk963

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