Copy information from another sheet to drop-down menu

[mc_mice]

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.

 

[Andrew Poulsom]

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]

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]

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]

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]

This worked great! As you said had to replace commas with semicolons but everything else worked like it should.

Thank you for the help