Formula solution required

[mdmackillop]

For those who use Index etc. this should be quite straightforward. I'm looking to return the information on the Summary page from the data on the Data page by updating the yellow cell. Note that this cell is custom formatted and has a simple numeric value.
Regards
MD

[matthewspatrick]

Malcolm,

Put this formula in D17:

=SUMPRODUCT((Data!$A$2:$A$54=TEXT(A$13,"#"))*(Data!$B$2:$B$54=A17)*(Data!$D $2:$D$54))

Copy down as needed. This was unnecessarily complicated by the fact that you have your "valuation numbers" on the Data worksheet formatted as text instead of being plain numbers; had they been numeric, the formula could have been:

=SUMPRODUCT((Data!$A$2:$A$54=A$13)*(Data!$B$2:$B$54=A17)*(Data!$D$2:$D$54))

Cheers,

Patrick

[CCkfm2000]

try this...

[Shazam]

Put this in cell D17 and copy it down.



=IF(ISNUMBER(MATCH(A17,Data!B11:B54,0)),VLOOKUP(C17,Data!C2:D54,2,0),"")

[matthewspatrick]

Malcolm,

I think I completely misunderstood the question the first time


This time:

1. Add a Name to the workbook, GrabData, which refers to: =OFFSET(Data!$A$1,MATCH(TEXT(Summary!$A$13,"#"),Data!$A$2:$A$65536,0),0,COU NTIF(Data!$A:$A,Summary!$A$13),4)
2. Put this formula in Summary!A17: =IF(ISERROR(INDEX(GrabData,ROW()-16,2)),"",INDEX(GrabData,ROW()-16,2))
3. Put this formula in Summary!C17: =IF(ISERROR(INDEX(GrabData,ROW()-16,3)),"",INDEX(GrabData,ROW()-16,3))
4. Put this formula in Summary!D17: =IF(ISERROR(INDEX(GrabData,ROW()-16,4)),"",INDEX(GrabData,ROW()-16,4))
5. Copy Summary!A17: D17 down as far as needed (I went down to Row 45)

Now, when you change the valuation scheme in Summary!A13, the dynamic range will update, and so will the return values in A17: D45.

Patrick

[matthewspatrick]

Malcolm,

Did you have a chance to check out my latest suggestion?

Patrick

[mdmackillop]

Hi Patrick,
This is a "Work" project and I've been off this job for a couple of days. I'll get back to it on Monday hopefully,
Thanks,
Malcolm