Frustration with Copying Conditional Formatting

[yesithinkso]

Hey,
Im trying to make a template with data for my dissertation, im trying to copy the a table and paste in the numbers so it will automatically format the data if it is above or below the a figure generated in the data. I can set up the conditional formatting and paste in the data but when i copy the table and input the new data it still uses the cell from the original table to condition the rest of the data, Ive been looking online for days and ive tried removing the $ sign or the format painter or different formulas but i cant get it working. There is too much data in total to manually condition each data set but i cant create a template so i can just paste into and speed things up. Anyone got any advice/suggestions?
Thanks

 

[meddem]

So there's a figure generated in the data, and you want conditional formatting based on where the data is relative to the figure?

I'm having a hard time understanding that.

 

[yesithinkso]

Sorry i may not have explained it the best, i have two columns, pre-test data and post test data, at the bottom of the pretest the template works out the mean and the standard dev. based on the data i paste in, i would like to condition the post test data red if it above the pretest standard deviation and green if it below the prestest standard deviation. Once i do it manually and then copy and paste the template it always sticks with the location of the standard deviation in the original template i.e. it does not reassign it to the standard deviation in the copied template.

Hope this is a little clearer
thanks

 

[meddem]

That is a little clearer, but it's not quite there yet. Let's go through this carefully to make sure we get on the same page.

i have two columns, pre-test data and post test data

Okay, let's say these are in columns A and B, respectively.

at the bottom of the pretest the template works out the mean and the standard dev.

So, what you're calling the 'template' occupies at least two cells in column A.

based on the data i paste in,

Are you talking about pasting in different pre-test or post-test data? (Or both?)

i would like to condition the post test data red if it above the pretest standard deviation and green if it below the prestest standard deviation

That seems clear enough.

Once i do it manually

Explain "manually". Condition formatting? Manually selecting cells and coloring them?

and then copy and paste the template

Okay, hold on. You didn't say anything about copying and pasting the template before this. Why do you do this?

it always sticks with the location of the standard deviation in the original template

Now we're running into pronoun/antecedent problems. What is the "it" that sticks with the location of the original STDEV? (I'm pretty sure you mean the formatting, since the STDEV is what you are copying to a new location.)

i.e. it does not reassign it to the standard deviation in the copied template.

What does not reassign what?


I have a couple ideas about what you're really after here, and either way the solution is pretty simple, but I'd like to make sure we understand each other.

EDIT: Especially the part about copying and pasting the template.

 

[yesithinkso]

So, what you're calling the 'template' occupies at least two cells in c


Are you talking about pasting in different pre-test or post-test data? (Or both?)
Yes, the current pre and post test data is for one subject so ideally i would paste each individual subjects data into what i call a template so its all conditioned under the same rules but the numbers will be different for each individual


Explain "manually". Condition formatting? Manually selecting cells and coloring them?

Yes, clicking on conditional formatting, selecting "greater than", selecting the cells then selecting "less than", selecting the cells

Okay, hold on. You didn't say anything about copying and pasting the template before this. Why do you do this?

I copy and paste the template so i can put in the pre and post test data for each subject and have a worksheet with all subjects in front of with the relevant data highlighted by the conditioned cells. As there is alot of subjects it would take too long to set up the conditional formatting "manually" as i call it.


Now we're running into pronoun/antecedent problems. What is the "it" that sticks with the location of the original STDEV? (I'm pretty sure you mean the formatting, since the STDEV is what you are copying to a new location.)

The "it" is the stdev cell, if i set it up "manually" then copy the "template" and paste in another subjects pre and post test data, the post test data will be conditioned by the stdev in the original "template" and not the stdev in the second subjects "template"


What does not reassign what?

By reassign i meant condition the copied "template" based on the newly generated stdev instead of still using the original one

Hope this makes more sense, i havent explained it the best

 

[meddem]

This is what I think I understand:

The template isn't just the cells that calculate standard deviation and average. It consists of everything for one test subject: their post-test data, their pre-test data, AND the calculations beneath.

You populate the template by pasting in a subject's test data from some other location, and each individual cell in the post-test data column of the template gets conditioned either green or red by comparing it to the cell that calculates the standard deviationof the pre-test data.

Once you've accomplished this, you copy the entire template, including the cells that calculate mean and standard deviation, to a different worksheet. And when you've done that, you find that the conditional formatting is still referencing the original standard deviation cell on the template worksheet, instead of the standard deviation cell you copied with all the rest of the data.

Did I get that right? Because the behavior you're describing makes it sound like you are not copying the cells where the calculations are done.