MrDB4Excel
Board Regular
 Joined
 Jan 29, 2004
 Messages
 161
 Office Version

 2013
 Platform

 Windows
I have a workbook containing 15 sheets that retrieve data from power query sheets in the same workbook. The attached XL2BB refers to sheet 1.
Sheet 1 includes a named range called Data01 which includes A5:F45.
Is it possible to set up a formula or multiple formulas without VBA to dynamically change the range of the named range?
For example, Data01 includes A5:F45. If a value is input into a cell, say 50, then the range automatically changes in Data01 to A5:F55.
This image shows the result of the row highlighting conditional formatting.
Sheet 1 includes a named range called Data01 which includes A5:F45.
Is it possible to set up a formula or multiple formulas without VBA to dynamically change the range of the named range?
For example, Data01 includes A5:F45. If a value is input into a cell, say 50, then the range automatically changes in Data01 to A5:F55.
Cell Formulas  

Range  Formula  
B1  B1  =RIGHT(C5,LEN(C5)0) 
C1  C1  =LEFT(C3,1)&"z" 
E1  E1  =COUNTIF(Data01,A5)>0 
B2  B2  =MID(B1, FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),4))+1, FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),5))  FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),2))1) 
C2  C2  =C1 
D2  D2  =HYPERLINK("#"&CELL("address",INDEX(Summary!$E$2:$E$33,MATCH($C$2,Summary!$E$2:$E$33,0))),"<<< Jump To This Sheet's Link In The Summary Sheet") 
B3  B3  =LEFT(B2,LEN(B2)1) 
C3  C3  =MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32) 
H3  H3  =SUM(A5:INDEX(Data01,H5,H6)) 
A5:A10  A5  =VLOOKUP('1z'!$A5,'1z'!$A:$A,COLUMN('1z'!$A:$A)COLUMN('1z'!$A:$A)+1,0) 
B5:B10  B5  =VLOOKUP('1z'!$B5,'1z'!$B:$B,COLUMN('1z'!$B:$B)COLUMN('1z'!$B:$B)+1,0) 
C5:C10  C5  =VLOOKUP('1z'!$C5,'1z'!$C:$C,COLUMN('1z'!$C:$C)COLUMN('1z'!$C:$C)+1,0) 
D5:D10  D5  =CONCATENATE(C5,B5) 
E5:E10  E5  =HYPERLINK(D5) 
F5:F10  F5  =VLOOKUP('1z'!$D5,'1z'!$D:$D,COLUMN('1z'!$D:$D)COLUMN('1z'!$D:$D)+1,0) 
Named Ranges  

Name  Refers To  Cells 
'1'!Data01  ='1'!$A$5:$A$5:'1'!M38  H3, E1 
'1z'!ExternalData_1  ='1z'!$A$4:$D$8  A5:A10 
Cells with Conditional Formatting  

Cell  Condition  Cell Format  Stop If True  
A5:F45  Expression  =AND($A5<>"",$G$1=ROW())  text  NO 
This image shows the result of the row highlighting conditional formatting.