Help me make a formula better

[Craigc3814]

I have created this formula for a spreadsheet that scores your selection based off of the cell you select. (I Know its horrible). The main thing I want to fix is the worksheet reference bit of it. I would like to use the indirect formula to reference the worksheets so I can drag the formula down rather than having to do a find and replace when I want the score a different sheet. Indirect confuses the crap out of me for some reason.



=IF('1.1.2'!$C$6=$H$7,$A$1,IF('1.1.2'!$C$7=$H$7,$A$2,IF('1.1.2'!$C$8=$H$7,$A$3,IF('1.1.2'!$C$9=$H$7,$A$4,IF('1.1.2'!$C$10=$H$7,$A$5,IF('1.1.2'!$D$6=$H$7,$B$1,IF('1.1.2'!$D$7=$H$7,$B$2,IF('1.1.2'!$D$8=$H$7,$B$3,IF('1.1.2'!$D$9=$H$7,$B$4,IF('1.1.2'!$D$10=$H$7,$B$5,IF('1.1.2'!$E$6=$H$7,$C$1,IF('1.1.2'!$E$7=$H$7,$C$2,IF('1.1.2'!$E$8=$H$7,$C$3,IF('1.1.2'!$E$9=$H$7,$C$4,IF('1.1.2'!$E$10=$H$7,$C$5,IF('1.1.2'!$F$6=$H$7,$D$1,IF('1.1.2'!$F$7=$H$7,$D$2,IF('1.1.2'!$F$8=$H$7,$D$3,IF('1.1.2'!$F$9=$H$7,$D$4,IF('1.1.2'!$F$10=$H$7,$D$5,IF('1.1.2'!$G$6=$H$7,$E$1,IF('1.1.2'!$G$7=$H$7,$E$2,IF('1.1.2'!$G$8=$H$7,$E$3,IF('1.1.2'!$G$9=$H$7,$E$4,IF('1.1.2'!$G$10=$H$7,$E$5," ")))))))))))))))))))))))))

 

[Eric W]

