Multiplying cells from different sheets

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

Multiplying cells from different sheets

edited 12/09/19 in Formulas and Functions

Sheet 1: Work Scheduled (number of work units scheduled in a given month)

Sheet 2: Budgeted Cost of Work Scheduled (to determine the total cost of the work scheduled in a given month)

I would like to multiply a cell from Sheet 2 with the corresponding cell from Sheet 1 and then be able to copy/paste the formula so that it updates based on cell position in Sheet 2. I can easily do this in Excel, but have no idea how to do it in Smartsheet. Any ideas?

Example:

- Sheet 1: Build 10 miles of fence posts in month 1, build 15 miles in month 2

- Sheet 2: At $100/mile, the cost in month 1 is $1000, cost in month 2 is $1500, cumulative cost is $2500

- In Excel, the formula might be [Sheet 2]$B2*[Sheet 1]C2, [Sheet 2]$B2*[Sheet 1]D2, etc. (copy/paste would update the formula based on cell position in Sheet 2)

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Joe,

    You'd use cross-sheet formulas or cell-linking for this in Smartsheet.

    It sounds to me that cross-sheet formulas would be the best option.

    More info: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Which sheet would you have this data populated on? Are you able to provide some screenshots with sensitive/confidential data hidden or replaced with "dummy data"?

  • Here are a couple of dummy screenshots of an over-simplified example of what I would like to do. This is super easy in Excel, so I am sure there is a way to do the same thing in Smartsheet. 

    2019-08-30 (2).png

    2019-08-30.png

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

    I'd recommend using cell-linking from the PLANNED WORK sheet and add the information on new columns in the BUDGETED COST OF WORK SCHEDULED and then do the calculations from those.

    When you change something in the PLANNED WORK, the result will update in the BCWS sheet.

    More info: https://help.smartsheet.com/articles/861579-cell-linking

    Would that work?

    Hope that helps!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • How can I use cross-sheet formulas for a simple multiplication. I have on one sheet the assumptions on daily Tier rates for project managers - this must be constantly updated - and on the other sheet I have the workdays a project manager has on specific tasks. Next to this I have a column for the fees. In here I need to multiply the workdays by the daily tier rate. Is there a way to do this in a simple way??? I am new in smartsheet and I am not sure how it will work. Many thanks!!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Alexandra,

    @Alexandra Gallo

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Tommy RayTommy Ray
    edited 01/15/20

    Hello @Andrée Starå ,

    I think I'm looking for a similar solution. I simply want to multiple two numbers (cells) that exist on different sheets.

    Here is a basic example that you would see in Excel. I thought you could do the same in Smartsheet...

    =C2*Sheet2!B2

    Smartsheet gives me an #INPARSEABLE error message and won't allow me to reference another worksheet.

    Any insight or solution is greatly appreciated.

    Thank you!

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 01/16/20

    Hi @Tommy Ray ,

    Depending on your process and needs, I would recommend testing Cell-linking first (to collect the information on one sheet), and if that doesn’t work, you’ll need to use Cross-sheet formulas instead.

    Would that work?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • @Andrée Starå those are helpful articles and there are certainly some formulas that allow you to cross reference (i.e. SUM). However, for some reason you can use PRODUCT or "*" across various sheets.

    Any idea why or is this just a limitation of the tool?

    Thanks!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Tommy Ray You should be able to. Using a cross sheet reference it would look something like this...


    =[Column Name]@row * {Cross Sheet Reference}


    or


    =87 * {Cross Sheet Reference}

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Tommy Ray

    Happy to help!

    I saw that Paul answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Tommy RayTommy Ray
    edited 01/23/20

    @Andrée Starå and @Paul thanks for the input. I'm not quite there. I've attached a PDF for reference.

    In this basic example, I'm trying to multiply estimated hours by rates in a different sheet (Resource Rates). Is this possible?

    I can cross reference a value (rate from Resource Rates sheet) but essentially all that does is pull that into the Example Project Plan sheet and defeats the purpose of being able to reference another sheet with more data.

    Thanks,


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    So you are trying to reference a table on another sheet to pull an amount for multiplication?

  • Correct! But specific cells in that table.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. It is making more sense now. You are going to want to use an INDEX/MATCH to pull the value from the table. It is going to look something like this.

    =INDEX({Resource Rates Rate Column}, MATCH([email protected], {Resource Rates Resource Column}, 0))


    Then we multiply the result of that formula to your Hours.

    =INDEX({Resource Rates Rate Column}, MATCH([email protected], {Resource Rates Resource Column}, 0)) * [email protected]

  • @Paul thanks so much, this is very helpful.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 01/24/20

    Happy to help! 👍️


    Please don't forget to mark the most appropriate response(s) as "helpful" to let others with a similar problem know that a solution can be found here.

Sign In or Register to comment.