Solved

How can I populate a master form with data from another sheet with Hlookup?

Posted on 2011-02-28
9
180 Views
Last Modified: 2012-05-11
I have a master sheet calloed Users and Their User Groups.  Col A is their userid and Col B is their assigned User Group. Then there is a blank form with entried for access levels and rights.
My other sheets look just like the blank one but filled in.  I want to be able to Type in the user group I want to see and have it populated with the corresponding values from the worksheet it matches.  Any help would be greatly appreciated.  I have been trying to use HLOOKUP but can only get one col populated
Copy-of-allusers--version-1-.xls
0
Comment
Question by:redskye88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
9 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35003067
Since your data is in a column, you might want to try VLOOKUP:

VLOOKUP(value, datablockrange, offsetcolumn-2, FALSE)

(FALSE at the end if data is not sorted, otherwise TRUE should be fine as long as you know the value is in the list.)
0
 

Author Comment

by:redskye88
ID: 35009081
Thanks rspahitz for response!

I'm still having an issue tho.  If I highlight from C6 on the Credit Admin Access worksheet to all the data and call it DataRange2
  and then go to main worksheet and put this formula in E6 (to point what value I type in E5
  VLOOKUP(E5,DataRange2,-2, false)  I get a #N/A.  Am I missing your logic?

Thanks!
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35009365
Hmmm...I don't think it lets you put in a negative value for the 3rd parameter.  The VLOOKUP likes to have the lookup value in column 1 of the DataRange, then you need an offset to the right (where 1 is the first column, etc.)

I don't see "DataRange2" in yoru attached workbook so I'm not sure what it refers to.  However, I think you're trying to do this:

Use E5 to look down column A and return what's in column B.  If so, try this:

=VLOOKUP(E5,A:B,2,FALSE)

This would go in a cell like F5.

In addition, to make this more user-friendly, you can use DataValidation on cell E5 to create a dropdown list based on the choices in column A.  Let me know if you wan to try that and need help.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:redskye88
ID: 35009769

Thanks so much for your help but I'm still getting the #N/A error.  I think your idea on the dropdown is EXCELLENT!  Yes, I would like that!  I attach an updated version of my work sheet with the datarange2 in the3 Credit Admin Access worksheet and the formula I'm trying to use.

Thank you!!!
Copy-of-allusers--version-1-.xls
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 35009890
I'm not sure you quite understand how the H/VLOOKUP work.  They assume that the data is organized like a grid:

User  Group  File
User1 0002 CreditAdmin
User2 0002 Credit Admin
User3 0002 Credit Admin
User4 0002 Credit Admin with Img
etc.

then it will look down through the users and return either the user, group or file.

It seems that you want it to jump to large blocks of data (rather than a grid of data.)

So the first thing I would suggest is to build the grid on a new sheet.  After that you can have all the other pieces reference it as needed.  From that, the selection will be easy, the vlookup will be easy, then you can use that information to pull out other things from the various blocks (with a bit more effort.)
So add a new sheet and call it something like GroupData and add the groups down column A and their corresponding file rooms down column B (and institution down column C, etc, if desired)
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35317155
ID 35009890 should be the answer if no new response from the author.
0
 
LVL 50
ID: 35744600
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question