Solved

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

Posted on 2011-02-28
9
176 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Expert Comment

by:Ingeborg Hawighorst
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

730 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