Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Archive: Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory modem monitor motherboard network printer problem ram registry router security slow software sound toshiba trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications > Archive: Business Applications >
Solved: Excel issue re: lookups

Reply  
Thread Tools
Akuller's Avatar
Member with 115 posts.
 
Join Date: Feb 2004
Location: Northern Calif
09-May-2007, 05:32 PM #1
Solved: Excel issue re: lookups
Hi, I've been using the vlookup function for quite awhile, but have come up against a problem where I'm stumped. I have a very large spreadsheet of data that I want to link another worksheet to and only extract certain values from. The problem seems to be with the Vlookup function, no column with ascending values in which to benchmark the formula. I have no control over the spreadsheets original format (comes from outside souce). I've attached a copy of the spreadsheet. The only values I want to pull in are "1 YEAR LIBOR ALT A"; LOCK DAYS= 45,30 & 15

Let me know if there is a function I'm unaware of...

Thanks in advance
Attached Files
File Type: zip Forum test Cit.zip (94.4 KB, 19 views)
__________________
AK
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
10-May-2007, 01:18 AM #2
Hi there,

You either need to add a concatenated helper column or use the DGET function.
Akuller's Avatar
Member with 115 posts.
 
Join Date: Feb 2004
Location: Northern Calif
10-May-2007, 12:58 PM #3
Dget won't return all the criteria I need, but thanks. What type of concantenated column are you referring to?
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
10-May-2007, 01:22 PM #4
Maybe I don't understand. Are you looking to pull all records with the specified information? If so, too bad this isn't in a database, you could just run a query on it, which is what you should be doing. But, if you're doing this in Excel, you can use Autofilter. Then if you wanted to extract the data you could select the remaining visible cells and copy/paste to another area.

Can you tell us what exactly that you want to do with the data?

Re concatenated column: that was for returning a single record, which the DGET could do for you. But if that is not going to work, I wouldn't worry about the other.
Akuller's Avatar
Member with 115 posts.
 
Join Date: Feb 2004
Location: Northern Calif
10-May-2007, 01:31 PM #5
I agree in regards to the dbase suggestion...don't have any control over that unfortunately! What I'm trying to achieve is the extraction of the following pieces of data from the whole:

Rates & prices for the following lock days (15,30 & 45) for ONLY the product type, "1 YEAR LIBOR ALT A". I have been using vlookups for most of my data extraction but this spreadsheet has no benchmark, i.e. ascending order data from which to base the formula on. Also, this spreadsheet comes from an outside source and I have no control in regards to how the data may grow or shrink on a daily basis (if they no longer offer certain rates/prices). I am not the one using this on a daily basis, if I was, I'd simply link to the data and check it for movement daily, but this has to be as fool proof as possible since the user is not formula saavy, nor wants to check for accuracy.

Thanks,
__________________
AK
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
10-May-2007, 02:03 PM #6
Do you want to change this data criteria? Will it always be the same? If not, specify. Will this external data source always be in the same location? If so, is it a network location? Specify.
Akuller's Avatar
Member with 115 posts.
 
Join Date: Feb 2004
Location: Northern Calif
10-May-2007, 02:19 PM #7
Right now all I want to do is have a way to extract the specific data, then I will adjust it as necessary. The data itself will vary in rate/price daily and it will not always be in the same exact location. For example: if today the 30 lock day rate of 5.000 is in cell f22, tomorrow is may have moved to f23 or f21 etc. The external source is sending an email with this data, so I have no control over movement, that is based on market shifting. I just have to deal with how to grab only what I need..
__________________
AK
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
10-May-2007, 02:22 PM #8
I'm not worried about the row. Now if the column changed, that would be different. Is this a web-based source? Do you want to use a file picker so the user can pick the file? Or shall the user not have any control and it points to the web-based source all the time? (This would give the benefit of having current information every time.)
Akuller's Avatar
Member with 115 posts.
 
Join Date: Feb 2004
Location: Northern Calif
10-May-2007, 02:28 PM #9
No, columns will not change. Source is email of an excel file daily. User opens the email sent by source, copy's & pastes into another spreadsheet on users network which is linked to a final ratesheet (excel based). The ratesheet contains many different investors rates and is linked to about 9 different source files. I created the links from source files into ratesheet.
__________________
AK
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
10-May-2007, 02:36 PM #10
So why the email anyway? Sounds like a lot of work that you may not need to do. What is the original data source here?
Akuller's Avatar
Member with 115 posts.
 
Join Date: Feb 2004
Location: Northern Calif
10-May-2007, 02:42 PM #11
Various investors send in their ratesheets(excel based). We copy them into individual workbooks and then those workbooks (in same location as master ratesheet) are linked to master ratesheet which contains price and rate bumps to the data. It works very smoothly for all the adjusting we do to the original data, but this one has stumped me because of the data fluctuation.
__________________
AK
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
10-May-2007, 02:48 PM #12
So the location of the data is in a central 'ratesheet' workbook which every user has access to? And you're wanting the individual users to be able to extract data from this?
Akuller's Avatar
Member with 115 posts.
 
Join Date: Feb 2004
Location: Northern Calif
10-May-2007, 03:29 PM #13
I'm probably not being clear, sorry it's messy. We publish a ratesheet for our company which allows others to view our daily rates, call this Master. Behind the scenes the Master is linked to various files we pull in daily from investors. Basically we get data from many sources add hits/points and other verbiage to it. I have been able to establish links from Master to other daily ratesheets utilizing the vlookup method and it returns exactly what I need. This particular worksheet does not have anything unique for me to benchmark for a vlookup. I can Hlookup but that will only return me the top row. Right now, the link volatile, it is looking at the cell level and that adjusts daily. I'm going to do more reading up on this, I think I'm making it too complicated... Thanks!
__________________
AK
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
17-May-2007, 03:01 PM #14
Hmm, lost my post. I'll try to recap what I had posted earlier...


This should be in a database. Centralized for access, multiple users, simplicity and less maintenance for you. Plus it would be 100 (roughly) times faster, maybe more.

If you still want to do this in Excel, I'd recommend an add-in. This will give you some of the benefits of a database like structure. Everyone will have common information, settings and interface, you have one place to manage data/information and you are able to more closely control what the user can get/see.

Let us know which way you want to go.
cff2880's Avatar
Computer Specs
Junior Member with 6 posts.
 
Join Date: Apr 2008
Experience: Intermediate
20-Apr-2008, 11:24 PM #15
Akuller,

So I am looking into offering a similar service to my members, who are mtg brokers. they would greatly benefit from this kind of tool. What is the easiest way to get the major investors to send rate sheets in XML or Excel? PDF is easy, however an obvious pain to extract the data from different investors... different formats.
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 10:01 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.