linking a cell containing text to another cell containing text / data

[gin2000]

Hi, I am looking for a formula to link a cell containing text to another cell containing text / data.

Example: I have a list of 'countries' in Cell A1 (using data validation - lists). In cell B1 I want to have the 'number of states per country' automatically populate (corresponding to the country selected in cell A1). Naturally, I will have to have a separate table somewhere on the page that links the 2 fields.

My question is 'how do i go about linking the 2 fields?

Any help is much appreciated.
Gin

[Ardus Petus]

You could re-use your validation list
Say your countries are in D1:D99
In E1:E99, enter the number of states of the country to the left

In B1, enter formula:
=VLOOKUP(A1,D1:E99,0)

HTH
--
AP

"gin2000" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi, I am looking for a formula to link a cell containing text to another
> cell containing text / data.
>
> Example: I have a list of 'countries' in Cell A1 (using data validation
> - lists). In cell B1 I want to have the 'number of states per country'
> automatically populate (corresponding to the country selected in cell
> A1). Naturally, I will have to have a separate table somewhere on the
> page that links the 2 fields.
>
> My question is 'how do i go about linking the 2 fields?
>
> Any help is much appreciated.
> Gin
>
>
> --
> gin2000
> ------------------------------------------------------------------------
> gin2000's Profile:
> http://www.excelforum.com/member.php...o&userid=34606
> View this thread: http://www.excelforum.com/showthread...hreadid=543774
>

[gin2000]

Hi AP,

Thanks for your response. I tried what you suggested but was not successful. I was wondering if you could set up your formula in the attached sheet.
Thanks again
Gin

[Ardus Petus]

Why did you embed your workbook in a Word document?

Here is the corrected workbook: http://cjoint.com/?ftusOqmrk2

HTH
--
AP

"gin2000" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Hi AP,
>
> Thanks for your response. I tried what you suggested but was not
> successful. I was wondering if you could set up your formula in the
> attached sheet.
> Thanks again
> Gin
>
>
> +-------------------------------------------------------------------+
> |Filename: Countries.doc |
> |Download: http://www.excelforum.com/attachment.php?postid=4791 |
> +-------------------------------------------------------------------+
>
> --
> gin2000
> ------------------------------------------------------------------------
> gin2000's Profile:
> http://www.excelforum.com/member.php...o&userid=34606
> View this thread: http://www.excelforum.com/showthread...hreadid=543774
>

[gin2000]

Thanks AP it worked!

I couldn't attach an excel file so that's why I embedded it

[Ardus Petus]

You'd rather zip it.

Cheers,
--
AP

"gin2000" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Thanks AP it worked!
>
> I couldn't attach an excel file so that's why I embedded it
>
>
> --
> gin2000
> ------------------------------------------------------------------------
> gin2000's Profile:
> http://www.excelforum.com/member.php...o&userid=34606
> View this thread: http://www.excelforum.com/showthread...hreadid=543774
>