Author Topic: Excel help  (Read 59476 times)

Offline choi

  • of sex.
  • Legacy Fan
  • ******
  • Posts: 5,782
  • Toto
Excel help
« on: March 3, 2011, 06:56:51 pm »
Excel experts, i need your help!

Basically i have 2 columns of data, but some of the rows aren't filled in.

I only want to use the rows where both columns are filled in, and ignore those where there is a an empty cell.
Can anybody help me?

I was looking at trying to make a third column which had this in but it didn't work:
=IF(ISTEXT(A2),IF(ISTEXT(B2),"true","false"))

Offline choi

  • of sex.
  • Legacy Fan
  • ******
  • Posts: 5,782
  • Toto
Re: Excel help
« Reply #1 on: March 3, 2011, 07:07:35 pm »
Edit, figured it out now...

But now how do i delete the rows that contain the string "false" in a certain column?
Bear in mind theres thousands of rows of data...

Offline Party Phil

  • Boring Cunt that flies Air Bizarre
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 12,552
  • Big in Japan
Re: Excel help
« Reply #2 on: March 3, 2011, 07:12:56 pm »
Have you ever used Excel before? Why not just filter both columns for blanks and delete the rows that are left rows?
If you're lying, I'll chop your head off.

Offline choi

  • of sex.
  • Legacy Fan
  • ******
  • Posts: 5,782
  • Toto
Re: Excel help
« Reply #3 on: March 3, 2011, 07:40:16 pm »
Because then the 2 columns would get mixed up as there arent the same number of blank rows.
each row is 1 persons data.

Offline Operation Hennessey

  • RAWK Supporter
  • Anny Roader
  • ******
  • Posts: 380
Re: Excel help
« Reply #4 on: March 3, 2011, 07:55:24 pm »
Filter to non-blanks then copy and paste?

Offline Party Phil

  • Boring Cunt that flies Air Bizarre
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 12,552
  • Big in Japan
Re: Excel help
« Reply #5 on: March 3, 2011, 08:19:44 pm »
Because then the 2 columns would get mixed up as there arent the same number of blank rows.
each row is 1 persons data.

Ok, I actually just remembered that I'm giving up helping retards for lent. Looks like I may as well start early.
If you're lying, I'll chop your head off.

Offline rednich85

  • Gargantuan Wanker. Intimately linked to Keys and Gray.
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 17,631
  • Stay Black. That's the most important thing.
Re: Excel help
« Reply #6 on: March 3, 2011, 08:21:40 pm »
hahaha come on Phil mate.

There's many ways to skin a cat.....especially on excel.

I find its better to leave someone to their own devices and let them work it out for themselves.
"Smart people believe weird things because they are skilled at defending beliefs they arrived at for non-smart reasons."

@rednich85

Offline choi

  • of sex.
  • Legacy Fan
  • ******
  • Posts: 5,782
  • Toto
Re: Excel help
« Reply #7 on: March 3, 2011, 08:27:09 pm »
Filter to non-blanks then copy and paste?

Ta. So simple :D

Offline choi

  • of sex.
  • Legacy Fan
  • ******
  • Posts: 5,782
  • Toto
Re: Excel help
« Reply #8 on: March 3, 2011, 08:27:56 pm »
Have you ever used Excel before? Why not just filter both columns for blanks and delete the rows that are left rows?

Just realised what you meant ;D

Offline ChaChaMooMoo

  • From doubters to believers - Klopp 2015
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 6,870
  • Justice shall prevail.
Re: Excel help
« Reply #9 on: May 31, 2011, 10:25:50 am »
I have a weird problem with MS excel.

I have a formula in C22 and C24. But it does not calculate. It just displays the formula. No idea why. I tried changing the cell properties from 'standard' to 'number'. But it just displays the formula.

I have attached the screenshot of the problem.

Please help.  :wave
Ta.  :)

Offline Sudden Death Draft Loser

  • old and annoying
  • Legacy Fan
  • ******
  • Posts: 9,483
Re: Excel help
« Reply #10 on: May 31, 2011, 10:38:29 am »
I have a weird problem with MS excel.

I have a formula in C22 and C24. But it does not calculate. It just displays the formula. No idea why. I tried changing the cell properties from 'standard' to 'number'. But it just displays the formula.

I have attached the screenshot of the problem.

Please help.  :wave
Ta.  :)

works ok for me, you have commas instead of full stops in your numbers
"The greatest argument against democracy is to have a five minute conversation  with the average voter. "

Offline ChaChaMooMoo

  • From doubters to believers - Klopp 2015
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 6,870
  • Justice shall prevail.
Re: Excel help
« Reply #11 on: May 31, 2011, 10:40:37 am »
works ok for me, you have commas instead of full stops in your numbers

I am using the German number system. So the commas and points are interchanged.

Offline Craig S

  • KOP CONDUCTOR
  • Legacy Fan
  • ******
  • Posts: 4,979
Re: Excel help
« Reply #12 on: May 31, 2011, 10:52:17 am »
I have a weird problem with MS excel.

I have a formula in C22 and C24. But it does not calculate. It just displays the formula. No idea why. I tried changing the cell properties from 'standard' to 'number'. But it just displays the formula.

I have attached the screenshot of the problem.

Please help.  :wave
Ta.  :)

press CTRL + `  (the apostrophe above tab on the left)
That should toggle between showing the formula or the result
« Last Edit: May 31, 2011, 10:54:00 am by Craig S »

Offline ChaChaMooMoo

  • From doubters to believers - Klopp 2015
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 6,870
  • Justice shall prevail.
Re: Excel help
« Reply #13 on: May 31, 2011, 11:45:26 am »
press CTRL + `  (the apostrophe above tab on the left)
That should toggle between showing the formula or the result

Although I use the german version, I was able to find out the equivalent command and the problem is now solved.

Thanks.  :wave

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: Excel help
« Reply #14 on: August 26, 2011, 12:33:38 pm »
Bumped because I have an Excel question.

Is it possible to link to an already written and formatted email from an Excel cell that contains an email address?

I've got a list of email addresses in one column and want to be able to just click on the email address and have that open up the email with the email address conveniently placed in the To field.

The body of the email will always be the same btw.

Can this be done?
Sit down, shock is better taken with bent knees.

Offline Roady

  • Streety's long lost brother. AKA the Shit Buhunt.
  • Legacy Fan
  • ******
  • Posts: 8,409
Re: Excel help
« Reply #15 on: August 26, 2011, 12:40:27 pm »
i hate excel. Ive never liked it.Ive had to use it now and again for work but i still just make it up as i go along. seems to work out ok most of the time.
Giant sponges. That is the answer for flooding.

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: Excel help
« Reply #16 on: August 26, 2011, 12:43:48 pm »
Er.. thanks?
Sit down, shock is better taken with bent knees.

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: Excel help
« Reply #17 on: August 26, 2011, 12:48:00 pm »
Do you want to send the email to them all. If you use Outlook, you should create a new mail message. You can then select Tools, Letters and Mailings, Mail Merge. Follow the wizard and you can choose your Excel spreadsheet as your source of recipients in step 3 of the wizard.

Offline Party Phil

  • Boring Cunt that flies Air Bizarre
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 12,552
  • Big in Japan
Re: Excel help
« Reply #18 on: August 26, 2011, 01:16:30 pm »
If it's a nice short email you could edit each email address hyperlink to the form mailto:abc@xyz.com&subject=Hello&body=Some+text+here

Otherwise I'd probably use VBA to write some code for the task, if SP's mail merge idea doesn't suit you.
If you're lying, I'll chop your head off.

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: Excel help
« Reply #19 on: August 26, 2011, 02:14:34 pm »
Thanks for the ideas, guys.   I can't use the mail merge because it's an on going project which needs emails generated for certain people every now and then, but we don't know who until the day.  Also the subject line will need editing for each job.

I'm reluctant to get bogged down with VBA but I guess that's probably the only way I can do this as the email body text is pretty big.

