Using a cell ref. from =address in an array formula

SamueXCEl

New Member
Joined
Aug 6, 2019
Messages
9
Hi - I am trying to do a nested iferror/index/match/countif array formula - my formula works - but when I use a reference made from =address, I can't get it to work.

The =address reference is the max point for searching in my formula - it would be repeated a few times.

any ideas?

just a simple example:
e.g. instead of doing =sum(a1:a5) I am trying to do =sum(a1:the cell where I have made my =address cell reference - a???)
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,797
Welcome to the forum.

It's hard to say what will work for you without seeing your formula, there are many ways to do what you ask. One thing you can try is to use INDEX instead of ADDRESS. For example:

=SUM(A1:INDEX(A:A,MATCH("Z",B1:B100,0")))

The MATCH finds the cell in B1:B100 with a Z in it, the INDEX finds the corresponding cell in A, then the A1:Axx range is used by SUM. So if you can find your end cell with something like that, you might be in luck.
 

SamueXCEl

New Member
Joined
Aug 6, 2019
Messages
9
Thanks Eric - here is my formula - to return the unique/remove duplicates from column area H2 to the address I have created:
{=IFERROR(INDEX($H$2:[], MATCH(0, COUNTIF($B1:$D1, $H$2:[]&"")+IF($H$2:[]="", 1, 0)),"")

[] is where I assume the reference from =ADDRESS would go.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,797
I had a bit of an issue getting your original formula to work. I had to tweak it a bit. You may want to look at it to make sure it does what you expect. It has a fixed end row of H15. Having gotten it to work, I changed it so that the bottom row of the range being search is based on the row number in F1. You didn't say how you determined the last row. If you're just looking for the last row with data, consider the formula in F2.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">List</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">List</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="color: #333333;;">a</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">a</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">a</td><td style="text-align: right;;"></td><td style=";">a</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="color: #333333;;">b</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">b</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">b</td><td style="text-align: right;;"></td><td style=";">b</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="color: #333333;;">c</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="color: #333333;;">d</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">a</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="color: #333333;;">e</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">c</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">d</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">e</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">a</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="color: #333333;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=LOOKUP(<font color="Blue">"zzzzzzzz",H1:H100,ROW(<font color="Red">H1:H100</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$H$2:$H$15, MATCH(<font color="Green">0, COUNTIF(<font color="Purple">$B$1:$B1, $H$2:$H$15&""</font>)+IF(<font color="Purple">$H$2:$H$15="", 1, 0</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$H$2:INDEX(<font color="Green">$H:$H,$F$1</font>),MATCH(<font color="Green">0, COUNTIF(<font color="Purple">$E$1:$E1, $H$2:INDEX(<font color="Teal">$H:$H,$F$1</font>)&""</font>)+IF(<font color="Purple">$H$2:INDEX(<font color="Teal">$H:$H,$F$1</font>)="", 1, 0</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$H$2:INDIRECT(<font color="Green">ADDRESS(<font color="Purple">$F$1,8</font>)</font>),MATCH(<font color="Green">0, COUNTIF(<font color="Purple">$J$1:$J1, $H$2:INDIRECT(<font color="Teal">ADDRESS(<font color="#FF00FF">$F$1,8</font>)</font>)&""</font>)+IF(<font color="Purple">$H$2:INDIRECT(<font color="Teal">ADDRESS(<font color="#FF00FF">$F$1,8</font>)</font>)="", 1, 0</font>),0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Your original (ish) formula is in B2. The updated version is in E2. The F2 formula gets you the last used row in H, which you can incorporate in E2 if you wish. If you absolutely want to use ADDRESS, the J2 formula has that. But I don't recommend it since INDIRECT is volatile and INDEX is not.

Let me know if this helps.
 

SamueXCEl

New Member
Joined
Aug 6, 2019
Messages
9

ADVERTISEMENT

Hi - no, my formulas works grand when I [Ctrl.]+[Shift]+[Enter] it to remove the duplicates. I just wanted to determine the area of column H (how far down it goes) when removing the duplicates.

I used a formula to find the first empty cell in column H, and just used that row number - 1 as the last row, I know the column letter, so I made a cell reference, and this cell ref. should be the extreme of the 'H' components for my master formula.

Basically, when a bigger/smaller data source file gets dropped into the file next month, the formula will then know the end of the H column area to look in, and only look within this variable boundary area each time.

I can only get it to work when I set it to an actual area - e.g. H2:H15 - but that only works when the raw data I need is within that area, anything else is not necessary and ruins my result.

Is it possible to use a cell reference from an =address formula as the 2nd part to the ':', instead of - e.g. - H15?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,797
Yes, the J2 formula in my previous post shows how to use INDIRECT(ADDRESS to reference the bottom row. But look at the E2 formula using INDEX - it's shorter and more efficient.
 
Last edited:

SamueXCEl

New Member
Joined
Aug 6, 2019
Messages
9

ADVERTISEMENT

Thanks Eric - let me try ...
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,980
Would Names work.
If you have a named cell MyCell and use =SUM(A1:MyCell) you could adjust the definition of MyCell and the formula(s) will follow.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,312
Messages
5,635,492
Members
416,860
Latest member
coen078

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top