[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-02-28
9
Medium Priority
?
187 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

649 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