Solved

Excel data linking between sprdsheets based on values

Posted on 2013-01-04
4
183 Views
Last Modified: 2013-01-24
Hi!  
I have one Excel 10 spreadsheet (SPRD1) with context specific values which are CPT codes (5-digit numeric).  A have another sprd2 which contains tens of thousands of these CPT codes, with corresponding values called RVU.
What I would like to do is tell SPRD1 that when a specific CPT is used "12345", to go to SPRD2, find the row which contains "12345", and place the corresponding RVU in the assigned cell in SPRD1....

I have no idea how to do this... I've googled a fair amount and found similar questions, but the answers are very specific to the context and data that I've been unable to extract a formula....  

Is that enough info to get me started?  Thanks in advance!
0
Comment
Question by:SonicVoom
[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
  • 2
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38745776
You probably want either VLOOKUP, or INDEX/MATCH.

This article will help with VLOOKUP syntax: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2637-Six-Reasons-Why-Your-VLOOKUP-or-HLOOKUP-Formula-Does-Not-Work.html

Not having a sample file I can only guess at your requirements, but assuming:
"SPRD2" is another worksheet, and that it has CPT codes in Col A and RVU in Col B
On the SPRD1 worksheet, your current CPT code is in Col A, starting with A2

Then a formula like this should work:

=VLOOKUP(A2,SPRD2!A:B,2,FALSE)
0
 
LVL 2

Author Comment

by:SonicVoom
ID: 38745836
Wow, THANK YOU!  I messed with it but I still I didn't get it to work (learned a small heap in the process though).

SPRD1 can now be called "Berry"
SPRD2 can now be called "pfr2006"

CPT in "pfr2006" is (now) in column A (it appears this is a must), and the corresponding RVU is in column I.  This list is in numerical order.

CPT in "Berry" is in column E, and RVU is column P...The correspinding RVU from ["pfr2006", column I] is the value I want to add to ["berry", column P], based on the CPT used.  This list is not in order.

I hope that a formula exists that can be placed down the column of a 150 page spreadsheet which would save having to go back and forth manually entering the RVU that goes with each CPT in "berry"...  Possible?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38746070
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 38754477
Not for points:

The vlookup does not HAVE to start in column A but the lookup value has to be in the leftmost column of the range. The offset is then the number of the column in the range.

So a range of A:I with an offset of 9 would return contents of column I, likewise a range of C:M for example with an offset of 9 would return contents of column K.

Thanks
Rob H
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

749 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