Excel 2013 Uploaded to Google Sheets - Formulas Removed 1

[CrystalFacets]

I needed to create a listing that had a column with Data Validation (DV). Once someone selects from the column with DV, that selection is no longer 'available' for others to choose. This is accomplish using other formulas from a solution provided by a website (URL listed in the attached Excel doc).

The DV worked as expected after building and testing before uploading. However, once the doc was uploaded to Google Sheets for others to access, I noticed that DV no longer worked. The named range cell reference (includes OFFSET formula) was 'removed.' When I tried to re-insert it, I received a not valid message.

Any ideas how to make this particular DV operational in Google Sheets?

Thanks in advance for assistance. Again, file attached (pre-upload version).

 

[SkipVought]

Hi,

I learned something today.

In Google Sheets use Data Validation > Criteria: List from range ... and set the range C1:C11

No OFFSET() or any other formula necessary.

Got mine to work as long as the formulas are in columns B & C.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[CrystalFacets]

Thank you, Skip. Your solution works! Much appreciated!

 

[CrystalFacets]

Skip,
One question. Any thoughts on how to remove the 'error' message that appears on DV - "Invalid: Input must fall within specified range"?

I thought there would be something in Google Sheets to remove message, but haven't found it yet.

Again, thank you for feedback!

 

[SkipVought]

Sorry, no insight on this error message.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[CrystalFacets]

No worries. Thank you, Skip, for being so responsive. And, Happy Holidays!

 

[CrystalFacets]

Hello again. In an 'ideal' world is there a way of doing the following 1 or 2 things?

First, could there be a 2nd data validation column to the right of the 1st one that allows the user to input info provided that it's not the same info as the 1st DV? For example, let's say the 1st DV column contains different 'types' of hardware -- light bulbs -- instead of names. We would want the user to input in the 2nd DV the BRAND of light bulbs they purchased. Possible?

Second, several users will have access to this Google Sheet in GMail. In an 'ideal' world, is it possible for each user to only have access to the DV cell(s) next to their name?

I think these are 'pie in the sky ideas' for another day ...

 

[SkipVought]

What I think you are referring to is a dependent list.

List 1 is Hardware and list 2 is dependent on the hardware item selected in the first DV in-cell drop down, so if Light Buld is selected in the first DV, then the dependent DV list would contain the list of Brands for Light Bulbs. Conceivably, there could be a third dependent list of Product Types for the selected Hardware item/Brand item: wattage, bulb type rtc.

I know several methods in Excel. I’ll need to play around in Sheets to see how dependent list can be generated.

Please start a new thread with a new topic.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]