MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 348
- 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.