Copy&pasting coditional formatting... again

Hiya,

I know I've made a similar post not long ago, but I've tried to apply the same logic to a different spreadsheet and can't seem to get it working.

I've attached the workbook. As you can see you can insert data into the rows under columns C-I.

If you highlight cells C6:CI, you can see there are five sets of conditional formatting per row.

Firstly are the fill colours - if you change the 'transaction type', cell C6 will change accordingly. This affects four of the conditional formatting to change the colours also accordingly.

The fifth conditional formatting checks if the date is the same as the row below it, and if it is not the same, the border between the two rows is changed to solid line.

Have a little play around with entering some data into the three rows I have formatted to get your head round what my spread sheet does...

Now the problem, I need to make the rows go down to row 2000. I've tried to use autofill and copy&pasting but that totally mucks up the conditional formatting. I've also played around with all combinations of absolute and relative columns, rows and both. But I just can't seem to get it right.

Also, is there a way to make the 'transaction type' defaulted to '(Select from list)', and that if you deleted the data, that a no entry is not allowed in that cell? I have tried going to data validation and un-ticking 'ignore blank' but no change.

Thanks! Any help on both problems would be much-appreciated.

Thanks

I'm not 100% sure the theory behind your spreadhseet, but give this a try and let me know if it works they way you want it to?

Regards

SWM

Originally Posted by swmasson

I'm not 100% sure the theory behind your spreadhseet, but give this a try and let me know if it works they way you want it to?

Regards

SWM

Hi there,

Thanks for your time but it's not sorted the problem...
The theory behind the spread sheet is that you can keep track of every money transaction in a business. You type a date and time, then you can select the type of transaction, give it a short and long description.

What happens is that the type of transaction you can choose from is a list, linked to the second sheet 'add options'. If I choose 'money received' for example, the 'add options sheet' states that this is an 'Income', so coulmn C on the 'main sheet' displays 'INCOME'.

From this the conditional formatting changes the colours. according to if 'Income' or 'Outcome' is in column C.

Now, if you see your version, 1) for some reason there is an error in the whole of column C, and 2) if you see where the conditional formatting applies to, it's gone all over the place.

Thanks for your time though, I hope my spreadsheet makes some sense. play around with mine attached above and see if you can work out how to use it.

Thanks!

i inderstand it better now... will play around see what i can come up with...

what is column C supposed to be ause just now it reads "=#VALUE!"

Regards

SWM

Originally Posted by swmasson

i inderstand it better now... will play around see what i can come up with...

what is column C supposed to be ause just now it reads "=#VALUE!"

Regards

SWM

re-download my file from my first post - it has all the correct formatting and formulas

Thanks