Show the text in the cell furthest to the right when its the result of a formula

[damian_r_Home]

OK, my last one for today...

I've searched the net for help to this one but I've only found answers that are for the text in the cell furthest to the right of a range of cells rather than form certain cells......

Anyway, in cell E8 I need it to show the value in the furthest to the right of the cells M8, W8, AG8 & AQ8 (all of the ones I've coloured in yellow in the screenshot below) - so in this instance E8 would need to show the value from W8 which in this case is P02.

However, I cant get this to work whatsoever and I think that is because the formulas that I have found are when it needs to look at a range of cells rather that every 10th cell along the row AND because the value in cells M8, W8, AG8 & AQ8 is actually the result of a formula - I've included in row 9 all of the formulas that occur in row 8 just in case that helps with helping me.

Now ideally (and also greedily), the formula in cell E8 would be smart enough to take advantage of the fact that the cells it needs to look in are regularly spaced out at every 10 columns.

Anyway, as usual all help would be appreciated.

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[damian_r_Home]

OK, following is the first time I've ever tried to use XL2BB so hope it works....

Rev for Mr Excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
6														14						14				14						14				14						14				14
7										P01										P02										C01										C02
8				RF0100		01-Oct-20	15-Oct-20			01-Sep-20	02-Sep-20		P01	16-Sep-20	18-Sep-20					02-Oct-20	01-Oct-20		P02	15-Oct-20	15-Oct-20
9						=IF(D9="","",MAX(K9,U9,AE9,AO9))	=IF(D9="","",MAX(O9,Y9,AI9,AS9))	=IF(D9="","",MAX(L9,V9,AF9,AP9))				=IF(K9="",J9,"")	=IF(K9>0,J$7,"")	=IF(K9="","",K9+$N$6)						=IF(O9="","",O9+$T$6)		=IF(U9="",T9,"")	=IF(U9>0,T$7,"")	=IF(U9="","",U9+$N$6)						=IF(Z9="","",Z9+$T$6)		=IF(AE9="",AD9,"")	=IF(AE9>0,AD$7,"")	=IF(AE9="","",AE9+$N$6)						=IF(AJ9="","",AJ9+$T$6)		=IF(AO9="",AN9,"")	=IF(AO9>0,AN$7,"")	=IF(AO9="","",AO9+$N$6)
Design Report TWEAKED (2)

Cell Formulas
Range	Formula
X6,AN6	X6	=N6
E8	E8	=OFFSET($M8,0,COUNTA(M8:AY8))
F8	F8	=IF(D8="","",MAX(K8,U8,AE8,AO8))
G8	G8	=IF(D8="","",MAX(O8,Y8,AI8,AS8))
H8	H8	=IF(D8="","",MAX(L8,V8,AF8,AP8))
L8,AP8,AF8,V8	L8	=IF(K8="",J8,"")
M8,AQ8,AG8,W8	M8	=IF(K8>0,J$7,"")
N8,AR8,AH8,X8	N8	=IF(K8="","",K8+$N$6)
T8	T8	=IF(O8="","",O8+$T$6)
AD8,AN8	AD8	=IF(Z8="","",Z8+$T$6)

 

[Fluff]

Many thanks for that, how about

=INDEX(M8:AV8,AGGREGATE(14,6,(COLUMN(M8:AV8)-COLUMN(M8)+1)/(MOD(COLUMN(M8:AV8)-13,10)=0)/(M8:AV8<>""),1))

 

[Toadstool]

Edit: @Fluff beats me again!

Hi Damian_r_Home,

I've just used dummy formulae for the data but E8 should work.

Damian_r_Home.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
8	P02					01-Jan-20	01-Jan-20	Cat	P01	11-Jan-21	12-Jan-21					17-Jan-21	18-Jan-21		P02	21-Jan-21	22-Jan-21
9
10								=IF(1=2,"Help","Cat")	=IF(1=1,"P01","0")	=IF(1=1,TODAY()+COLUMN(),"0")	=IF(1=1,TODAY()+COLUMN(),"0")	#N/A	#N/A	#N/A		=IF(1=1,TODAY()+COLUMN(),"0")	=IF(1=1,TODAY()+COLUMN(),"0")		=IF(1=1,"P02","0")	=IF(1=1,TODAY()+COLUMN(),"0")	=IF(1=1,TODAY()+COLUMN(),"0")					=IF(1=1,"","0")		=IF(1=1,"","0")	=IF(1=1,"","0")	=IF(1=1,"","0")						=IF(1=1,"","0")	=IF(1=1,"","0")	=IF(1=1,"","0")	=IF(1=1,"","0")	Dog	=IF(1=1,"","0")	=IF(1=1,"","0")	=IF(1=1,"","0")	=IF(1=1,"","0")
Sheet1

Cell Formulas
Range	Formula
E8	E8	=INDEX(8:8,AGGREGATE(14,6,COLUMN($M$8:$AV$8)/((MOD(COLUMN($M$8:$AV$8)-COLUMN($M$8),10)=0)*(ISFORMULA($M$8:$AV$8))*($M$8:$AV$8<>"")),1))
L8	L8	=IF(1=2,"Help","Cat")
M8	M8	=IF(1=1,"P01","0")
X8:Y8,T8:U8,N8:O8	N8	=IF(1=1,TODAY()+COLUMN(),"0")
W8	W8	=IF(1=1,"P02","0")
AD8,AN8:AV8,AF8:AH8	AD8	=IF(1=1,"","0")
AS10:AV10,AN10:AQ10,AF10:AH10,AD10,W10:Y10,T10:U10,L10:R10	L10	=FORMULATEXT(L8)

 

[Peter_SSs]

Based on your sample layout & data and using Excel 365, this should also work for you.

=LOOKUP("ZZ",FILTER(M8:AQ8,(M8:AQ8<>"")*(J$7:AN$7<>""),""))

If it has to work in Excel 2007 as well then try this formula which should be confirmed with Ctrl+Shift+Enter, not just Enter (in Excel 2007 at least)

=IFERROR(LOOKUP("ZZ",IF(M8:AQ8<>"",IF(J$7:AN$7<>"",M8:AQ8))),"")

 

[damian_r_Home]

Thank you folks.

I'm going to try both of your methods and I'll tick as solutions asap.

Stay safe.

D

 

[damian_r_Home]

OK, spent longer than I thought I would on things this afternoon so apologies for the delay in getting back to you all.

@ Fluff & Toadstool
I'm afraid I didn't get your formulas to work - all they returned was #NAME

@Peter_SSs
again, it didn't return a valid answer/


In all cases it is likely me messing things up more than faults in teh formulas.


Many thansk for your help.

D

 

[Fluff]

Are you trying this in 365 or 2007?

 

[jasonb75]

What is in J7? The formulas in some (if not all) of your blank cells appear to be returning single spaces which will most likely be messing up the suggested formulas.

This looks like it works with the example in post 3, but it is a crude workaround rather than a solution and may be 100% reliable when you start to make changes to things.

=LOOKUP(2,1/(CHOOSE({1,2,3,4},M8,W8,AG8,AQ8)>" "),CHOOSE({1,2,3,4},M8,W8,AG8,AQ8))