paste formula in excel 2010 1

[tonyvee1973]

Hi Guys
Really need some help as this is driving me crazy.

I have a spreadsheet (see attached) and trying to do the following:

Look at Acc no (column A) and Code (Column B) in sheet 2.
If they match values in Acc no (Column A) and Code (Column B) in sheet 1 then paste contents of column C from sheet 2 into sheet 1 (In the adjacent column)

If anyone can help this would be amazing!

Thanks as always
Tony

 

[SkipVought]

Hi,

Some of us are restricted from downloading files by company security.

If you want help. I would suggest also posting your example here in plain text.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Taking a wild guess, try setting the number format of the cells containing formulas, to GENERAL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Ok, sorry about the attachment, here is what it looks like in plain text below.

Sheet 1:

Col A Col B Col C Col D Col E
2 Skegness 36000 21 (For Formula)
2 Skegness 36002 0
2 Skegness 36003A 7
2 Skegness 36005 9
2 Skegness 36006 1
2 Skegness 36007 16
2 Skegness 36008 12
2 Skegness 36009 0


Sheet 2:
Col A Col B Col C
1 36000 6
1 36003 5
1 36005 7
1 36042 1
2 36000 2
2 36012 1
2 36030 4
2 36039 1
2 36040 1
2 36042 1
3 36005 1
3 36007 2
3 36030 6
3 40840 3
4 36000 9
4 36003 16
4 36005 3


Thanks again

 

[Gruuuu]

in 2007 and later, you can use SUMIFS:

[tt]
=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,$A1,Sheet2!$B:$B,$C1)
[/tt]

And the only Account/Code combination that got a hit was the first one:
[tt]
A B C D E
2 Skegness 36000 21 2
[/tt]

 

[SkipVought]

and your FORMULA(S)???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tonyvee1973]

Thanks Gruuuu, looks so simple but was driving me crazy! This works liek a charm. Thanks you so much, really appreciated!
<I need to go back to excel school>

 

[tonyvee1973]

Thanks for the input Skip, all sorted now.

 

[Gruuuu]

SUMIFS looks simple, but before 2007 we had to use some creative applications of SUMPRODUCT. Be thankful.

And regarding Excel School, they have that. I've not personally attended the classes, but the guy really seems to know what he's talking about, and I've bought one of his products (in order to conduct some Excel Training of my own), and it was extremely well put-together.

 

[Gruuuu]

Sigh. I should know better than to attempt links without previewing the post. Oh well, it still works.

 

[tonyvee1973]

great, thx for the info and will definately take a look.
Just a final question, i inserted 4 rows into the top of the spreadsheets for some column headers and now the formula doesnt work. If i remove the lines it works.
WOuld you mind explaining how the formula you supplied works so i can try and work out why this is happening?
Many thanks

 

[Gruuuu]

No sweat.

[tt]
=SUMIFS(Sheet2!$C:$C,Sheet2!$A:$A,$A1,Sheet2!$B:$B,$C1)

=SUMIFS(Sum Range,Conditional Col 1,Condition 1,Conditional Col 2,Condition 2,... ,Conditional Col n,Condition n)
[/tt]

For each row with each Condition in the corresponding Condition Column, add up the values in the Sum Range.

Now I'm not sure which sheet you added the rows to, but honestly I don't need to. Take a look at the formula, and see if you can figure out why the formula doesn't work anymore (there will be a correlation with the rows you added). Then, you can figure out what it is that's moved, and update your formula accordingly.

One of the tools available that helps a lot when debugging formulas is the Evaluate Formula tool. It's located in the formula Auditing section of the Formula ribbon. Evaluate the formula step by step inside that tool and it will show you exactly what it's trying to do, and why it's failing to do what you want.

And let us know how it turns out for you!