Find value based on 2 comparisons and copy/paste value

[Blichfeldt]

Problem description.
I am trying to make a funktionality in Excel, where I am going to find a value based om 2 comparisons with 2*2 variables.
When the value is found, it's going to be copied to cell where the result of the comparison crossed.

I have made a test sheet, where sheet 1 contains data and value, and sheet 2 contains data and cells for the value.
Some of the cells are filled manually.
What I want is, the this filling can be done with eg a macro.

The code attached, is what I have done so far, but it's no working.
Any suggestions to solve this problem?

Kindly regards Peter

Please Login or Register  to view this content.

[Naveed Raza]

Hi,

U need the output on 2 condition right and there is no need to work on the 1st tab.

[Naveed Raza]

Hi,

place this formual on B3 cell of Sheet2

=INDEX('Sheet 1'!$A:$A,MATCH(1,('Sheet 2'!$A3='Sheet 1'!$C:$C)*('Sheet 2'!B$2='Sheet 1'!$B:$B),0))

then again press F2 on B3 cells then press Cntrl + shift + enter and drag the formula.

[event21]

Hi -

Use Pivot Table and you can achieve your purpose.

Regards,
Event

[Naveed Raza]

Thanks event21 pivot table is more easy to extract data.

Blichfeldt - please have review the attached workbook and let me know if u hv any question.

Thanks - Naveed.

[Blichfeldt]

What you send do not update automatic.
That's important.

What I'm after, is some generic code that can be activated with a button or automaticly when closing the workbook.

Regards Peter

[Blichfeldt]

this line is not working
=INDEX('Sheet 1'!$A:$A,MATCH(1,('Sheet 2'!$A3='Sheet 1'!$C:$C)*('Sheet 2'!B$2='Sheet 1'!$B:$B),0))

Regards Peter

[Blichfeldt]

I like the idea with index, if it works
Regards Peter

[Naveed Raza]

see if u pasted that index formula in B3 cells then goto B3 cell and press F2 function key and then press at a time Cntl + Shift + enter u will get.

[Blichfeldt]

I did that, but I couldn't press enter duing to failure in the line at thispoint " $A,MATCH ".
I don't even get to the point goto B3

[Naveed Raza]

ok,
1) press F2 in cell B3
2) then paste the formula and then hold Cntl + Shift and then press Enter u will get its very simple

[Blichfeldt]

I did that again :-)
Respone:
This formula contains invalid entries.

When I press "OK" it returns with mark around $A,MATCH
Can this problem be related to language settings

[Naveed Raza]

ok fine i will attach the workbook but see it will make all active worksheet very very very very very slow performance

[Blichfeldt]

That's fine Raza.

[Naveed Raza]

hv review the attached workbook

[Blichfeldt]

It was at language problem.
But this solution is not dueable, it's way to slow.
I'm proberly back to macro again

[Blichfeldt]

It was at language problem.
But this solution is not dueable, it's way to slow.
I'm proberly back to macro again