INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel 2013 Uploaded to Google Sheets - Formulas Removed

Excel 2013 Uploaded to Google Sheets - Formulas Removed

Excel 2013 Uploaded to Google Sheets - Formulas Removed

(OP)
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).

RE: Excel 2013 Uploaded to Google Sheets - Formulas Removed

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2013 Uploaded to Google Sheets - Formulas Removed

(OP)
Thank you, Skip. Your solution works! Much appreciated!

RE: Excel 2013 Uploaded to Google Sheets - Formulas Removed

(OP)
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!

RE: Excel 2013 Uploaded to Google Sheets - Formulas Removed

Sorry, no insight on this error message.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2013 Uploaded to Google Sheets - Formulas Removed

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

RE: Excel 2013 Uploaded to Google Sheets - Formulas Removed

(OP)
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 ... thumbsup2

RE: Excel 2013 Uploaded to Google Sheets - Formulas Removed

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close