Copy information from another sheet to drop-down menu

mc_mice

New Member
Joined
Dec 20, 2005
Messages
6
Hi,

I would like to copy information from another sheet to drop-down menu in another sheet. It seems that I cannot choose source from another sheet and reference it in any way.

If possible the copying could be dynamic/automatic, so that when there is additions to the source, drop-down menu is updated also.

Thanks in advance.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If your dropdown is from Data Validation, name the list eg MyList using Insert|Name|Define. Then you can use:

=MyList

in the DV Source box.

Us a dynamic range reference if you want the list to be updated automatically when items are added.
 

mc_mice

New Member
Joined
Dec 20, 2005
Messages
6
This worked great.

But... Now I have to it so that implement this to the following :

=SUM((COUNTIF(INDIRECT("sheet1!" & A1 & "3:" & A1 & "10");"1"));COUNTIF(INDIRECT("sheet1!" & A1 & "3:" & A1 & "10");"0"))

so that when I choose from drop-down menu first line, it will iplement to the previous formula the right column where it will count..

Thanks again
 

mc_mice

New Member
Joined
Dec 20, 2005
Messages
6
I noticed that I didn´t inform my problem correctly.

- I have 1 pull-down menu that takes information from another sheet
- I have numerous formulas that count the statistics from that same sheet

I would like to have so that when I choose from the pull-down menu some heading, the formulas will automatically change to calculate the statistics from that same column the heading was taken.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Please give an example of what appears in your list, how you would want that to be used in a formula.

In the meantime, I will try to guess what you are trying to do.

Suppose that A2:C10 on Sheet1 contains:

France Germany USA
Data Data Data
Data Data Data
Data Data Data
Data Data Data
Data Data Data
Data Data Data
Data Data Data
Data Data Data

and that A1 on some other sheet contains Data Validation with the list France, Germany and USA. This formula:

=SUM(INDEX(Sheet1!A3:C10,0,MATCH(A1,Sheet1!A2:C2,FALSE)))

will sum the values in the column for Germany. That's because setting the row_num or column_num arguments to zero results in INDEX returning the array of values for the entire column or row.

So the formula you posted could be written like this:

=SUM(COUNTIF(INDEX(Sheet1!A3:C10,0,MATCH(A1,Sheet1!A2:C2,FALSE)),"1"),COUNTIF(INDEX(Sheet1!A3:C10,0,MATCH(A1,Sheet1!A2:C2,FALSE)),"0"))

Note that my formulas use the comma as the argument separator and you will need to change them to semicolons.
 

mc_mice

New Member
Joined
Dec 20, 2005
Messages
6
This worked great! As you said had to replace commas with semicolons but everything else worked like it should.

Thank you for the help
 

Watch MrExcel Video

Forum statistics

Threads
1,118,291
Messages
5,571,331
Members
412,382
Latest member
Langtn02
Top