Border control on a expandable worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1586733%22%20slang%3D%22en-US%22%3EBorder%20control%20on%20a%20expandable%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586733%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20had%20a%20quote%20form%20I%20created%20many%20years%20ago%20that%20I%20am%20updating.%20The%20quote%20form%20uses%20VLOOKUP%20functions%20to%20grab%20information%20such%20as%20cost%20and%20descriptions%20from%20another%20sheet%20that%20we%20populate%20with%20our%20suppliers%20price%20lists.%20I%20used%20to%20have%20it%20so%20if%20you%20needed%20a%20second%20page%2C%20you%20had%20to%20copy%2Fpaste%20an%20entire%20page.%20But%20too%20many%20here%20in%20my%20office%20simply%20could%20not%20grasp%20it%20or%20had%20issues.%20Not%20to%20mention%2C%20the%20bottom%20of%20the%20page%20had%20our%20general%20conditions%20and%20a%20TOTAL%20BID%20line%20on%20every%20page.%20I%20want%20that%20information%20plus%20some%20additional%20room%20for%20general%20notes%20that%20are%20job%20specific.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20am%20working%20on%20creating%20what%20I%20will%20call%20a%20expandable%20single%20page.%20It%20will%20have%20the%20same%20header%2Ffirst%2010%20rows%20repeat%20on%20each%20page.%20I%20will%20make%20it%20so%20if%20you%20need%20more%20rows%20for%20the%20body%20of%20the%20quote%2C%20you%20simply%20copy%20and%20insert%20to%20add%2C%20or%20some%20other%20function%20to%20accomplish%20this%20%22growing%22%20body.%20So%20far%2C%20things%20seem%20to%20be%20working%20ok%2C%20though%20I%20still%20need%20to%20figure%20out%20if%20I%20can%20have%20the%20body%20automatically%20grow%20or%20add%20rows%20if%20I%20exceed%20the%20first%20page.%20But%20the%20reason%20I%20am%20reaching%20out%20here%20is%20the%20border.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%204%20columns%20that%20form%20the%20%22body%22%20of%20the%20quote.%20This%20is%20where%20we%20enter%20the%20materials%20needed%20for%20that%20job.%20Each%20column%20has%20a%20Quantity%2C%20Model%2C%20Description%20and%20a%20blank%20column%20for%20breaking%20out%20the%20pricing%20if%20required.%20Each%20of%20those%20columns%20has%20a%20bold%20boarder%20around%20the%20entire%20column%2C%20not%20for%20each%20individual%20cell.%20Our%20quote%20form%20when%20printed%20looks%20great%20as%20a%20result.%20But%20now%2C%20with%20an%20expanding%20body%20where%20I%20will%20use%20the%20same%20header%20or%20top%20cells%20to%20repeat%20at%20each%20new%20page%2C%20I%20am%20left%20with%20no%20border%20at%20the%20bottom%20of%20the%20first%20or%20each%20subsequent%20page%20when%20cells%20are%20added%20and%20the%20body%20expands%20into%20a%20second%20page.%20How%20can%20I%20have%20the%20last%20cell%20of%20a%20page%20give%20me%20a%20border%2C%20but%20only%20if%20that%20last%20cell%20is%20within%20the%20%22body%22%20or%20BOM%20section%20of%20the%20quote%3F%20Is%20this%20even%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20can%20I%20have%20an%20outline%20border%20that%20follows%20a%20specific%20number%20of%20cells%2C%20so%20that%20if%20cells%20are%20added%2C%20the%20border%20moves%20or%20shifts%20as%20needed%20to%20stay%20where%20it%20is%20needed%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1586733%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588932%22%20slang%3D%22en-US%22%3ERe%3A%20Border%20control%20on%20a%20expandable%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F758433%22%20target%3D%22_blank%22%3E%40GreatDay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20trial%20to%20see%20if%20it%20can%20help.%20Open%20a%20blank%20sheet.%20On%20any%20given%20row%20put%20a%20dark%20border%2C%20write%20something%2C%20or%20shade%20in%20a%20column%20for%20a%20reference%20point.%20Select%20a%20large%20range%2C%20say%20from%20A1%20to%20P23.%20Now%20go%20to%20conditional%20formatting%20and%20create%20a%20formula%20that%20says%20%3Drow()%3D23.%20Select%20format...%20go%20to%20the%20border%20tab%20and%20put%20a%20bottom%20line%20in%20the%20sample%20cell.%20Say%20ok%20until%20you%20are%20out.%20On%20your%20spreadsheet%20you%20should%20have%20a%20line%20going%20across%20the%20page%20from%20a%20to%20p%20on%20line%2023.%20If%20you%20add%20or%20delete%20any%20row%2C%20the%20line%20will%20stay%20on%20row%2023%20as%20the%20reference%20cells%20will%20move%20up%20or%20down.%3C%2FP%3E%3CP%3EYou%20can%20format%20this%20line%20or%20lines%20in%20any%20way%20you%20see%20fit.%20Helpful%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello, I have had a quote form I created many years ago that I am updating. The quote form uses VLOOKUP functions to grab information such as cost and descriptions from another sheet that we populate with our suppliers price lists. I used to have it so if you needed a second page, you had to copy/paste an entire page. But too many here in my office simply could not grasp it or had issues. Not to mention, the bottom of the page had our general conditions and a TOTAL BID line on every page. I want that information plus some additional room for general notes that are job specific.

 

So I am working on creating what I will call a expandable single page. It will have the same header/first 10 rows repeat on each page. I will make it so if you need more rows for the body of the quote, you simply copy and insert to add, or some other function to accomplish this "growing" body. So far, things seem to be working ok, though I still need to figure out if I can have the body automatically grow or add rows if I exceed the first page. But the reason I am reaching out here is the border.

 

We have 4 columns that form the "body" of the quote. This is where we enter the materials needed for that job. Each column has a Quantity, Model, Description and a blank column for breaking out the pricing if required. Each of those columns has a bold boarder around the entire column, not for each individual cell. Our quote form when printed looks great as a result. But now, with an expanding body where I will use the same header or top cells to repeat at each new page, I am left with no border at the bottom of the first or each subsequent page when cells are added and the body expands into a second page. How can I have the last cell of a page give me a border, but only if that last cell is within the "body" or BOM section of the quote? Is this even possible?

 

In other words, can I have an outline border that follows a specific number of cells, so that if cells are added, the border moves or shifts as needed to stay where it is needed?

1 Reply
Highlighted

@GreatDay 

Try this trial to see if it can help. Open a blank sheet. On any given row put a dark border, write something, or shade in a column for a reference point. Select a large range, say from A1 to P23. Now go to conditional formatting and create a formula that says =row()=23. Select format... go to the border tab and put a bottom line in the sample cell. Say ok until you are out. On your spreadsheet you should have a line going across the page from a to p on line 23. If you add or delete any row, the line will stay on row 23 as the reference cells will move up or down.

You can format this line or lines in any way you see fit. Helpful?