Find sum in other sheet based on name and last used cell in column

HI I am really not very good to formulaes better to macroes, but i would like some to help me with this.
I have in Sheet Middle Result All 4 columns from A to D.
There is a header in column A in this case Denmark. the next form its United Kingdom
I need to base a sum formula in other sheet on the name column A but from the range in first cell in column D to last used cell
I need it this way because the range can be variable, and the only i can hold on is the name first cell column A and last used row after this in column D.
And i can change the name so it takes the next from example United Kingdom

Formel inside sheet Middle Result 1 example cell B12.


Please have a look at below test sheet

Sincerely
Abjac

Hi FDibbons and thanks for the answer.
I basically just want to have a formula in cell B12 sheet Middle result 1, which calculate the sum of the numbers in column D
MIddle Result All.
The special in it is that the range can be variant. Now it goes from D2:D32. But it could also be shorter or longer.
Depends of the output.
So thats why i need the formula to take the range from Column A Denmark to the last used cell in column D.
Because this is the only thing i have which will be common.
Next time i get this info. Maybe the range is much shorter.

I cant add anything in the sheet Middle Result All. Its filled out automatically. So i need the code only to be in cell B12 Middle Result 1. In this sheet i can add a help column, if its nessasary.
Please try to download my file, will show more i think.
I tried also just to see your code, but some of it gave an error, could be some about i use exel 2003 i think.

Please have a look

Sincerely

Abjac

HI Ace_Xl. I tried it but nothing came out only the code stands in the cell where i put it. Is it possible for you to download my test sheet and try to play with it there and upload that, if you find a solution?

Sincerely

Abjac

HI Oeldere. The main reason for this is that it should go automatic.
There is many other countries to fill out, and for sure it can work, but the sheet i have here is only made by a macro. And from that i put values in to another sheet.
So thats the main reason. To avoid i have to make allot of changes in this sheet, everytime i get the date there.

The other i hope can be done.

So give it a shot if you have time.
Sincerely

Abjac

Also posted here but more based on a macro solution.

http://www.mrexcel.com/forum/excel-q...ml#post3497367

HI Oeldere. I just wanted to explain what i need, but dont mind to share i for sure.So sorry for that
Yes i looked at your solution with the pivot table. It can be used i think.
But i think i need a macro to fill out the country names.
And i need the pivot table to be in sheet Sheet Middle Result1 column Z. And i only need the countries and the summary for each country.

What i mean with that it can change is that the sheet we take these info from.
It comes from a macro runned over a mastersheet.
Next time i shall use it the range can be different, with example less for denmark and more for United Kingdom.

Thats why i need a solution where i consider that this range can change.

But if it can be done like i explained, i think it will fullfill that,
I just have to admit i dont know much about pivot tables.
So if you have time, that solution would be great.

Sincerely
Abjac

I have found out of delete blank rows and also put the names for the countries. With macroes
So all i need is a formula or better a macro which can count all numbers in column d, where the name in column A i Denmark.
And the same for United Kingdom
And other countries which will be there in the real sheet.
My code is not nice but working.
Please see i some can help with a macro for the last.
I would like a macro to display the sumresult for denmark in a certain specified column in Sheet Middle Result1 in cell B12 example.
Please see if some can help with the rest.

My code for delete blank rows and fill in country names is like below.

Sincerely
Abjac

Please Login or Register  to view this content.

HI Oeldere.
If you read my upper post the code to delete blanks and put then names in to the country all the way down, i made that yes-
But i never heard anything from how to continue with the pivot table. The fact is i dont know about pivot tables so i am a little lost on that to be honest.
So after not have heard anything, i thought maybe better to try with a macro for the ramining. So yes i made a new thread, because this one is posted under formulas. So i made the new thread under VBA, where i think it belong, which i also explain.
And the cross posting i off course after the rules post here.
So yes one posting to say i cross posted.
And one which was another question, but related yes. That one was solved by Rick Rothstein
This one where i shifted to VBA catagory, because i though if i want to have a macro, it belong there.
And the last here in VBA where it belong.
The fact is i should have this finish for tomorrow, but i dont think it will.So a little pressed with this one.

Your input lead me in the right direction i think. I have deleted the blank rows and also put the names in with macro in column A.

And i could use a pivot table, but also i think now it should be possible to do it with a macro.
Special when the country is in same row as the numbers.

I would like the pivot table in another sheet yes. Because this is just a middle result. This middle result is created by a macro from a mastersheet.
And therefor its not so smart to change anything in that sheet.
The hole idea is of course that i can use this project again and again.
So i dont have the next time to change codes and formulaes, when the range will change.

Dont know if i have explained it ok. But i think and also i have uploaded testsheets to look at, so i guess this should be ok.
Sorry if it have made any confusion.

Sincerely
Abjac

Hi Have been solved in the new thread i made to make a macro. But the solution i got is only with a formula. So is really great
Will mark this as solved also with reference to the below thread

http://www.excelforum.com/excel-prog...er-column.html

Sincerely
Abjac