| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Area
I have a sheet that has various formulas that return blank cells. How
do I set the print area dynamically so that is stops based on a column's length? Any Ideas? The sheet length changes daily and I don't want user to have to change settings at all... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Print Area
I like this approach (with those formulas in column A):
Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Nimish wrote: I have a sheet that has various formulas that return blank cells. How do I set the print area dynamically so that is stops based on a column's length? Any Ideas? The sheet length changes daily and I don't want user to have to change settings at all... -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Print Area
Dave,
Does this technique work if I need to replicate this for 14 different worksheets in the same workbook? I thin I am getting some name conflicts that I don't understand how to deal with. Nimish Dave Peterson wrote: I like this approach (with those formulas in column A): Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Nimish wrote: I have a sheet that has various formulas that return blank cells. How do I set the print area dynamically so that is stops based on a column's length? Any Ideas? The sheet length changes daily and I don't want user to have to change settings at all... -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Print Area
The parts that you have to be careful with:
Names in workbook: Sheet1!LastRow Names in workbook: Sheet1!Print_Area Make sure you include the sheet name when you do this. But some sheet names require apostrophes surrounding the names (just like a formula in a cell): Names in workbook: 'Sheet1'!LastRow Names in workbook: 'Sheet1'!Print_Area If you always use the apostrophes, excel won't mind. If you need them and don't use them, you'll have trouble. And change Sheet1 to whatever worksheet you're working on. Names in workbook: 'My SheetName Here'!LastRow Names in workbook: 'My SheetName Here'!Print_Area Nimish wrote: Dave, Does this technique work if I need to replicate this for 14 different worksheets in the same workbook? I thin I am getting some name conflicts that I don't understand how to deal with. Nimish Dave Peterson wrote: I like this approach (with those formulas in column A): Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Nimish wrote: I have a sheet that has various formulas that return blank cells. How do I set the print area dynamically so that is stops based on a column's length? Any Ideas? The sheet length changes daily and I don't want user to have to change settings at all... -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Print Area
Dave,
Never got back to you, but thank you very much. Dave Peterson wrote: The parts that you have to be careful with: Names in workbook: Sheet1!LastRow Names in workbook: Sheet1!Print_Area Make sure you include the sheet name when you do this. But some sheet names require apostrophes surrounding the names (just like a formula in a cell): Names in workbook: 'Sheet1'!LastRow Names in workbook: 'Sheet1'!Print_Area If you always use the apostrophes, excel won't mind. If you need them and don't use them, you'll have trouble. And change Sheet1 to whatever worksheet you're working on. Names in workbook: 'My SheetName Here'!LastRow Names in workbook: 'My SheetName Here'!Print_Area Nimish wrote: Dave, Does this technique work if I need to replicate this for 14 different worksheets in the same workbook? I thin I am getting some name conflicts that I don't understand how to deal with. Nimish Dave Peterson wrote: I like this approach (with those formulas in column A): Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Nimish wrote: I have a sheet that has various formulas that return blank cells. How do I set the print area dynamically so that is stops based on a column's length? Any Ideas? The sheet length changes daily and I don't want user to have to change settings at all... -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Print Area
Glad it worked for you.
Nimish wrote: Dave, Never got back to you, but thank you very much. Dave Peterson wrote: The parts that you have to be careful with: Names in workbook: Sheet1!LastRow Names in workbook: Sheet1!Print_Area Make sure you include the sheet name when you do this. But some sheet names require apostrophes surrounding the names (just like a formula in a cell): Names in workbook: 'Sheet1'!LastRow Names in workbook: 'Sheet1'!Print_Area If you always use the apostrophes, excel won't mind. If you need them and don't use them, you'll have trouble. And change Sheet1 to whatever worksheet you're working on. Names in workbook: 'My SheetName Here'!LastRow Names in workbook: 'My SheetName Here'!Print_Area Nimish wrote: Dave, Does this technique work if I need to replicate this for 14 different worksheets in the same workbook? I thin I am getting some name conflicts that I don't understand how to deal with. Nimish Dave Peterson wrote: I like this approach (with those formulas in column A): Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). Nimish wrote: I have a sheet that has various formulas that return blank cells. How do I set the print area dynamically so that is stops based on a column's length? Any Ideas? The sheet length changes daily and I don't want user to have to change settings at all... -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extra Row in Defined Print Area | Excel Discussion (Misc queries) | |||
| Can't Clear Print Area | Excel Discussion (Misc queries) | |||
| Volatile print area | Excel Discussion (Misc queries) | |||
| set the print area print multiple tabs | Excel Discussion (Misc queries) | |||
| Printing - Have to set print area 1 column further than necessary | Excel Discussion (Misc queries) | |||
Linear Mode
