Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2011-02-28
9
Medium Priority
?
193 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
  • 4
  • 2
7 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 

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 2000 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

606 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