Database of information from one sheet to another

paloma
paloma
edited 12/09/19 in Smartsheet Basics

Hi all,

I have a sheet that is a "database" of about 400 records.  I want other users to be able to input one column of that sheet (Media Company Dropdown) in a form or other sheet and for the next seven columns to autopopulate.  Is this possible?  Would VLOOKUP be a solution?

I've read a few forum posts that said that Smartsheet isn't a database, which I understand, but I'm wondering if the functionality I need might be possible in some other way.

I've been testing out this in Airtable as well, which seems a better option for this purpose, but I'm really hoping to use Smartsheet since my client has a Smartsheet account already.

Really appreciate your thoughts and advice.

Thanks,

P

Screen Shot 2019-01-15 at 1.00.14 PM.png

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 01/15/19

    Hi Paloma,

    Yes, it's possible with cross-sheet formulas.

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, [email protected])

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I feel like and INDEX/MATCH might be good for a solution to this.

     

    =INDEX({Database Sheet Range 1}, MATCH([Media Company Name]@row, {Database Sheet Range 2}, 0), #)

     

    {Database Sheet Range 1} would be all of the columns in your master sheet holding the data you want to eventually pull

    {Database Sheet Range 2} would be the [Media Company Name] column in your master sheet

     

    # is where you put a number designating which column form {Database Sheet Range 1} to pull the data from. This would be based on which column the formula is residing in and which column in your master sheet the data is residing in.

     

    Another option with this would be to establish a helper row (top or bottom depends on where new rows are added). In this row you could put in each column the corresponding number you would use for # above.

     

    Then you could use (assuming the helper row is row 1) [Column Name]$1 in place of the # in the formula above. That will allow you to drag fill the formula across and locks in the row reference suing the $ to avoid the formula automatically updating the row reference as new rows are added.

     

    Another option would be to use the same formula above, but change {Database Sheet Range 1} to encompass only one specific column based on the data you want to pull for the column the formula is residing in on your new sheet. {Database Range 2} could still be the [Media Company Name] column as above.

     

    Then in the next column you would replace range 1 with range 3 which will be referencing the next column you want to pull data from on your master sheet.

     

    Basically you could either reference the entire range of columns and designate a column number in the last portion of the INDEX formula, or you could designate individual columns as ranges and not have to have a column number in the last portion of the INDEX formula.