Excel help - I dont quite know my way around it

  Bob Bob™ 16:22 18 Aug 2010
Locked

Hey all.

Im mixing an album for a band and I decided the best way to keep track of the time Im working on songs is to get a time sheet template for Excel and adapt it slightly for what I need it for.

click here|

Thats the one I got. Im sure there are better templates to use but this looked like just what I needed.

I decided that Ill print it off for the band so they can see what I was up to etc.

Im not a regular user of Excel but I know my way around the basics of Office 2007 and this is the situation Im in.

There are 12 songs that need working on. I fill in the template saying the song name, description of work, the date(scheduled in the template), time started and finished and it calculates the money. Very handy.

Though if a job gets split across two days or morning and evening then it has two separate rows, and a third if I do something else some other time which could be the weakness of the template Im using.

At the end I then plan to use the sort function so that all the songs are grouped together for clarity, then sorted by date and time and its great. It makes things easier to read but:

Im just wondering:
Lets say after I sort it the song name column looks like this:

A song
B song
B song
B song
C song
D song

Can I set up excel to fill the rows a certain colour so its like this

A song (white fill)
B song (light blue fill)
B song (light blue fill)
B song (light blue fill)
C song (white fill)
D song (light blue fill)

This way song is much easier to find and read on a page, to me anyway.

So, if this template is a success Ill use it all the time and it would be nice if that process, if it can be done, is semi automated or done a few clicks rather then a whole bunch.

A second problem is that I if I hightlight a group of cells and I right click to format, I cant. I click it but nothing pops up. Some other cells on their own can be formatted but in the main body of the template they cant.

Thanks all

  VoG II 16:47 18 Aug 2010

Take a look at Conditional Formatting

click here

click here

However, if you are using Excel 2003 or earlier you are limited to three colours. In which case you will need code click here

  Simsy 22:11 18 Aug 2010

If conditional formatting looks like it will do what you want...

Instead of using A, B, C etc for the songs use 1,2,3 etc...

Then you can use conditional formatting basing the fill colour using a formula with "ISODD" or "ISEVEN"

Does that make sense?

Regards,

Simsy

  Bob Bob™ 23:00 18 Aug 2010

Ill check the links Vog posted. Thanks.

Simsy, I looked at conditional formatting already and couldnt quite get it to work my way. It would only fill the cells to start with, and not the whole row that the cell is contained in.

It doesnt make sense just quite yet but Im sure after some reading from Vogs links and practising Ill get it.

At the moment I am naming the songs as their actual titles. The A, B and C thing are just examples but I would be fine with having a number before the name.

1 Me and my Banjo rather (white)
1 Me and my Banjo (white fill)
2 Small name (blue fill)
3 Song (white fill)
3 Song (white fill)


If I wanted to have certain tasks that dont have to do with just one song, say 4 of them could I set conditional formatting so

"z - Domestic testing" could be last in my list but also get the same colour treatment?

Thanks

  Bob Bob™ 23:36 18 Aug 2010

OK, so I was playing around and maybe the way I have the template setup isnt going to help the conditional formatting.

So what about this.

In this album there will be 12 songs
So, If I enter that data somewhere to the side

1
2
3
4
5
6
7
8
9
10
11
12

Then in the cells beside those have the song titles they correspond to

Could I then (if you look at the template)

Type in the song number in the DAY column and have Excel pop the song name into the NAME column. That way conditional formatting should work out?

So all I would have to do is type in the song number, press tab twice then enter in what Im doing etc.

Then sort it by numbers and apply the formatting?

Sorry if it doesnt make sense :p

  Bob Bob™ 01:36 19 Aug 2010

Well I got some of it.

What I can now do is in the most left column I type in a number. Then a LOOKUP formula lets excel pop the song name into the right cell. The info is from a table on the side of the sheet so I type in the number, press tab twice and then enter what Im doing.

Works out better this way anyway :) Quite chuffed with myself. Took me ruddy ages.

Though I cant get the conditional formatting to work the way I want, if its possible in the first place.

Now, I have it at every second row is shaded through the conditional format, then I just used the Table styles thing.

For clarity I would like is this.
No. Song name:
1 My way (whole row white)
1 My way (whole row white)
2 New York New York (whole row blue)
3 No one knows (whole row white)
3 No one knows (whole row white)
4 Another Song (whole row blue)

I cant get the ISODD or ISEVEN formula to work with shading the whole row.

Thanks all!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone