Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.

# Help With A Formula(i Think Its A Formula Anyway!)

### #1 lizi

lizi

• Members
• 33 posts
• OFFLINE
•
• Local time:05:44 PM

Posted 28 August 2007 - 05:17 AM

Hey, i am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum.

I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th.

basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.

I hope this makes sense. Please help, is it possible, if so how? or am i just looking for something that isnt there?

pelase help asap!

### #2 Geoffc

Geoffc

• Members
• 92 posts
• OFFLINE
•
• Gender:Male
• Location:Warragul, Australia
• Local time:04:44 AM

Posted 22 October 2007 - 03:00 AM

Let's assume you're using Microsoft EXCEL. I'd do that job with a combination of formulas and functions.
Suppose you put your team names in cells A1 to F1, top left of the spreadsheet, and their result positions on the row below, in cells A2 to F2.

Essentially you want to warn the user not to re-use a position. Excel is not good with messages, but a warning row (cells B3 to F3) could work. In cell B3 enter the formula [ =IF(ISERROR(MATCH(B2,\$A2:A2,0)),"",MATCH(B2,\$A2:A2,0)) ] without the square brackets, of course. Copy-paste that formula into cells C3 to F3. Then, as you fill in results, row 3 will list the column number of any previous use, wherever it finds a "MATCH".

If you wanted to make a bigger fuss about an error, put a conditional format on Row 3, say, a glaring background behind any MATCH number. Suppose in Cell B3 your conditional format was set for Cell value is Between 1 and 5, and the format is a warmish Pink, that should catch attention. Again, copy cell B3 into C3 to F3.

Oh, you did mention1 to 6 (or 1st to 6th). That would require a second conditional format. In B3, this would read: Formula is =(OR(B2<1,B2>6) , and the same gaudy format if you want to keep it simple. Again, copy the format of cell B3 into C3 to F3, but this time also into A3, because the operator CAN stuff up there too.

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users