Dynamic Range formula Error

[ddrace]

Good day,

I would like to create a formula that automatically updates a subset of a list as items are added or removed from the main source. On the main data worksheet, I input loan information for all loans. For the subset, I would like a dynamic list that automatically pulls loans that meet certain criteria. In this example, I want to pull all loans that are 30 year mortgages. I have the current formula:

=INDEX(OFFSET(INDIRECT(ADDRESS(MATCH(B25,'Pending Loans'!A:A,0),1)),1,0):!A1000,MATCH(K24,OFFSET(INDIRECT(ADDRESS(MATCH(B25,'Pending Loans'!A:A,0),1)),1,4):E1000,0))

Ultimately, this is an index-match formula. The index-offset-indirect- address formulas are the dynamic part. Since there are multiple 30 year mortgages, a simple index match formula would only pull the first loan number. The offset references the previous loan number pulled and changes the array to index-match starting at the cell below.

The issue is that this formula is on tab "30 year fixed", referencing tab "Pending Loans". In testing the formula, everything works fine if all the info is on the same page. However, while installing the formula on the "30 year fixed" tab, the respective "A1000" and "E1000" references look on the incorrect tab. I would like help on how to get these two parts of the formula to reference the appropriate tab. Placing 'Pending Loans'! in front doesn't seem to work

I cannot post examples or screenshots because of sensitive information.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting). There is NO NEED to post your real sheet. Mock up a sample without real names, or replace them with "Mickey Mouse", etc.

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[ddrace]

I have uploaded a simple example.

I thought about it more last night. The issue is that I just need to be able to correctly reference a range of cells where the beginning cell in the range is determined by a formula. The issue as it stands is that I've typed the cell address as this:

OFFSET(INDIRECT(ADDRESS(MATCH(B25,'Pending Loans'!A:A,0),1)),1,0):A1000

But instead of calculating as A5:A1000, as an example, the formula refers to A5 and A1000 individually. I think you will see this on the example.

[JohnTopley]

Try

=IFERROR(INDEX('Pending Loans'!A$1:A$1000,SMALL(IF('Pending Loans'!$B$1:$B$1000="30yrfx",ROW('Pending Loans'!A$1:A$1000)-ROW($A$1)+1,""),ROWS($A$1:A1))),"")


Enter with Ctrl+Shift+Enter

[ddrace]

That worked. Thanks, John. I don't understand exactly how the formula is working just yet, but I greatly appreciate your help.

[JohnTopley]

Thank you for the feedback and rep.

FYI: the formula Is used is a common one for "extracting" data based on criteria so it will be worth the effort to try and understand how it works.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.