Linking cells which are completed using Validation lists

[sfoxjones]

I have a sheet where all the cells are filled in with numbers or text which are taken from a list created in data validation. I have then linked the cells to another sheet. When the cells in sheet 1 are filled in using the drop down lists the cells in sheet 2 are then required to update. This works fine if its a number in the list but if it is text I just get the #VALUE# entry in sheet 2

 

[tusharm]

How do you update the cells in sheet 2?

 

[sfoxjones]

Sheet 2 linking to sheet 1

I select a cell in sheet 2 and do paste special and click on paste link ortype in the link in the format example =Sheet1!$A$3

 

[tusharm]

I am not sure how a ={cell} formula can generate a #VALUE! error. What exactly happens before you get the error value in the cell?

 

[sfoxjones]

#Value Error

I have a sheet called medex, I use a list for validation and then on the second sheet I enter the following so as to get a filtered result.

=IF(MEDEX!$B$29,MEDEX!$B$29,"NONE")

If the list in The medex sheet is a number all is well, when it is text it gives the result #value!

 

[tusharm]

Re: #Value Error

Either intentionally, or otherwise, your formula assumes the MEDEX cell contains a boolean or a number that is coerced into a boolean. It's also what I call lazy programming, something that too many, including many "experts," are guilty of.

What do you really want to test for? That the MEDEX cell has something selected from the data validation dropdown? If so, use the unverified
=IF(ISBLANK(MEDEX!$B$29),"None",MEDEX!$B$29)

I have a sheet called medex, I use a list for validation and then on the second sheet I enter the following so as to get a filtered result.

=IF(MEDEX!$B$29,MEDEX!$B$29,"NONE")

If the list in The medex sheet is a number all is well, when it is text it gives the result #value!

 

[sfoxjones]

data Vlidation solved

Thanks very much for that. It works now. It was very kind of you to help out


ta

 

[tusharm]

Re: data Vlidation solved

You are welcome.

Thanks very much for that. It works now. It was very kind of you to help out


ta

 

[sfoxjones]

Data validation and filtering

Dear All,

I learnt from the previous replies about linking one cells result in one sheet to another sheet. But now I have been asked again by the boss to do another linking job.

From My previous post I took the results from a number of sheets of validation list entries and linked them to a single sheet to give all the totals from many sheets into one.

So I had 5 sheets with 20 q and a's on. The answwers were from a data validation list .

This has given me 100 questions and answers on the final sheet. But thanks to the filtering earlier only 20 of the 100 questions have actual answers next to them the others merely have none.

What I need is a formula in the final sheet that links the question only when there is an answer other than none!

That way instead of having 100 questions with maybe 20 "real" answers and 80 "NONE" I would have a sheet with just the 20 rows of questions and valid answers

so the following gave me from column B in sheet sheet MEDEX to column B in sheet TOTAL

IF(ISBLANK(MEDEX!$B$29),"None",MEDEX!$B$29) None
IF(ISBLANK(MEDEX!$B$30),"None",MEDEX!$B$30) 1234
IF(ISBLANK(MEDEX!$B$31),"None",MEDEX!$B$31) None

When what would be handy. If Medex column A question was also carried accross when it's answer in other than "none"

So Medex column A29 would be "How many Apples" with No entry at allbeing carried over to sheet Total.

Whereas Medex column A30 would cause the question to be filled out in Toatal A column aling with the answer in sheet Total B column


I hope this makes sense


best regards