Oh well.
Sit down, shock is better taken with bent knees.

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: Excel help
« Reply #20 on: August 26, 2011, 02:41:50 pm »
Thanks for the ideas, guys.   I can't use the mail merge because it's an on going project which needs emails generated for certain people every now and then, but we don't know who until the day.  Also the subject line will need editing for each job.

I'm reluctant to get bogged down with VBA but I guess that's probably the only way I can do this as the email body text is pretty big.

Oh well.

Any reason why you can't cut and paste the relevant addresses? You could use hold down the control key, select all of the relevant ones and paste them into Notepad.

Then copy and paste them again into the To: box. Use BCC if you want to hide the list.


You can slick this up a bit by adding 2 columns. The first should contain an X if you want to send them an email. Then in the next column use an =IF(X2="X", W2, "") type of formula. You can then just paste the whole of this column to get your list.   
« Last Edit: August 26, 2011, 02:44:18 pm by Southern Pansy »

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: Excel help
« Reply #21 on: August 26, 2011, 02:47:21 pm »
I'm going for speed.  Currently the people doing the work have to copy the address, find/open the email template in Outlook, paste the address in.

I'm looking for a way to do all that (even though it's not THAT much) with one click from the spreadsheet containing the email addresses.
Sit down, shock is better taken with bent knees.

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: Excel help
« Reply #22 on: August 26, 2011, 02:51:07 pm »
I'm going for speed.  Currently the people doing the work have to copy the address, find/open the email template in Outlook, paste the address in.

I'm looking for a way to do all that (even though it's not THAT much) with one click from the spreadsheet containing the email addresses.

Unless you are generating loads of these, you are not going to recoup the development time of a VBA fix...

If the email bodies are all the same. Send one e-mail, and have a huge CC list. You may need to append a comma or a semicolon (depending on the email client) to each email address. But sending it as one mail should speed the whole thing up mightily.

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: Excel help
« Reply #23 on: August 26, 2011, 03:01:09 pm »
No can do, we have to change the subject line on each email.
Sit down, shock is better taken with bent knees.

Offline redbyrdz

  • No to sub-optimal passing! Not content with one century, this girl does two together. Oh, and FUCK THE TORIES deh-deh-deh-deh!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 24,186
Re: Excel help
« Reply #24 on: August 26, 2011, 11:28:01 pm »
If it's a nice short email you could edit each email address hyperlink to the form mailto:abc@xyz.com&subject=Hello&body=Some+text+here

Otherwise I'd probably use VBA to write some code for the task, if SP's mail merge idea doesn't suit you.

Following on from Phil's idea...

Have your usernames/email addresses in one columns.
Put the subject lines into the next column.
Write the message body into some cell somewhere.

Use excels' concatenate function to link the bits of string together:
=CONCATENATE("mailto:",A1,"&subject=",B1,"&body=",$F$1)
Assuming you have the addresses in column A, the subject lines in B1 and the body text in F1

Then make a hyperlink out of that cell using the hyperlink function
=HYPERLINK(CONCATENATE("mailto:",A1,"&subject=",B1,"&body=",$F$1),A1)
The first bit is the link target and the second bit is some random text, here I used the username from column A again.

Then just copy+paste that down the column. It should give you a clickable cell that will automatically open your email program (check this is selected to happen when you click a mailto link).




.... and stop using excel. its shite. :wave
« Last Edit: August 26, 2011, 11:30:59 pm by redbyrdz »
"I want to build a team that's invincible, so that they have to send a team from bloody Mars to beat us." - Bill Shankly

Offline Party Phil

  • Boring Cunt that flies Air Bizarre
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 12,552
  • Big in Japan
Re: Excel help
« Reply #25 on: August 27, 2011, 12:01:05 pm »
.... and stop using excel. its shite. :wave

tell us what is better then, o wise master?
If you're lying, I'll chop your head off.

Offline redbyrdz

  • No to sub-optimal passing! Not content with one century, this girl does two together. Oh, and FUCK THE TORIES deh-deh-deh-deh!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 24,186
