[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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

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
redskye88
Asked:
redskye88
  • 4
  • 2
1 Solution
 
rspahitzCommented:
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
 
redskye88Author Commented:
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
 
rspahitzCommented:
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
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!

 
redskye88Author Commented:

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
 
rspahitzCommented:
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
 
rspahitzCommented:
ID 35009890 should be the answer if no new response from the author.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now