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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.