Re: Excel help
« Reply #26 on: August 27, 2011, 01:02:07 pm »
tell us what is better then, o wise master?
Depends on the task? The problem with Excel is that people use it for all kinds of things, but there are often simpler, smaller, and quicker solutions available. In tubby's case, why do the usernames end up in excel in the first place? If its just to send them emails occasionally, wouldn't it be easier to add them to an address book and do everything inside their mail client?
"I want to build a team that's invincible, so that they have to send a team from bloody Mars to beat us." - Bill Shankly

Offline Graeme

  • Slightly Undergay RAWK PC Support
  • Legacy Fan
  • ******
  • Posts: 14,891
The Microsoft Excel Help Thread
« Reply #27 on: August 17, 2012, 02:52:00 pm »
There's a few of these and I need to post another, so here's a thread to post all of our Excel woes in.

Here's my dilemma today: -

I work for an Exam Board, and our assessors around the UK are given videos to watch and mark online, with the marks compared to what the candidates should have received - to check their marking isn't too harsh or too lenient etc.

I've got a spreadsheet with all the assessor names down the left, and all the candidates across the top. The row above the assessors grades has the correct and actual grade on for each candidate.

The grading is as follows: -

D (Distinction)
M+ (Merit Plus)
M (Merit)
GP (Good Pass)
P (Pass)
N (Nearly)
U (Unsuccessful)

I need to format it so if they get the grade correct then the cell goes green. If they're one out either side then its yellow, two out its orange and 3 or more out then its red.

Any suggestions where to start?

Offline Slick_Beef

  • RAWK's Master Baker
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 9,087
Re: The Microsoft Excel Help Thread
« Reply #28 on: August 17, 2012, 04:49:24 pm »
Hi mate,

