• Welcome to AppraisersForum.com, the premier online  community for the discussion of real estate appraisal. Register a free account to be able to post and unlock additional forums and features.

Forum Sponsor - a la mode

Learn more about Titan Reports, our new cloud-based formfiller, SmartExchange, Titan Office, and Titan Drive to see how you can revolutionize your appraisal business.

Excel pro needed.

Status
Not open for further replies.

Bill_FL

Thread Starter
Senior Member
Joined
Aug 23, 2002
Professional Status
Certified General Appraiser
State
Florida
OK, Here is what I am trying to do. I want to have multiple drop lists that renturn to one cell, based on the entry in another cell.

Lets say that I have created three lists, we will call them :SFR, Land, and Commercial.

Now lets say, that in cell E9 I have a list called "Appraiser". That list has the names of 3 people, Bill, Bob and Joe. If from that list I select "Bill", then in cell c10, I want the drop list called "SFR" to return, if I have selected "Bob" I want the drop list called "Land" to return, and if I have selected "Joe" I want the drop list to called "Commercial" to return.


I can do lists, and have written some pretty complex lookups and if formulas, but I can not figure out how to get it to return a list instead of just a single entry. Any ideas?
 

JonGalt

Junior Member
Joined
Aug 4, 2005
Professional Status
Certified Residential Appraiser
State
Illinois
I'm not sure exactly what you're trying to do, but then I haven't had my first cup of coffee yet. I used to make some pretty complex spreadsheets with diificult coding and formulas. I would recommend using a copy of the "Excel Bible" for guidance, and then a visit to the "Mr Excel" message forum. I used to go to the Mr Excel forum to get help, your answer may already be there.
 

Bill_FL

Thread Starter
Senior Member
Joined
Aug 23, 2002
Professional Status
Certified General Appraiser
State
Florida
Jon,

Ok, let say that in cell A1, I have created a drop list that has the choices "Bill" or "Jon"


Now, I have created 2 other lists, one I will call List1, the other List2. In cell C1, I want List1 or List2 to appear, depending if I selected Bill or Jon in A1.

Its relatively simple to write an IF formula to return a preset singluar value or text string based on what is selected in A1. What I can not figure our is how to make it select another list from a choice of mulitple lists.
 

Howard Klahr

Senior Member
Joined
Oct 4, 2004
Professional Status
Certified General Appraiser
State
Florida
Check out the Choose function. You would need to use additional functions to return the input info for the choose funtion but this function works with a list of items
 

Smokehouse

Junior Member
Joined
May 7, 2003
Professional Status
Certified Residential Appraiser
State
Florida
http://www.mrexcel.com/forum/index.php

Try that link. This guy does excel podcasts and this link is to his message board. Post your question there I am sure someone will be able to help you. I have watched several of his podcasts and he knows excel inside and out!
 

Alan Gertner

Member
Joined
Nov 1, 2002
Professional Status
Certified Residential Appraiser
State
Texas
Check out the following functions:

INDIRECT()

INDEX()

VLOOKUP()

MATCH()
 

deturner

Senior Member
Joined
Apr 2, 2003
Professional Status
Certified Residential Appraiser
State
Kansas
You may have issues doing this in Excel. I recommend Access. It makes it a lot easier to manipulate the data but I'll work on it some and see if I can come up with a solution.

I worked for IBM for a few years supporting users with all the MS Office products and specialized in MS Access.

DaveT in NC
 

RemelD

Freshman Member
Joined
Nov 23, 2007
Professional Status
Real Estate Agent or Broker
State
Illinois
Bill,

I understand what you need and I can help you with this. I just created a similar solution for a client of mine.

I just need a little more information.

1. Can you give me more information about the purpose of the spreadsheet. Are you using it for tracking purposes; to create reports; etc? Do you need to store the data you've selected for retrieval at another date?


2. You stated that if you choose "Bill" in one drop down then you want the SFR drop down to "return". Please tell me what items you want to be in the SFR drop down. Likewise, what choices do you want in the Land and Commercial drop downs.

You can PM me to discuss this more.
 

Alan Gertner

Member
Joined
Nov 1, 2002
Professional Status
Certified Residential Appraiser
State
Texas
Step 1. Create the lists.

AppraiserL Bill, Bob, Jane
SFRL WoodHouse, BrickHouse, StrawHouse
LandL Lot, Acreage, Waterfront
CommercialL QuickieStop, LubeShop, Warehouse

Note: The number of entries in the AppraiserL must match the number of property type lists.
Note: The AppraiserL entries must be unique.

Step 2. Create a list of the Property type lists.

ProperTypeL SFRL, LandL, CommerciaL


Step 3. Create a drop down menu for the SelectedAppraiser cell.

Data -> Validations -> Settings -> Allow -> List
Data -> Validations -> Settings -> Source -> “=AppraiserL”


Step 4. Create a SelectedAppraiserIndex cell to hold the AppraiserL selection index.

= MATCH(SelectedAppraiser, INDEX(AppraiserL,1:1,1:1 ), 0)


Step 4. Create a SelectedPropertyType cell which identifies the corresponding PropertyTypeL reference.

=INDEX(PropertyL,1:1,SelectedAppraiserIndex)


Step 5. Create the property type cell with a drop down menu which uses an indirect reference to obtain the menu values.

Data -> Validations -> Settings -> Allow -> List
Data -> Validations -> Settings -> Source -> “= INDIRECT(SelectedPropertyType)”

The steps can be combined to shorten them. I broke them out for clarity.

I’m sure there are other methods to accomplish the same thing.

Bueno suerto.
 
Status
Not open for further replies.
Find a Real Estate Appraiser - Enter Zip Code

Copyright © 2000-, AppraisersForum.com, All Rights Reserved
AppraisersForum.com is proudly hosted by the folks at
AppraiserSites.com
Top

AdBlock Detected

We get it, advertisements are annoying!

Sure, ad-blocking software does a great job at blocking ads, but it also blocks useful features of our website. For the best site experience please disable your AdBlocker.

I've Disabled AdBlock
No Thanks