Excel question, forcing one of two numerical values

[Edward_The_Head]

I’ve tried to look under help, but that stupid paper clip can’t seem to get what I’m asking. Or maybe it’s me, but I hope I’m not dumber then a paper clip!

Anyway, in Excel I’d like to have a lot cells that either has a 0 or another number, such as 100. Is there a way to force Excel to be either 0 or 100, and nothing else? I’m sure there is, but no one can come up with a way to do it around here and I couldn’t figure out the proper formula to make it work.

[sailor]

I got this from excel help:

Designate valid cell entries
Select the cell to validate.
On the Data menu, click Validation, and then click the Settings tab.
Specify the type of validation you want:
Allow values from a list

In the Allow box, click List.
Click the Source box and then do one of the following:
To define the list locally, type the list values separated by commas.
To use a range of cells with a defined name, type the equal sign (=) followed by the name of the range.
To use cell references, select the cells on the worksheet and then press ENTER.
Note Cell references have to be to cells on the same worksheet. If you have to refer to a different worksheet or a different workbook, use a defined name and make sure the workbook is already open.

Make sure the In-cell dropdown check box is selected.

[Whack-a-Mole]

You want it to only be “0” or “100” based on some criteria?

You can use an IF/THEN argument.

For example:

=IF(D12<100,“0”,“100”)

That reads as follows:

IF Cell-D12 is less than 100 then put a “0” here else put “100” here (“here” being whatever cell you type that formula in).

Note the commas are the “then” and “else”.

Those statements will copy as any other formula does so if you drag the copy box down the cell designations will increment per usual Excel rules.

[Rhodes]

sailor’s suggestion is pretty cool. I’ve never seen that feature. It creates a drop-down list in each cell so you can select either 0 or 100. Enter anything else, and it gives an error message. Unless you enter a formula that computes to 0 or 100, in which case you’re fine.

[Edward_The_Head]

sailor, that’s pretty much what I’m looking for thanks. I’ll have to play around with it more, but it seems that it work for what I want. Thanks.

Since we’re on about Excel, is there a way to put a value that I’ve typed into sheet 1 onto sheet 2? I have my families assets on a spreadsheet and some items are the same, i.e. stocks. Would be great to be able to just change one instead of 3-4.

[Whack-a-Mole]

@Edward_The_Head

Since we’re on about Excel, is there a way to put a value that I’ve typed into sheet 1 onto sheet 2? I have my families assets on a spreadsheet and some items are the same, i.e. stocks. Would be great to be able to just change one instead of 3-4.

Absolutely.

It looks like this:

=Sheet1!A1

So, click on the cell in Sheet2 where you want the number copied from Sheet1 and type the above (with the relevant cell number).

Another way to do it is on Sheet2 type (where you want the number copied):

=

Then click on the sheet where the number you want is and click on that cell. Hit ENTER. Excel fills it all in for you.

Now, anytime you change that number it will be updated anywhere you have done that.

Note it is wise to “protect” your spreadsheets once you have them setup as you wish. You can make it so cells with formulas cannot be edited. This prevents you from accidentally typing a number in a cell where a formula is and then losing the formula. On big or complex spreadsheets that can have all sorts of bad knock-on effects as the error propagates through the whole thing. You can still change numbers in cells that are meant as data input and you can always unprotect the sheet to edit any formulas if you need to (then re-protect it).

[Boyo_Jim]

Just to check – can the vaules be 0 or 100 OR no value at all? Also, do you want to enter data directly into those cells, and and only allow those two entries? Or do you want a formula to look at come other information and display a 0 or 100 based on those criteria?

If the former, then sailor’s answer looks best. If the latter, then **Whack-a-Mole’s **should work.

Re the same value in two cells.
If the two worksheets are in the same workbook, this is extremely easy. Lets say you have the value in sheet1 in cell A1, and you want that value in sheet2 in cell b2. Go to the 2nd cell and type in “=”, then click on the other sheet and click on the A1 cell and press “enter”. Whatever value is in that cell will show up in the one you typed the “=” into.

[Edward_The_Head]

@Boyo_Jim

Just to check – can the vaules be 0 or 100 OR no value at all? Also, do you want to enter data directly into those cells, and and only allow those two entries? Or do you want a formula to look at come other information and display a 0 or 100 based on those criteria?

What I’d like to do really is have only one possible number. I have 35 different scores so either you have the score or your don’t. Since there are 10 players the odds of making a small mistake are there so I wanted to be only able to enter one number, though a 0 doesn’t make a difference when adding things up. I’d love to be able to just click on a box and have it input the number, but a drop down box works just as well.

[Boyo_Jim]

If you’re going to enter the value rather than calculate it, then a list is probably your best choice.

There are a couple of choices that would be more complicated to set up, but maybe a little quicker to use, if you want to play with the spreadsheet.

You could write one macro that tells Excely to enter a value of ‘0’ in a cell, and then moves down (or some other direction) to select the cell for the next entry. Write another macro that has it enter a value of ‘100’, and selects the next cell. Then attach each macro to a button. So you end up with a ‘0’ button and a ‘100’ button.

I suppose it depends on how much you have to do this kind of data entry as to whether it’s worthwhile to do the front end work.