Linking cells which are completed using Validation lists

sfoxjones

New Member
Joined
Jul 22, 2007
Messages
7
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sfoxjones

New Member
Joined
Jul 22, 2007
Messages
7
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

New Member
Joined
Jul 22, 2007
Messages
7
#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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

New Member
Joined
Jul 22, 2007
Messages
7
data Vlidation solved

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


ta
 

sfoxjones

New Member
Joined
Jul 22, 2007
Messages
7
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
 

Forum statistics

Threads
1,181,053
Messages
5,927,848
Members
436,572
Latest member
khalid hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top