Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Either simple or impossible... 1

Status
Not open for further replies.

Morglisn

Industrial
Jan 31, 2003
65
US
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.
 
Replies continue below

Recommended for you

You could use a smaller font and/or increase the row height to get space between the rows. Does that help?
 
No, my problem is the border surrounding the cells. If I decrease the font size, the borders still butt against one another, with no space between. If I increase the row height, the borders follow the height of the row, and I'm left with the same problem.

Thanks, though!!
 
Maybe split the cells surrounding the cell you work with into two colums would make it.

Just my two cents

Cyril Guichard
Mechanical Engineer
 
You can perhaps:
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 ( 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.
 
Morglisn:

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
 
No, I want the rows sequential so I can copy my formulas down and have the references increment properly (by +1). If I add a blank row in between, for my unbordered space, my references increment by two when I copy.
 
Is this what you want (in general):

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.
 
while i am short on time and do not have my pc with me, i recently (i.e. w/in past 2 months) came across a good technique for using the conditional formatting tool, that is special formatting based on alternating or every x rows. it may be of interest to you. i cannot remember the website (perhaps, this forum referred to it). check previous posts in this forum. when conditions allow, i will post the technique (might b a day or 2 - traveling from ak to contential us).
-pmover
 
how about if you drag out the cells (no spaces, +1 increments), then insert the rows? You could also fill in formulas in the cells, then drag them to their desired locations.
 
There is no way to get a separate border around to consecutive rows, so that they show as 2 separate borders.

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
 
It is a bit of work but here is one way:
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)


 
As menioned above conditional formating will do it. the condition test looks to see if the row number is odd or even and formats the cell accordinatley.

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.



 
I didn't see these options offered, so I'll offer them.

> 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
 
I could do that, but I don't want to have to type in each reference individually. I have quite a few formulas, and I was hoping to just drag them down, having them increment.. If I have to insert the $, it's going to be quite a bit of clicking around.

Thanks, though!
 
You don't need to type them individually; you can simply do a global find and replace on the selection. Select the range of cells, hit [ctrl]-h, enter the column designator in the find, enter the column designator followed by the "$" and replace all.

TTFN
 
You can do this by breaking it into two "parts". The first part is a condition to see if you are displaying or skipping the row. The second part would use the OFFSET function in conjunction with COUNT. This would be in place of the actual reference in your formula, so that the location of the reference is computed, then "looked up". It is hard to provide the exact syntax you would need without an example, but in general what you could do is embed the COUNT function into the OFFSET function.

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,&quot;&quot;,OFFSET($A$2,COUNT($B$2:B2),0,1,1))
R3 =IF(INT(COUNTA($B$2:B3)/2)<>COUNTA($B$2:B3)/2,&quot;&quot;,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),&quot;&quot;)

Here's the breakdown on the steps:
IF(INT(COUNTA($B$2:B2)/2)<>COUNTA($B$2:B2)/2,&quot;&quot;, 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 &quot;how far down the list you are&quot; 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 &quot;&quot;). 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!




 
Okay, I missed a couple things on my last post. Ignore the R4 Column B formula, this is supposed to be a fill down of the one above.

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.

 
If your problem is to get little gaps between consecutive row borders. Double borders should be helpful. Or otherwise using conditional formating you can have borders in alternative rows.

Regards,

Vineet Maloo
 
mand(Industrial)

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.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor