Solved

Excel Update info from another sheet

Posted on 2011-02-11
6
173 Views
Last Modified: 2012-05-11
I have one tab that holds my database information and another tab that references it. I need the data on the first tab to automatically fill in the columns to the left based upon the Resource and the Intr# (see spreadsheet)

 I believe       =IF(ISNA(VLOOKUP    is what I am looking for but don't know how to do it


Thanks so much
Sample.xls
0
Comment
Question by:zyanj
  • 2
  • 2
  • 2
6 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 500 total points
ID: 34875056
Here you go...please check the attached working example.

jppinto
Sample-1-.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875060
Paste this in Cell L2

=IF(ISERROR(VLOOKUP(B2,Database!A:H,2,0)),"-",VLOOKUP(B2,Database!A:H,2,0))

M2

=IF(ISERROR(VLOOKUP(B2,Database!A:H,3,0)),"-",VLOOKUP(B2,Database!A:H,3,0))

and so on. Just increase the number so next one will be 4

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34875065
xover

Sid
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 33

Expert Comment

by:jppinto
ID: 34875071
Your formula, for the first column, will be:

=IF(ISNA(VLOOKUP($B2,Database!$A$1:$H$4,2;FALSE)),"",VLOOKUP($B2,Database!$A$1:$H$4,2,FALSE))

For the next columns, you need to change the 2 for 3, 4, etc, to get the values from the corresponding columns.

jppinto
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34875075
put that in L2 and copy that formulas everywhere you need it
=VLOOKUP($B2,Database!$1:$65536,COLUMN(B1))
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34875092
i would use jppinto solution, but instead of changing the number for 2, 3, 4, etc... i would do a ref like i did to COLUMN(B1).  This way it will automatically increment as you copy the formula.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now