[Akuller]

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

[Zack Barresse]

Hi there,

You either need to add a concatenated helper column or use the DGET function.

[Akuller]

Dget won't return all the criteria I need, but thanks. What type of concantenated column are you referring to?

[Zack Barresse]

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]

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,

[Zack Barresse]

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]

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

[Zack Barresse]

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]

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.

[Zack Barresse]

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]

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.

[Zack Barresse]

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]

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!

[Zack Barresse]

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]

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.