If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autoformatting...kinda?
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 --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Autoformatting...kinda?
Hi
for your shading you can just modify your conditional format formula to =AND(MOD(ROW(),2),$a11) assuming that your data is pasted into the first column. Frank |
#3
|
|||
|
|||
Autoformatting...kinda?
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. "abxy " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Autoformatting...kinda?
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. --- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
Autoformatting...kinda?
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 |
#6
|
|||
|
|||
Autoformatting...kinda?
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 --- Message posted from http://www.ExcelForum.com/ |
#7
|
|||
|
|||
Autoformatting...kinda?
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 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 --- Message posted from http://www.ExcelForum.com/ |
#8
|
|||
|
|||
Autoformatting...kinda?
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"",$C10,$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 |
#9
|
|||
|
|||
Autoformatting...kinda?
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'! --- Message posted from http://www.ExcelForum.com/ |
#10
|
|||
|
|||
Autoformatting...kinda?
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 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'! --- Message posted from http://www.ExcelForum.com/ |
|
Thread Tools | |
Display Modes | |
|
|