• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Data Validation Values in Table, Want to reference values from same row of table when selected|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Data Validation Values in Table, Want to reference values from same row of table when selected|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Data Validation Values in Table, Wa…
sp_PrintTopic sp_TopicIcon
Data Validation Values in Table, Want to reference values from same row of table when selected
Avatar
Jimmy A.
Member
Members
Level 0
Forum Posts: 7
Member Since:
July 17, 2019
sp_UserOfflineSmall Offline
1
July 17, 2019 - 5:49 am
sp_Permalink sp_Print

Hello all. I've been digging all around the cloud for this, and it's tough to type in a concise search phrase, so hopefully someone can help me. 

I have a worksheet with multiple tabs. One of my tabs is the main (destination) Report for my data. I also have secondary tab with a list of Retailers, including columns for Address, City, State, Zip (as headers of a Table). 

I created a dropdown list in my Primary sheet/tab for the names of each retailer. I have matching headers on the Primary sheet as the Secondary sheet (Table). 

My goal is to choose the name of the Retailer, and then each corresponding cell in that row (under each matching header of the table for that Retailer) to automatically populate in the same cells of the Primary sheet. 

In case I'm not articulating this correctly. I want to choose "Great Lakes Ace" from the dropdown in my Primary sheet, and then the cell values to reference the Table in my secondary sheet, and pull the Street Address, City, State, and Zip for that Retailer. 

I tried some IF/THEN formulas, but could only get it to work for one retailer at a time. Also, I believe you can only nest so many and retailer list is at 25+ and will continue to grow.

Any guidance here would be greatly appreciated. Thanks. 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
July 17, 2019 - 9:37 am
sp_Permalink sp_Print

Hi Jimmy,

Do you have a workbook with sample data we can see?

Regards

Phil

Avatar
Greg Beck
Member
Members
Level 0
Forum Posts: 9
Member Since:
July 5, 2019
sp_UserOfflineSmall Offline
3
July 17, 2019 - 1:15 pm
sp_Permalink sp_Print

Yes, a workbook would be helpful, but you can probably just use VLOOKUP or Index/Match to get what you want.

Avatar
Jimmy A.
Member
Members
Level 0
Forum Posts: 7
Member Since:
July 17, 2019
sp_UserOfflineSmall Offline
4
July 18, 2019 - 9:48 am
sp_Permalink sp_Print

Thanks all. Sorry, I understand the spreadsheet itself would be helpful. However, oddly enough, I tried to attach it and this platform gave me an error that it's too large (even though there's barely any data right now). 

So I'll try to upload some screenshots for reference... I appreciate any insight. Honestly, I am quite an amateur, though I have figured a few data validation formulas myself thus far. Hopefully this is child's play for you... 

The first screenshot is my Primary sheet, where I would be choosing a retailer from the dropdown I created, and then ideally columns K, L, M, N, O will populate based on values from columns B, C, D, E, F from my Table in screenshot two (Tab "GLP Retailers).

I think this is a straightforward description. But please feel free to hit me with any follow up questions. 

I really appreciate it. 

 

-JAPrimary-Sheet-Destination.jpgImage Enlarger

Source-Sheet-Table.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Primary-Sheet-Destination.jpg (55 KB)
  • sp_PlupImage Source-Sheet-Table.jpg (28 KB)
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
July 18, 2019 - 10:21 am
sp_Permalink sp_Print

Jimmy's workbook is attached below.

Avatar
Greg Beck
Member
Members
Level 0
Forum Posts: 9
Member Since:
July 5, 2019
sp_UserOfflineSmall Offline
6
July 18, 2019 - 1:02 pm
sp_Permalink sp_Print

First, I would put the data on the first sheet in a table (like it is in your last sheet).  In fact, I would put everything I possibly could in tables as they offer great advantages.

I added Index/Match formulas in the columns for the retailers address so they will populate automatically.  In a table, the formula automatically is added to all rows in a table, but you can copy down the formulas as far as you need to if you'd like.

Avatar
Jimmy A.
Member
Members
Level 0
Forum Posts: 7
Member Since:
July 17, 2019
sp_UserOfflineSmall Offline
7
July 18, 2019 - 11:19 pm
sp_Permalink sp_Print

Hi gents. Thanks for the insight/advice here. I added the Index/Match function and it works! There's just one follow up: I cannot drag down the formula to subsequent rows without so many cell numbers moving up sequentially. Is there a way I can scale this formula downward per column (K,L,M,N,O) where the reference value cell (Here it's Row J) stays static, but my Index + Match value ranges (for the table) stay the same? 

Is this what you meant by "In a table, the formula automatically is added to all rows in a table"?

Thanks so much.

Avatar
Jimmy A.
Member
Members
Level 0
Forum Posts: 7
Member Since:
July 17, 2019
sp_UserOfflineSmall Offline
8
July 19, 2019 - 12:33 pm
sp_Permalink sp_Print sp_EditHistory

Hi again gents. I am 99% of the way there. I started to create this report, but I have an issue with scaling these Index/Match formulas downward in Columns K, L, M, N, O. Some are pulling the incorrect address from the Retailer chosen in the dropdown. Some are not pulling any Data at all. Can you help? I will try to upload the current sheet as-is, and if it doesn't work, I'll ask Phil to re-upload it to the thread manually. Any help is appreciated. Thanks!

Avatar
Greg Beck
Member
Members
Level 0
Forum Posts: 9
Member Since:
July 5, 2019
sp_UserOfflineSmall Offline
9
July 19, 2019 - 12:38 pm
sp_Permalink sp_Print

I've uploaded a file where I put the data in the first sheet in a table.  I don't know if you want all the data in the same table, but you should get the idea.  As you start typing in the first empty row below the last row of the table, it will automatically expand, and the formulas will copy down automatically.

Avatar
Jimmy A.
Member
Members
Level 0
Forum Posts: 7
Member Since:
July 17, 2019
sp_UserOfflineSmall Offline
10
July 19, 2019 - 10:09 pm
sp_Permalink sp_Print

I'm sorry, I don't see an attachment to your latest response. 

Avatar
Greg Beck
Member
Members
Level 0
Forum Posts: 9
Member Since:
July 5, 2019
sp_UserOfflineSmall Offline
11
July 21, 2019 - 12:08 am
sp_Permalink sp_Print

I think I know what happened.  The file was too big and was rejected.  I deleted a lot of rows on your "Retailer Codes" tab so the size could be reduced enough to be able to upload it.  Are you able to see it now?

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
12
July 21, 2019 - 4:24 pm
sp_Permalink sp_Print sp_EditHistory

Hello,

The problem you have is due to not locking the lookup range.

In cell K3 you have this formula: =INDEX('GLP Retailers'!B4:B100,MATCH(J3,'GLP Retailers'!A4:A100,0))

In cell K4 you have this formula: =INDEX('GLP Retailers'!B5:B101,MATCH(J4,'GLP Retailers'!A5:A101,0))

In cell K8 you have this formula: =INDEX('GLP Retailers'!B4:B105,MATCH(J8,'GLP Retailers'!A9:A105,0))

As you notice the lookup range differs. Lock the cell ranges before you copy down and to the right.

In cell K2 write the formula like this: =INDEX('GLP Retailers'!B$4:B$100,MATCH($J2,'GLP Retailers'!$A$4:$A$100,0))

Or use table references: =INDEX(Table1[IMGCL_Retailer_Code],MATCH($J2,Table1[[Retailer_Name]:[Retailer_Name]],0))

Or use VLOOKUP: =VLOOKUP(Table2[@[Retailer_Name]:[Retailer_Name]],Table1,2,0) Just remember to change the column reference 2 when copying to the right.

Avatar
Jimmy A.
Member
Members
Level 0
Forum Posts: 7
Member Since:
July 17, 2019
sp_UserOfflineSmall Offline
13
July 22, 2019 - 3:33 am
sp_Permalink sp_Print sp_EditHistory

@Greg - I still was unable to see any attachments. In the meantime, I think this is solved, and I really appreciate your help. 

Avatar
Jimmy A.
Member
Members
Level 0
Forum Posts: 7
Member Since:
July 17, 2019
sp_UserOfflineSmall Offline
14
July 22, 2019 - 3:34 am
sp_Permalink sp_Print sp_EditHistory

@Anders. Thanks so much for your insight here. I chose to go with the VLOOKUP process, and it has worked out nicely. Thanks so much everyone for your help with this. I learned a lot!

sp_AnswersTopicAnswer
Answers Post
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
15
July 22, 2019 - 3:47 pm
sp_Permalink sp_Print

Hello Jimmy,

Great that it works fine for you now.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Aron Samuels
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27239

 

Member Stats:
Guest Posters: 49
Members: 31891
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.