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

damian_r_Home

Board Regular
Joined
Jan 8, 2005
Messages
205
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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.

1609174861379.png


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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 8, 2005
Messages
205
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
OK, following is the first time I've ever tried to use XL2BB so hope it works....


Rev for Mr Excel.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
614141414141414
7P01P02C01C02
8RF0100 01-Oct-2015-Oct-20 01-Sep-2002-Sep-20 P0116-Sep-2018-Sep-2002-Oct-2001-Oct-20 P0215-Oct-2015-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
RangeFormula
X6,AN6X6=N6
E8E8=OFFSET($M8,0,COUNTA(M8:AY8))
F8F8=IF(D8="","",MAX(K8,U8,AE8,AO8))
G8G8=IF(D8="","",MAX(O8,Y8,AI8,AS8))
H8H8=IF(D8="","",MAX(L8,V8,AF8,AP8))
L8,AP8,AF8,V8L8=IF(K8="",J8,"")
M8,AQ8,AG8,W8M8=IF(K8>0,J$7,"")
N8,AR8,AH8,X8N8=IF(K8="","",K8+$N$6)
T8T8=IF(O8="","",O8+$T$6)
AD8,AN8AD8=IF(Z8="","",Z8+$T$6)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Many thanks for that, how about
Excel Formula:
=INDEX(M8:AV8,AGGREGATE(14,6,(COLUMN(M8:AV8)-COLUMN(M8)+1)/(MOD(COLUMN(M8:AV8)-13,10)=0)/(M8:AV8<>""),1))
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,362
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Edit: @Fluff beats me again!

Hi Damian_r_Home,

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

Cell Formulas
RangeFormula
E8E8=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))
L8L8=IF(1=2,"Help","Cat")
M8M8=IF(1=1,"P01","0")
X8:Y8,T8:U8,N8:O8N8=IF(1=1,TODAY()+COLUMN(),"0")
W8W8=IF(1=1,"P02","0")
AD8,AN8:AV8,AF8:AH8AD8=IF(1=1,"","0")
AS10:AV10,AN10:AQ10,AF10:AH10,AD10,W10:Y10,T10:U10,L10:R10L10=FORMULATEXT(L8)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,347
Office Version
  1. 365
Platform
  1. Windows
Based on your sample layout & data and using Excel 365, this should also work for you.

Excel Formula:
=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)

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

damian_r_Home

Board Regular
Joined
Jan 8, 2005
Messages
205
Office Version
  1. 365
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jan 8, 2005
Messages
205
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Are you trying this in 365 or 2007?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=LOOKUP(2,1/(CHOOSE({1,2,3,4},M8,W8,AG8,AQ8)>" "),CHOOSE({1,2,3,4},M8,W8,AG8,AQ8))
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,641
Members
415,849
Latest member
PhoenixRising2015

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