# Help me make a formula better

#### Craigc3814

##### Board Regular
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," ")))))))))))))))))))))))))

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Eric W

##### MrExcel MVP
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.

ABCDEFGHIJKLMN
110601101602101.1.2
220701201702201.1.3
33080130180230
44090140190240
550100150200250
6
725250250250250250
8250250250250

<tbody>
</tbody>
Sheet7

Worksheet Formulas
CellFormula
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
CellFormula
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.

Last edited:

#### Craigc3814

##### Board Regular
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

##### Board Regular
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).

Last edited:

#### Eric W

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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,""))}

Replies
2
Views
107
Replies
3
Views
88
Replies
0
Views
37
Replies
4
Views
107
Replies
13
Views
154

1,191,191
Messages
5,985,209
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back