The only problem I see with that is that to compare how far out each grade is you need to assign each grade a numerical value. (At least, I haven't come up with an alternative anyway)

You can declare constants in excel, so for example, D=7, M+=6... etc. so that partly solves it. The downside there is that if you write D in a cell, excel will treat it as a text string "D" rather than numerical constant 7 unless you have an equals sign in front of it, so you'd have to add an equals sign in front of every grade in the grid.

It's a bit tricky so explain so I've thrown an example together below..

http://ben.iteachers.eu/rawk/xgrid.xlsx

You can write =D and =M in the cells and it will convert them to their numeric values automatically and make the colour based on the calculation.

Hope that helps a little. If you do want to use it and need help expanding it to the full grid give us a shout (all the rules for the colours and which cells they apply to are in the conditional formatting section)

I'm no excel expert so there may be some other tricks to make it easier that others can suggest


p.s. I couldn't set M+ as a constant because excel reserves the use of the + symbol so I used MP instead

Offline smithy

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 2,011
Re: The Microsoft Excel Help Thread
« Reply #29 on: August 17, 2012, 05:06:05 pm »
I'd use a vlookup on a table that assigns numbers to grades. Then do the conditional format on the sum of (vlookup1 - vlookup2).

Don't know about later versions of excel, but 2003 only allows 3 conditional formats on each range...
« Last Edit: August 17, 2012, 05:11:19 pm by smithy »

Offline TepidT2O

  • Deffo NOT 9"! MUFC bedwetter. Grass. Folically-challenged, God-piece-wearing, monkey-rubber. Jizz aroma expert. Operating at the lower end of the distribution curve...has the hots for Alan. Bastard. Fearless in transfer windows with lack of convicti
  • Lead Matchday Commentator
  • Legacy Fan
  • ******
  • Posts: 93,586
  • Dejan Lovren fan club member #1
Re: The Microsoft Excel Help Thread
« Reply #30 on: August 17, 2012, 06:05:06 pm »
You could just use conditional formatting......

If the cell is equal to D, select format and then green ....or whatever colour...

Probably easier than working a formula and you can also pick arrows and smiley faces as well if you're into that kind of stuff
“Happiness can be found in the darkest of times, if one only remembers to turn on the light.”
“Generosity always pays off. Generosity in your effort, in your work, in your kindness, in the way you look after people and take care of people. In the long run, if you are generous with a heart, and with humanity, it always pays off.”
W

Offline Slick_Beef

  • RAWK's Master Baker
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 9,087
Re: The Microsoft Excel Help Thread
« Reply #31 on: August 17, 2012, 06:22:36 pm »
You could just use conditional formatting......

If the cell is equal to D, select format and then green ....or whatever colour...


The case when they are equal is the easy part ;D deciding how different "U" is from "D" is more tricky!

That vlookup could work better than the constants i used in my example I've never tried it before..

Offline TepidT2O

  • Deffo NOT 9"! MUFC bedwetter. Grass. Folically-challenged, God-piece-wearing, monkey-rubber. Jizz aroma expert. Operating at the lower end of the distribution curve...has the hots for Alan. Bastard. Fearless in transfer windows with lack of convicti
  • Lead Matchday Commentator
  • Legacy Fan
  • ******
  • Posts: 93,586
  • Dejan Lovren fan club member #1
Re: The Microsoft Excel Help Thread
« Reply #32 on: August 17, 2012, 06:27:13 pm »
The case when they are equal is the easy part ;D deciding how different "U" is from "D" is more tricky!

That vlookup could work better than the constants i used in my example I've never tried it before..
Bugger.... Now I read it again......


I would do exactly as you said then...

I do this to measure students progress against target grades.... -1 is amber -2 is red etc
“Happiness can be found in the darkest of times, if one only remembers to turn on the light.”
“Generosity always pays off. Generosity in your effort, in your work, in your kindness, in the way you look after people and take care of people. In the long run, if you are generous with a heart, and with humanity, it always pays off.”
W

Offline Thush

  • Spawwow, Tit. Anal Chat is "Equidistant between chit-chat and analysis"
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 3,072
  • It's pronounced "Toosh"
Re: The Microsoft Excel Help Thread
« Reply #33 on: August 17, 2012, 07:13:25 pm »
I would do this:
1. Have a data entry sheet with your students along the top, actual grade, then each assessor's grades
2. Have a mapping sheet that contains a map between the grade character and a numeric score (D = 1, M+ = 2, etc)
3. Have a sheet that replicates the students, actual grade and assessors, but converts the grade character to the numeric value using VLOOKUP
4. Have a final output sheet that calculates the absolute difference between each assessor's grade number and the actual grade number and apply conditional formatting on that sheet only.

Example screenshots
1. Data entry table



2. Mapping table



3. Convert text grade to numeric value



4. Calculate difference and apply conditional formatting



5. Example if assessor a3 changes grade for student c5 from U to M+



Note only three rules allowed in Excel 2003 and below for conditional formatting. Can send you my spreadsheet if you like.


Assumptions
Four worksheets
1. DataEntry - contains original text grades and actual grades
2. Convert - contains text grades converted to numeric values
3. Output - contains calculated difference between assessor grades and actual grades and conditional formatting
4. Map - mapping between text grade and numeric value

Formulas
1. In Convert worksheet, use VLOOKUP to convert text grade to numeric value (assuming mapping is in worksheet called "Map" in cells A1 to B7) and the data entry is in a worksheet called "DataEntry" with data starting in B2 (actual grade for student #1)

=VLOOKUP(DataEntry!B2,Map!$A$1:$B$7,2,FALSE)

This can be copied to the other cells.

2. In Output worksheets, use ABS to calculate absolute difference between assessor grade and actual grade

=ABS(Convert!B$2-Convert!B3) in assessor #1/student #1 intersection cell

This can be copied to the other cells.
« Last Edit: August 17, 2012, 09:25:06 pm by Thush »

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: The Microsoft Excel Help Thread
« Reply #34 on: September 27, 2012, 01:03:56 pm »
Hoping one of you might be able to help me with a couple of formulas I want to combine into one.

The first one looks up a cell with a date in it and returns a date 5 days in advance, unless the cell is empty, then it returns 'no date'.

=IF(E38="","no date",WORKDAY(E38,5))

Second one is similar but checks whether the text 'review' appears in another cell before returning a date 3 days in advance or 'no date'.

=IF(ISNUMBER(FIND("review",C38)), WORKDAY(E38,3), "no date")

I tried combining them (below), but I'm not great at nesting stuff and Excel was having none of it.

=IF(OR(E38="","no date",WORKDAY(E38,5)),(ISNUMBER(FIND("review",C38), WORKDAY(E38,3), "no date")))


Any suggestions?

EDIT:

Neither of the below formulas seem to work either:

=IF(OR("no date"),IF(ISNUMBER(FIND("review",C38)),WORKDAY(E38,3),WORKDAY(E38,5)))

=IF(OR(IF(ISERROR(SEARCH("review",C38)),"no date",WORKDAY(E38,5))),IF(ISNUMBER(FIND("review",C38)),WORKDAY(E38,3),WORKDAY(E38,5)))

I think I'm starting to not see the wood for the trees with this.
« Last Edit: September 27, 2012, 05:41:16 pm by tubby »
Sit down, shock is better taken with bent knees.

Offline smithy

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 2,011
Re: The Microsoft Excel Help Thread
« Reply #35 on: September 27, 2012, 10:51:12 pm »
=IF(E38="","no date",IF(ISNUMBER(FIND("review",C38)),WORKDAY(E38,3),WORKDAY(E38,5)))

This kind of thing? I've assumed "review" takes priority.

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: The Microsoft Excel Help Thread
« Reply #36 on: September 28, 2012, 09:23:31 am »
=IF(E38="","no date",IF(ISNUMBER(FIND("review",C38)),WORKDAY(E38,3),WORKDAY(E38,5)))

This kind of thing? I've assumed "review" takes priority.

Perfect!  That's exactly what I was looking to do, thanks.
Sit down, shock is better taken with bent knees.

Offline Tomaldinho

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 7,058
  • camina, camina, con esperanza de tu corazon
Re: The Microsoft Excel Help Thread
« Reply #37 on: December 28, 2012, 10:02:24 am »
Looking to do something similar with a spreadsheet where I'm making appointments to train people on Windows 7, I want to record the time requested (lets say in column G) and the time agreed (would be in column I). Would like the value in column I to go green if it matches G and red if it is higher or lower.
Obviously that's easy enough to do for one row but incredibly time consuming to set up 3 rules for each row (probably over 150 rows in total).
It doesn't seem to work  to copy the cells down because it will all refer back to G1 whereas obviously I need I2 to go off G2, I3 off G3 etc.

I'm an absolute dinosaur with excel. Any ideas?
@TomNJones on Twitter

Offline Roady

  • Streety's long lost brother. AKA the Shit Buhunt.
  • Legacy Fan
  • ******
  • Posts: 8,409
Re: The Microsoft Excel Help Thread
« Reply #38 on: December 28, 2012, 10:06:23 am »
i detest excel with a passion.It is just horrible to use for basic stuff.SOmeone should bring out something similar where you can just type into a field "i want to do x,y,z" and it does it for you.I try to avoid it as much as possible and thankfully in my job rarely have to use,just as well really.SOd the formulas, should just be able to type what you want and it works.
Giant sponges. That is the answer for flooding.

Offline Thush

  • Spawwow, Tit. Anal Chat is "Equidistant between chit-chat and analysis"
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 3,072
  • It's pronounced "Toosh"
Re: The Microsoft Excel Help Thread
« Reply #39 on: December 28, 2012, 10:20:28 am »
Are you using conditional formatting for colour the cells in? If so, you need to amend the formula slightly.


I am guessing your formula looks something like this:





You see the "$" signs? This means that the cell references are fixed and so don't change when you copy the formula formatting to other cells.


What you need to do is change the formula to remove the "$" signs. It's a bit fiddly, but you may need to click in front of each "$" and then press backspace to remove it. That should get you this:





Now, this formula will change as you copy it around.