Autoformatting...kinda?

  • Thread starter Thread starter abxy
  • Start date Start date
A

abxy

Ok, I've created a worksheet with some alternating shaded rows using
conditional formatting and formula is: =MOD(ROW(),2). and I've
customized some cells (the same cells on every row) with formulas to do
some needed calculations when I paste data onto the worksheet from
notepad or Word, no biggie. Now here's where the problem comes in,
Everyday, there's a different amount of data, some days it's only 10
lines, some days it's 105 lines of data. Therefore i've had to make an
excess of these alternating shaded rows, and after I paste in the data,
delete the unused rows, or select everything i want printed and set
that as the print area. Is there anyway I make it so that when I paste
in the data, my alternating shaded rows(complete with my cells that i
have formulas in) will continue in there pattern to accomadate for the
number of lines of data pasted in? A bonus would be if i could make it
so that the very last row (the row after the last line of data pasted
in) is a different type of row that will sum up the vaules in the rows
pasted.

Thanks in advance
 
Hi

for your shading you can just modify your conditional format formula to
=AND(MOD(ROW(),2),$a1<>1)
assuming that your data is pasted into the first column.

Frank
 
Hi,

Assuming your data starts in A1

Conditional Format Condition1 (Last Line of data):
=AND($A1<>"",$A2="") ' Formatted Blue or whatever

Condition Format Condition2: 'Grey formatted
=AND(MOD(ROW(),2),$A1<>"")

Regards,

Daniel M.
 
thanks, but that doesn't work. Maybe I wasn't being clear though.

Basically here's what's happening and here's what i need. The data that
i paste in starts on the 8th row, that's also where my alternating
shaded rows start, and some of the cells on everyone of those rows also
have formulas in them to perform calculations on the data that i paste
in. I need it so that every row after the 7th that contains any data in
any cells will not only follow the alternating shaded rows pattern, but
will also have those formulas in those cells that i have in every row.
But, I don't want anything in rows that have any data in any on the
cells...if that makes sense.
 
Hi again

so if i understood you correctly in addition to the conditional format
you have to change your formulas in the following wax (assuming that
you paste your data in column A)

=IF(A1<>"",[Type in your specific formula], "")

Frank
 
Ok, now this actually works, Thanks a mil., but i'm not quite there yet,
This formula is only making the particular cells that I put data in
follow this pattern. I need it so that, If there are any cells with
data in them, the entire row will then follow the pattern.

Thanks
 
i do not quite understand your problem. Of course you have to fill this
IF formula for the entire row. Please specify what you mena with 'any
cells'. Also an example row would help :-)

Frank
 
well, actually, I just got it under conditional formatting so that, If
any data is entered in the first cell of a row, the entire row (A to L
actually[but that's what i wanted]) will follow my alternating shaded
rows pattern (this is acutally a good thing...i hope it doesn't come
back to bite me in the butt later though :/ ). However, now I am having
trouble autofilling this conditional format. Here's my conditional
format formula:

=IF($A$1:$L$1>" ",MOD(ROW(),2)," ")

and the second row:

=IF($A$2:$L$2>" ",MOD(ROW(),2)," ")

and so forth...

Now, the problem is when i try to autofill to cut time, Instead of
counting like I thought it would:

$A$1:$L$1
$A$2:$L$2
$A$3:$L$3... ...$A$10:$L$10

It patterns like this:

$A$1:$L$1
$A$2:$L$2
$A$1:$L$1
$A$2:$L$2... ...$A$1:$L$1

How can I get Excel to count up? It seems to not work when those "$"
are included in the formula
 
I don't think this kind of conditional format will work as the formula
in the conditional dialog has to return either TRUE or FALSE.
What you can do if you want to check if any cell in one column (ranging
from A to L in your example is filled) is the following formula:
=AND(OR($A1<>""´,$B1<>"",$C1<>0,$D1<>"",
[.......],$L1<>"" ),MOD(ROW();2))

This will ensure your shading. For entering this conditional format do
the following:
1. Highlight all the rows and columns for which you want to apply this
conditional format
2. Enter the formula (I assume that A1 ist your upper left corner).

Frank
 
Hah! I can't believe it, this is EXACTLY what I wanted. To tell the
truth I don't understand how this formula works though(possibly because
I don't know what the AND and OR functions do yet) but, heh, It
definately works!

Thanks a mil'!
 
You're welcome. for AND and the OR function, quite simple:
With AND all conditions have to be fullfilled to evaluate to 'TRUE'
With OR at least one condition has to be evaluated to 'TRUE'

Frank
 
I decided not to make a new thread since this question kinda falls unde
that last few post in this topic. I'd think that i'd already know ho
to do this, but...well, i can't.

I need to make a conditional formula so that when one cell (B1
displays a certain string of text (hello), cell A1 acts on it'
conditional format
 
But, I don't want anything in rows that have any data in any on the
cells...if that makes sense.
Click to expand...

You have to modify the formulas in the cells to return "" (i.e. the empty
string, will appear blank) if the cells are blank.

Also modify the conditional formatting formula to not format the cell if
the appropriate cell is blank.
 
Highlight A1 and go to 'conditional format'. enter the formula
=B1="hello"

and set your format
Frank
 
works!

hmm, as simple as that was, I don't know why it didn't come to me.

Anyway, thanks a lot, that was racking my brain for at least an hour.

Thanks again
 
Back
Top