Data Validation List / Sort Problem?

I am using the following formula to sort data from sheet1(data) to be used on sheet3(adjustments) with a data validation list.

Cell A2 is my Property address
=IF(ROW(A1)<=ROWS(OFFSET('Data '!$A$1,1,0,COUNTA('Data '!$A:$A)-1,1)),INDEX(OFFSET('Data '!$A$1,1,0,COUNTA('Data '!$A:$A)-1,1),MOD(SMALL(CODE(LEFT(OFFSET('Data '!$N$1,1,0,COUNTA('Data '!$N:$N)-1,1),1))+(ROW(OFFSET('Data '!$A$1,1,0,COUNTA('Data '!$A:$A)-1,1))-1)/1000,ROW(A1)),1)*1000,),"")

Cell A3 Status of each property, Active, Sold, Closed etc...
=IF(ROW(B1)<=ROWS(OFFSET('Data '!$N$1,1,0,COUNTA('Data '!$N:$N)-1,1)),INDEX(OFFSET('Data '!$N$1,1,0,COUNTA('Data '!$N:$N)-1,1),MOD(SMALL(CODE(LEFT(OFFSET('Data '!$N$1,1,0,COUNTA('Data '!$N:$N)-1,1),1))+(ROW(OFFSET('Data '!$N$1,1,0,COUNTA('Data '!$N:$N)-1,1))-1)/1000,ROW(B1)),1)*1000,),"")

This is working really well thanks to help from this forum, however I ran into a bug while using the spreadhseet. I had a property that had been both sold and active.

When I go to sheet3(adjustments) and used the data validation list to select the property its only allowing the property from one status and the rest of spreadsheet is using data of that property form another status in other words:

When I go to sheet3(adjustments) I should be able to select the status of property I want to use then the next cell allows me to pick amongst those propertys that meet the status criteria and then the rest of the spreadhseet uses the following formula =VLOOKUP($D$2,vlookuptable,30,0)
to input data and make calculations.

The problem in simple english is 12 apple street is in my data twice once as a "sold" status property and once as an "active" status property, both are correct the home was sold a few months ago and is now for sale again("Active")
The problem is I am selecting status "sold" and 12 apple street populates but in the look up formulas it is giving the information for 12 apple street that has an status of "active"

If anyone had any ideas I would really appreciate the feedback .I need 12 apple street to show as both "sold" and "active " status and depending on my selection from my data validation list the look up formula should reference the correct data.

Thanks

p.s i am not entirely comfortable with how the sort formulas are working a bit over my head. I got them from someone on this forum

workbook please with examples!

well you seem to have deleted all your data from sheet data so named ranges are blank
so i have no idea what's supposed to be happening.
make up a workbook with right ,wrong,duplicate or whatever

fyi altho' 0=false in excel ,use =VLOOKUP($D$2,vlookuptable,30,false)
it makes it clearer to see whats actually being done at a glance
and
oh well here we go
you have no sheet 3 called "adjustments"

I should be able to select the status of property I want to use

how/where?
but a vlookup will only find the first "sold" so you need to make that something else now thats a harder thing!
and i cant see from your sheet what you mean
make upa sheeet with say 20 rows illustrating your problem.

yep i can see that, it may be perfectly obvious to you but what /where is looking up that info ?
what formula in what cell says get info from data! row 84/85

Originally Posted by martindwilson

yep i can see that, it may be perfectly obvious to you but what /where is looking up that info ?
what formula in what cell says get info from data! row 84/85

I think the "sort sheet is hidden you should be able to unhide it that is what sorts the data so the data validation list can be used

On the Adjustments Tempo sheet the user selects the data validation drop list (D2) and selects the status of propertys they wish to choose from then they select the property to use (D3)
D4 and down to D25 use the vlookup formula to pull over the data for the corresponding property selcted in D2

In the workbook i sent you the selcted property happens to be the one that has both a "sold" status and "Pending" status,
The problem is that it comes across the data validation drop list as a "pending" but it pulls the data from the "sold" list on the data sheet. because as you said vlookup stops as soon as it finds the property.

i sort of see but its too much reverse engineering for me to see how this was all put together.do you know who originally helped you out? they may see somthing i'm clearly missing,sorry!

ANYONE ELSE HERE HELP?

Working backwards....

If we take Sheet DATA I would do the following:

AF1: KEY
AF2: =IF($A2="","",$N2&":"&COUNTIF($N$2:$N2,$N2))
copied down to AF300

This simple approaches gives you a nice quick way to identify various properties without the need for expensive arrays later on...

Insert new sheet - let's call it "DNR"... on DNR sheet:

A1: Active
B1: Backup Offers
C1: Closed Sale
D1: Pending Sale

A2: =COUNTIF('Data '!$N:$N,A$1)
copied to B2:D2
(this shows how many properties are in each "bucket")

Now let's create new Named Ranges

Name: _Active
RefersTo: =OFFSET(DNR!$A$3,,,DNR!$A$2,1)

Name: _Backup_Offers
RefersTo: =OFFSET(DNR!$B$3,,,DNR!$B$2,1)

Name: _Closed_Sale
RefersTo: =OFFSET(DNR!$C$3,,,DNR!$C$2,1)

Name: _Pending_Sale
RefersTo: =OFFSET(DNR!$D$3,,,DNR!$D$2,1)

Now on to "Adjustment Tempo" sheet ... revise the Validation sources as follows:

D1: =DNR!$A$1:$D$1

D2: =IF(D1="Active",_Active,IF(D1="Backup Offers",_Backup_Offers,IF(D1="Closed Sale",_Closed_Sale,_Pending_Sale)))

So as D1 is altered so D2 listing updates... re: your issue with Status, why not simply tie to D1 ?

D6: =D1

You know if the property is selected in theory it should be the same as D1, no ?

Does that help ?

... and having just re-read it would appear the issue is not related to the Validation at all ... that said I would still advocate the above over the existing as I think it's far simpler and importantly the addition of KEY column to Data sheet is what will resolve your fundamental issues...

So continuing from above... on Adjustment Tempo sheet:

E1:
=D1&":"&MATCH(D2,INDEX(DNR!$A$3:INDEX(DNR!$D:$D,MAX(DNR!$A$2:$D$2)+2),0,MATCH($D$1,DNR!$A$1:$D$1,0)),0)

The above will return the Unique Key value for chosen property... use this as basis for retrieving all other info as it is a unique value on Data sheet... col AF, eg - let's select "Pending Sale" in D1 and subsequently "19701 Hatton St" as our property given we know 2 records exist...

Using the original MLS# formula:

D3: =VLOOKUP(D$2,vlookuptable,23,0)

we know returns the number for the Closed Sale instance irrespective of choice in D1 (ie not the Pending Sale value) ... using our unique value as stored in E1

D3: =INDEX('Data '!$W:$W,MATCH($E$1,'Data '!$AF:$AF,0))

returns the correct value... the same logic is applied elsewhere...

NOTE: the MATCH of E1 is a repetitive calculation, that is to say the value will be used repeatedly without result altering so rather than recalculating the same value over and over I would advise storing the result of the MATCH in another cell and then referring to said cell in all other calcs (ie calculate only once).

ok I think i got most of it, When i revise the data validation list on adjustment page i get an error message that i can reference other worsksheets or workbooks
I am slo a little confused about the E1 storing? the key formula works and i added the DNR sheet and that seems to be working, although i am not quite sure about the name ranges i am doubling checking

Billboy, see the attached work-up in which all is in place... though note I have only entered the formula into D3 on Adjustment Tempo sheet... the others all need to be altered in the same manner (ie utilising value in E1 and column AF on Data sheet)

You can copy the cell and paste special Validation only to the other cells... I can't recall off-hand how we set up the cell references in the existing Validation cell but if not absolute they should alter appropriately (just check each cell once you've pasted the validation rules).

Originally Posted by Billyboy

I cant thank you enough, this is a better way of doing what I needed done, compared to an array formula.
I see on the data sheet you added drop down lists to sort the data. I am a bit embarrased but how was those created and if i cut paste new data into that shet will those lists be deleted.

The way the sheet works now is the users pastes the data into that sheet from another sheet.

Maybe there is a better way by referencing the sheet that has the data

The dropdowns on Data are the result of applying an Auto Filter against the data set, see: http://www.contextures.com/xlautofilter01.html

To be honest I added them only so I could quickly filter the records discussed (those in yellow) so I could quickly check to see if the revised formulae etc were working correctly.

Regards collating the datasheet itself, we might be able to help on that front but we would need more info with regard to where the data is presently stored etc ie from where it is copied and whether or not there is any underlying that dictates when the data is copied.

Martin, mpoas ? I don't know that acronym - I hope it's not offensive

donkeyote has just been awarded mpoas!