PDA

View Full Version : Formula solution required



mdmackillop
02-21-2006, 08:20 AM
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
02-21-2006, 09:03 AM
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
02-21-2006, 09:10 AM
try this...

Shazam
02-21-2006, 09:16 AM
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
02-21-2006, 09:37 AM
Malcolm,

I think I completely misunderstood the question the first time :banghead:


This time:


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,COUNTIF(Data!$A:$A,Summary!$A$13),4)
Put this formula in Summary!A17: =IF(ISERROR(INDEX(GrabData,ROW()-16,2)),"",INDEX(GrabData,ROW()-16,2))
Put this formula in Summary!C17: =IF(ISERROR(INDEX(GrabData,ROW()-16,3)),"",INDEX(GrabData,ROW()-16,3))
Put this formula in Summary!D17: =IF(ISERROR(INDEX(GrabData,ROW()-16,4)),"",INDEX(GrabData,ROW()-16,4))
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
02-23-2006, 08:13 PM
Malcolm,

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

Patrick

mdmackillop
02-24-2006, 01:27 AM
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