Get the first match cell contain in excel formula

[mehidy1437]

hi guys, I'm using the below formula to make a summery in my first sheet.

This formula is working nicely without any problem, but if there is an additional row with cell value "Total=", then it gives no result of that sheet.

I just want to add additional conditions in this formula to grab only the first match & ignore the rest if there is any row/cell with Total=.

Please help me with this.

FORMULA AT W32

=IFERROR(INDIRECT("'" & $V32 & "'!" &(ADDRESS(SUMPRODUCT((INDIRECT("'"&$V32&"'!$A$1:$AZ$1000")="Total=")*ROW($A$1:$A$1000)),(SUMPRODUCT((INDIRECT("'"&$V32&"'!$A$1:$AZ$1000")="Total=")*COLUMN($A$1:$AZ$1)))+21))),"")

 

[jasonb75]

If it is found in more that 1 row (or column) then the row / column numbers will be added / multiplied by sumproduct, causing the result to be an incorrect row / column.

Can "Total=" be found in any row and any column, or would one be variable and the other fixed?

Without seeing the layout of the other sheets there appears to be little sense to the way your formula is written. Why use the range A:AZ and add 21 to the column at the end?

 

[mehidy1437]

in my case, Total= will be only in column A, but the row number will be different in different sheets.
Row numbers are not fixed.

Range A:AZ, I have used to garb a big range, it's can be A:Z.
added 21, to grab the cell value of column V, after finding the cell of "Total=" in column A.

Sheet layouts are simple datasheets.

Below is just an example. Sorry my XL2BB is not working.

 

[jasonb75]

Range A:AZ, I have used to garb a big range, it's can be A:Z.

That is going to serve no purpose, it will only mess things up (as you have found). You don't need to 'grab' any range, just refer to what is necessary.
I haven't tested this so typo's are possible.

=IFERROR(INDIRECT("'"&$V32&"'!V"&AGGREGATE(15,6,ROW($A$1:$A$1000)/(INDIRECT("'"&$V32&"'!$A$1:$A$1000")="Total="),1)),"")

 

[mehidy1437]

wow, perfect. Thanks alot.

In some case, if my column also not fixed like A, then what I have to do?

 

[jasonb75]

if my column also not fixed like A, then what I have to do?

You said it was so the formula was based on that.

in my case, Total= will be only in column A

So which is correct? If it's not fixed then don't say that it is when we ask the question.

If it is fixed then there is no point in allowing for such exceptions, they will only make it more complicated than necessary, which makes it more likely to fail in the same way as your original formula.

 

[mehidy1437]

I get the datasheet file from many different factories. Normally our requirement is to keep the "Total=" in column A, but sometimes some/few factories people change it to B, C , or any other column. Then we manually bring the "Total=" to column A & do the summary.

That's why I've asked you in my post #5, if "Total=" is not fixed in column A. Then what do I have to do?

Hope you understand now. Sorry if I make you confuse, with my question.

Your post#4, already answers my main requirement.

 

[jasonb75]

but sometimes some/few factories people change it

Perhaps instruct them not to change it? If you're sending out a sheet for them to complete and return maybe add some protection to the sheet to stop them moving it?

Failing that, is vba an option that can be considered? A simple UDF should work more effectively for what you're asking.

 

[mehidy1437]

Thanks for your reply.

If I change the range in the formula from A1:A1000 to A1:AZ1000, then it grabs the V column cell value, even if the "Total=" is in column A, B, C, etc.

It's solved my issue entirely.

What is your opening to use the range A1:AZ1000 ?

=IFERROR(INDIRECT("'"&$V32&"'!V"&AGGREGATE(15,6,ROW($A$1:$AZ$1000)/(INDIRECT("'"&$V32&"'!$A$1:$AZ$1000")="Total="),1)),"")

 

[jasonb75]

What is your opening to use the range A1:AZ1000 ?

Apart from being extremely inefficient, it should work.

Depending on your exact requirement, you may not get the expected result if "Total=" appears multiple times in different rows and columns (see below). In this scenario, the formula would return row 2.

Book1
	A	B	C
2			Total=
3		Total=
4	Total=
Sheet2