Transfer data from drop down box to a different sheet

[monkeytennis]

Hi everyone,

It's that time when I need help again and things start to get too complicated for me.

As the title says I want to move information (cumulative) that is selected from a drop down box in sheet 1 column A to a column in sheet 2.

If I select a geographical county in sheet 1 cell A1 via a drop down box of say 50 counties and then a different county is selected in cell A2 and so on, I want to see the cumulative result of that action in sheet 2 . In sheet 2 all of the 50 counties are detailed alphabetically in individual cells in column A. I need the cumulative (and running) total of each county (referenced from sheet 1) to appear by its respective county detailed in sheet 2 column A and to appear in sheet 2 column B.

I hope someone can understand what I am trying to do. I'm sure its possible but this is a bridge too far for me,

Thanks in advance.

 

[GlennUK]

You have not described what numbers are to be added. Or how the cumulation show be handled exactly? Describe the logic of what you want to happen.

 

[monkeytennis]

Thanks for the reply. I can't upload the attachment so its more difficult to explain but I'll give it a go.

In sheet 1 Column A (and for every row of data) I select data from a drop down box. In this instance it happens to be 48 English Counties.

So In sheet 1 Column A row 1 I select Derbyshire from the drop down of the 48 counties. In row two it may be Yorkshire and so on. Sometimes I am choosing the same county as that in a previous row.

I want to see the ever increasing (we are continually adding new data to the sheet) cumulative figure ( I could have picked Derbyshire 10 times and Yorkshire 20 times) of the selections appear against the respective county name (which would be listed alphabetically in sheet 2 Column A) in sheet 2 column B.

This would save us having to manually update the second sheet each time. The purpose of this is so that we can chart enquires and value from each of the counties to see which we need to develop further.

I hope you understand this better.

Thanks

 

[GlennUK]

So, tell me if I'm wrong, but isn't that just doing a COUNTIF in the second sheet, of what is entered in the first sheet Column A?

 

[monkeytennis]

I'm not sure. I've used Excel for years but its mostly for data storage rather than analysis. I can do the odd basic formula but this is too complicated for those that aren't proficient users as I have asked a number of friends and collagues and they don't have a clue either.

Thanks

 

[GlennUK]

Like:

=COUNTIF(Sheet1!A:A,"Derbyshire")

... you could replace "Derbyshire" with a cell reference.

 

[monkeytennis]

Thanks. I managed to do this but I have no idea how to include the remaining counties within the same formula without creating an individual formula (that references the specific county) in each cell of Sheet 2 column B. Usually you can drag a formula down the cells but not in this instance.

Can it be done without the indivdual formulas?

Thanks

 

[GlennUK]

If you want to see the count appear next to the county in sheet 2, then change that formula so that the county criteria is the cell reference containing the county. And copy it down.

 

[monkeytennis]

I have tried that and it returns zero in each case. It only appears to show the count if I use the county like initially suggested. The cell reference doesnt work but is that because there are multiple choices to pick from the drop down?

 

[GlennUK]

If the cell referenced has the county, it will work exactly the same as initially suggested.

=COUNTIF(Sheet1!A:A,A2)

( in Sheet2 ).