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

[SamueXCEl]

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???)

 

[Eric W]

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]

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]

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.

Book1
	A	B	C	D	E	F	G	H	I	J
1		List			List	6				List
2		a			a	10		a		a
3		b			b			b		b
4		c			c					c
5		d						a
6		e						c
7								d
8
9								e
10								a
11
Sheet1

Cell Formulas
Range	Formula
F2	=LOOKUP("zzzzzzzz",H1:H100,ROW(H1:H100))
B2	{=IFERROR(INDEX($H$2:$H$15, MATCH(0, COUNTIF($B$1:$B1, $H$2:$H$15&"")+IF($H$2:$H$15="", 1, 0),0)),"")}
E2	{=IFERROR(INDEX($H$2:INDEX($H:$H,$F$1),MATCH(0, COUNTIF($E$1:$E1, $H$2:INDEX($H:$H,$F$1)&"")+IF($H$2:INDEX($H:$H,$F$1)="", 1, 0),0)),"")}
J2	{=IFERROR(INDEX($H$2:INDIRECT(ADDRESS($F$1,8)),MATCH(0, COUNTIF($J$1:$J1, $H$2:INDIRECT(ADDRESS($F$1,8))&"")+IF($H$2:INDIRECT(ADDRESS($F$1,8))="", 1, 0),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

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]

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]

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.

 

[SamueXCEl]

Thanks Eric - let me try ...

 

[SamueXCEl]

Thanks Eric - I used your INDEX hint. Worked.

 

[mikerickson]

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.