Data validation

Question

On sheet 1 A1:A5 I have a small list of letters and numbers formatted as General. On sheet 2 A1:A5 if a cell's value does not match an entry from sheet 1, I want it to pop up a warning message. I have successfully done this using data validation for the letters, but not the numbers. I do not have the option to change the formatting to text or numbers for other formatting reasons. Is it possible to have it see both the numbers and letters with a formula of some sort? Any help would be greatly appreciated. Thanks in advance~

Answer

I would set up sheet1 a1:a5 to be a named range, and then use data validation on sheet 2 to be a list which refers to this name - this will ensure that the ONLY possible choices are those on your list.

email me if you need a sample file - my email being aidan.heritage@virgin.net

Question

I am using dependent data validation on2003, to track laybys of bikes in our store. User can select a range of models of specific makes, then enter the price and initial deposit. I am now trying to set up a way of tracking ongoing payments. My problem is finding a way of associating the balance (price - initial deposit) with the specific bike, so that when a payment is made, the running balance is automatically updated. This will allow multiple payments to be made on different occasions without the need for the user to update the balance each time.

Answer

MATT

I don't know exactly what your data looks like so I can't be specific,but I believe you maybe able to do what you need to do with a vlookup formula. The problem you have is making each bike unique I sometimes do that by combining parts of the data with the combining formula I.E. +A1&B1&C1. The combine of these three cells should create a unique value which you can use as the value to lookup or it can be used in an if formula to post a payment to a column that represents that unique bike. As I said its a bit difficult to give yu anything specific without knowing what your data looks like, but maybe these suggestions will help you come up with something.

Question

I can't seem to wrap my head around getting validation to validate 2 cells. To explain further: I have a workbook with 4 weeks of information for 7 Areas in a workplace. Each individual worksheet needs to be able to check B$4:B(x) and find a duplicate value IF a Dupe value is found it then needs to check A$4:A(x) ON THAT SAME LINE to see if it's the same as A$4:A(x)that the duplicate was found in the B Column IE: I type 2 B16 when 2 is already in B4 I want to check to see if A16 is equal to A4.

I understand follow-up questions may need to be made because of my lack of ability to explain but I have attempted this and so far I have something like : =ISNA(IF(VLOOKUP(B4,B$1:B4,1,FALSE)=FALSE,IF(VLOOKUP(A4,A$1:A4,1,FALSE)=FALSE,FALSE,1),1))

But of course I feel like I am completely off track, as even when I look at this it doesn¡¯t seem like even if it was working it would not be doing what I wanted it to do, because it would just check the range and if anything is duplicated in column A it would error, but what I want is Column B can have duplicates and Column A can have duplicates but B16 and A16 cannot both be the same as B4 and B16. I should be able to do this but I just can't seem to get it right. Currently, if I have anything in the A column it errors I have tried many different variances with no success, Please assist!

Thanks, Matthew

Answer

Hi Matt

I was taking me forever to pull this off, that's why I send this question to the pool to see if anyone have a faster way

Now I did it, but you have to tell me more about your spreadsheet, I have only work within the range A4:B16, so they might be to revisit the validation to fit it to your spreadsheet, now the basic stuff should work no matter what

Now, I have data starting from row 4 till row 16, on columns A and B Go to cell B4 and then go to data-validation, choose custom and write this formula down =SUMPRODUCT(--($A$4:$A$16=$A4),--($B$4:$B$16=$B4))<=1

copy the validation over the rest of the cell, including the ones on column A (using copy, and paste special¡­ validation)

Let me know how it goes

Question

I created a data validation for my spreadsheet and was wondering if something else can be entered into the cell instead of what's listed in the data validation. As the source for the data validation I used the following: =Parts!$A$2:$A$32 (which is on the Parts worksheet). The part numbers are listed on the same sheet in cells B2 thru B32. This is the outcome I want: the data validation will list part names (A2 thru A32), but when you choose that part it enters the part number (B2 thru B32). I also tried using a list box, but could only get it to work on a single cell. Kelvin

Answer

Apologies for the delayed response.

When a validation has been created,will not automatically replace the corresponding values. A simple solution to the problem may be to use VLOOKUP function to get the part number.

Thanks and Ranjan