OK, first let's try to simplify that formula. If the results are numeric you can just use the SUMIF formula shown in I7. If the results you're looking for are character, then the array formula in J7 should work. Your formula is in M7 for comparison.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	10	60	110	160	210									1.1.2
2	20	70	120	170	220									1.1.3
3	30	80	130	180	230
4	40	90	140	190	240
5	50	100	150	200	250
6
7								25	250	250	250	250	250
8									250	250	250	250

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula I7 =SUMIF('1.1.2'!$C$6:$G$10,$H$7,$A$1:$E$5) K7 =SUMIF(INDIRECT("'"&N1&"'!$C$6:$G$10"),$H$7,$A$1:$E$5) M7 =IF('1.1.2'!$C$6=$H$7,$A$1,IF('1.1.2'!$C$7=$H$7,$A$2,IF('1.1.2'!$C$8=$H$7,$A$3,IF('1.1.2'!$C$9=$H$7,$A$4,IF('1.1.2'!$C$10=$H$7,$A$5,IF('1.1.2'!$D$6=$H$7,$B$1,IF('1.1.2'!$D$7=$H$7,$B$2,IF('1.1.2'!$D$8=$H$7,$B$3,IF('1.1.2'!$D$9=$H$7,$B$4,IF('1.1.2'!$D$10=$H$7,$B$5,IF('1.1.2'!$E$6=$H$7,$C$1,IF('1.1.2'!$E$7=$H$7,$C$2,IF('1.1.2'!$E$8=$H$7,$C$3,IF('1.1.2'!$E$9=$H$7,$C$4,IF('1.1.2'!$E$10=$H$7,$C$5,IF('1.1.2'!$F$6=$H$7,$D$1,IF('1.1.2'!$F$7=$H$7,$D$2,IF('1.1.2'!$F$8=$H$7,$D$3,IF('1.1.2'!$F$9=$H$7,$D$4,IF('1.1.2'!$F$10=$H$7,$D$5,IF('1.1.2'!$G$6=$H$7,$E$1,IF('1.1.2'!$G$7=$H$7,$E$2,IF('1.1.2'!$G$8=$H$7,$E$3,IF('1.1.2'!$G$9=$H$7,$E$4,IF('1.1.2'!$G$10=$H$7,$E$5," "))))))))))))))))))))))))) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula J7 {=INDIRECT(TEXT(MAX(IF('1.1.2'!$C$6:$G$10=$H$7,(ROW($C$6:$G$10)-5)*100+COLUMN($C$6:$G$10)-2)),"R00C00"),FALSE)} L7 {=INDIRECT(TEXT(MAX(IF(INDIRECT("'"&N1&"'!$C$6:$G$10")=$H$7,(ROW($C$6:$G$10)-5)*100+COLUMN($C$6:$G$10)-2)),"R00C00"),FALSE)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

On Sheet 1.1.2 I just have a table from 1-25 in C6:G10, with the same values from A1:E5 divided by 10.

To use INDIRECT to have them reference a different sheet, just put the sheet names in a column like I did in N. Then adapt the formulas as I did in K7 and L7, and you can just drag them down the columns. You can see how much nicer it is to just have to use INDIRECT once instead of 25 times.

Let me know if this helps.

 

[Craigc3814]

Sorry, just got around to working on this. Works great, I added an iferror to the front because I need a blank to return when the value in H7 was not found. Thank you so much!!!!

 

[Craigc3814]

One question, just because I like to learn. I am trying to break this formula down and I am a bit lost.


{=INDIRECT(TEXT(MAX(IF(INDIRECT("'"&N1&"'!$C$6:$G$10")=$H$7,(ROW($C$6:$G$10)-5)*100+COLUMN($C$6:$G$10)-2)),"R00C00"),FALSE)}

It is formatting the number in N1 to the sheet name then searching that sheet cells C6-G10. The - 5 * 100+ column section is losing me. Then I am assuming the R00C00 is a type of text format? What is False doing in the indirect formula (no experience at all with indirect as I stated).

 

[Eric W]

Sorry, I got a bit sloppy with that formula. To be more portable, the formula should be:

{=INDIRECT(TEXT(MAX(IF(INDIRECT("'"&N1&"'!$C$6:$G$10")=$H$7,(ROW($C$6:$G$10)-ROW($C$6)+1)*100+COLUMN($C$6:$G$10)-COLUMN($C$6)+1)),"R00C00"),FALSE)}

As far as how it works, you have the first part right. The innermost INDIRECT finds the sheet name, then the IF searches that sheet, range C6:G10 to see what cell matches H7. Let's say that D8 matches. Then this part:

(ROW($C$6:$G$10)-ROW($C$6)+1)*100+COLUMN($C$6:$G$10)-COLUMN($C$6)+1)

evaluates to:

(ROW($D$8)-ROW($C$6)+1)*100+COLUMN($D$8)-COLUMN($C$6)+1)
(8-6+1)*100+(4-3+1)
3*100 + 2
302

This is just a way to combine the row and column of the matching cell into a single number. The 3 represents the row, the 2 represents the column. The -ROW($C$6)+1 part converts it from an absolute row into a relative row, relative to the starting corner, C6.

Assuming there's only 1 match, then the IF will return an array that looks something like {FALSE,FALSE,FALSE,302,FALSE,FALSE,...} and the MAX gets the largest numeric value, or 302. If there is more than 1 match, the other values will also be in the array, but MAX will still only choose 1 of them, we don't know which.

The TEXT function allows you to reformat a number as text, and you can give it the format you want to use. In this case we give it R00C00, which converts the 302 to R03C02. Excel has 2 ways to reference a cell. One is the A1 method you're familiar with, and the other is a row/column reference. R03C02 is equivalent to B3, (3 rows down, 2 columns over).

And the final use of INDIRECT takes a text value of a range and converts it to a real range. The use of FALSE as the second parameter tells it that you're giving it an R01C01 type reference instead of an A1 type reference.

Hope this helps!

 

[Craigc3814]

Unbelievable. Thank you so much for your help. This website never fails to amaze me, I think I have it all figured out, run into an error, and within an hour someone here has it solved. My formula was a duct tape formula that was getting me through for the time being, but I got tired of manipulating it. Thanks again.

 

[Eric W]

Incidentally, if you have one of the newer versions of Excel with the new TEXTJOIN function (which Microsoft should have added long ago!), you can use this much simpler formula:

{=TEXTJOIN(",",TRUE,IF(INDIRECT("'"&N1&"'!$C$6:$G$10")=$H$7,$A$1:$E$5,""))}