select from a list

**eran3185** · 01-17-2010, 01:20 AM

hi

i have 2 lists (letters-h2:h4 & numbers-g2:g4)
i need to select number or letter (e2) , then i need to select from the list (b2).
i also want to use a dynamic range (h2:h4) with "offset" , but its not work

what can i do ???

**DonkeyOte** · 01-17-2010, 03:15 AM

If you make letters/numbers dynamic ranges you will need to use a CHOOSE/IF based construct for B2 - you can not use INDIRECT to select Dynamic Ranges.

So:

Numbers
RefersTo: =OFFSET(Sheet1!$G$2,,,COUNTA(Sheet1!$G:$G)-COUNTA(Sheet1!$G$1),1)

Letters:
RefersTo: =OFFSET(Sheet1!$H$2,,,COUNTA(Sheet1!$H:$H)-COUNTA(Sheet1!$H$1),1)

Then

B2 - Data Validation List:
Source: =IF($D2="Letters",Letters,IF($D2="Numbers",Numbers,""))

**DonkeyOte** · 01-17-2010, 03:56 AM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!