Either simple or impossible...
Either simple or impossible...
(OP)
Hey, guys..
I have several rows in my spreadsheet that use the same formula, although the cells referenced by the formulas increment. Normally, I could just copy the cells down and let the cell #s increment. No problem.
In this case, however, I have borders around each of the cells in the rows in question. I want a gap between the borders. Is there any way to insert a gap between two rows, in lieu of inserting another row? If I insert another row, my formulas do not increment correctly, and I have to correct them manually.
If that made absolutely no sense, I apologize. I'm finding it hard to describe what I'm trying to do.
I have several rows in my spreadsheet that use the same formula, although the cells referenced by the formulas increment. Normally, I could just copy the cells down and let the cell #s increment. No problem.
In this case, however, I have borders around each of the cells in the rows in question. I want a gap between the borders. Is there any way to insert a gap between two rows, in lieu of inserting another row? If I insert another row, my formulas do not increment correctly, and I have to correct them manually.
If that made absolutely no sense, I apologize. I'm finding it hard to describe what I'm trying to do.





RE: Either simple or impossible...
RE: Either simple or impossible...
Thanks, though!!
RE: Either simple or impossible...
Just my two cents
Cyril Guichard
Mechanical Engineer
RE: Either simple or impossible...
1. use double borders, or
2. setup the formula's like this, for example,
A1: 1
A3: =A1+1
then select rows 3 and 4, copy (Ctrl-C), select down as many rows as you like (should be an even number), and paste. This will fill A5: =A3+1, A7: =A5+1, etc.
3. download ASAP utilities (www.asap-utilities.com) which contains a tool to add rows between each row in your selection.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Either simple or impossible...
I'm assuming you want a row that has borders followed by a row without borders etc...
Assuming this is the case, you need to border the rows you want bordered then select both the row you want and the row above it (or below it). If you then click on the black square in the lower right corner and drag downward, you should have a row that is not bordered followed by a row that is bordered.
If I'm off track let me know and I'll try again.
jproj
RE: Either simple or impossible...
RE: Either simple or impossible...
A B
1 1 =A1 (With border)
2 2 =A2 (Without Border)
3 3 =A3 (With Border)
4 4 =A4 (Without border)
etc...
If it is, you need to follow what I said before and select one row with a border and one without a border in order to retain both formula and border increment.
jproj
PS: If not, please post something visual so we can better understand your problem.
RE: Either simple or impossible...
-pmover
RE: Either simple or impossible...
RE: Either simple or impossible...
You can acheive a similar look to what you want by using the double line format under the: format (menu) > cell (menu item) > border (tab).
This still has the problems of the ends of the cells still being connected together. Try it and see.
regards
sc
RE: Either simple or impossible...
On a column that you can hide, say Column A, enter 1 say at row 5 then 2 at next cell below (row 6). At the 3rd cell down (A7) write formula = A5. Then copy this cell all the way down as long as you want your spreadsheet to be. You should get a colum of nos alternating between 1 & 2.
Next on you spreadsheet, say you want the blank space to occur at every other row with the no. 2 in column A.
Prefix each of your formula by a condition statement to display blank whenever the no. 2 is at the spreadsheer row in column A as follows, for row 1:
If($A1=2,"",write your formula for the particular cell)
for row 2:
If($A2=2,"",copy your formula for the cell above)
RE: Either simple or impossible...
This allowa you, for example, shade even second line to make rading across rows easlier. The great thing about this method is when you insert a row you stil get every second line shaded.
to test to see if the row is odd or even use 'mod(row(a1),2)'. 0=even 1 = odd
Even rows format a boarder or shading etc, odd rows not format.
RE: Either simple or impossible...
> You could simply insert gaps in the referenced data.
> You could replace the relative references with fixed references prior to inserting the extra cells. For example, if the referenced cells are A1,A2,A3, replace A with A$, resulting in references to A$1, A$2, A$3, which you can now insert any number of cells in between without affecting the references.
TTFN
RE: Either simple or impossible...
Thanks, though!
RE: Either simple or impossible...
TTFN
RE: Either simple or impossible...
Here is an example of what I'm talking about:
Source data:
Col A
R1 heading
R2 value1
R3 value2
R4 value3
Then your formatted, skip-a-row, formula in column B would be:
Col B
R1 heading
R2 =IF(INT(COUNTA($B$2:B2)/2)<>COUNTA($B$2:B2)/2,"",OFFSET($A$2,COUNT($B$2:B2),0,1,1))
R3 =IF(INT(COUNTA($B$2:B3)/2)<>COUNTA($B$2:B3)/2,"",OFFSET($A$2,COUNT($B$2:B3),0,1,1))
R4 =if(row(a3)<>2*counta($a$2:a3),offset(sheet1!$a$2, count(sheet2!$a$2:a3),0,1,1),"")
Here's the breakdown on the steps:
IF(INT(COUNTA($B$2:B2)/2)<>COUNTA($B$2:B2)/2,"", will skip every other row, change the <> to a = if needed for your app.
OFFSET($A$2,COUNT($B$2:B3),0,1,1)) will lookup the reference value by counting "how far down the list you are" then offsetting accordingly.
I've tested this, and it seems to work. The OFFSET function lends itself to long formulas but elegant solutions. You could use conditional formatting to throw on a border if the cell has a value (half the cells will contain ""). Because of the length of this expression, if you are using multiple references in the column B formula you might want to split operations into several columns for ease of understanding the formula....
Hope this makes sense, & good luck!
RE: Either simple or impossible...
Second, this will actually put in blank rows so that the borders look like you wanted. **You can fill the column B formula down without skipping rows**. If it is critical that you do not skip any rows, but have a space between the cell borders, this is not the solution.
RE: Either simple or impossible...
Regards,
Vineet Maloo
RE: Either simple or impossible...
Consider Column B
YOu put the formula in first row cell
COpy the formula in next row cell i.e. row cell number 2
Border first row cell i.e. row cell number 1
Leave row number 2 borderless
Then Select both the row cells (row cell number 1
and 2)
click on the black square in the lower right corner and drag downward.
you have a row that is not bordered followed by a row that is bordered sequentially.
This is similar to jproj(chemical)
Please check & let me know if it is working.
RE: Either simple or impossible...
RE: Either simple or impossible...
The workaround is in the method of copying not by reformating the rows around the cells.
1. Insert one or more rows between each formula and
format cells as required.
2. Enable Datafilter and select "Non Blanks" cells
(This filters out all blank rows)
3. Copy 1st cell and paste into other cells
Use Paste Special if you only want to copy formula's
4. If this trick did not work for you then add a custom
command "Select Visible Cells" to your one of your
toolbars. To add this command. Right-click toolbar,
select Customize-Commands then Category: Edit and find
command "Select Visible Cells" at the bottom.
5. Now when you copy the 1st cell, highlight the cells you
want to paste to and click on "Select Visible Cells"
toolbar first and then Paste info
6. Now turn-off the Data Filter or select "All" from the
drop down box and you are all done.
The last feature is very handy and usefull when you are working on 3-d spreadsheets and want to paste information down to other grouped worksheets. It's a powerfull feature, which can save you quite a bit of time. Also tricky as you can mess up a workbook in a hurry if multiple worksheets are group together for editing.
Btw.
Play with Excel's Copy & Paste as it has some need features which can really speed up your work. Like copying non-continuous cells and pasting into continuous block. Copying one cell and paste it to non-continuous cells. Note this works only for one row or one column.
Hope this works
Krossview/OK
RE: Either simple or impossible...
--Steve