Looking at 2 sheets for data that does not exist in 1 sheet and providing a blank cell

Options

I have a sheet that contains a lot of data that is an export from a separate data source (sheet 1). The separate data source also produces financials for certain periods, e.g. Q1 2023 and is exported to a separate sheet (sheet 2). Where there is no data for this period the report does not provide any data as it does not exist. As such I cannot produce a report with other data from "sheet 1" that would include the blank fields for the reporting period in question. Where data exists for Q1 2023 then this could be included in the report using sheet 1 for all additional data with exception of the financial data.

The only way I thought this could be used is by creating a blank column in sheet 1 and call this say "Financial data". I could then use a formula that would source sheet 2 "Financial data" using a unique reference number that appears in both sheets and where no data exists then make the cell blank for the cell matching the unique reference number.

Anybody help with the formula I could use that would look for the unique reference number in both sheets and the financial data data column in sheet 2.

Answers

  • Ipshita
    Ipshita ✭✭✭✭✭✭
    Options

    Hello @mike.thorpe17421

    The other solution that I think might work for this case is creating "summary sheet formulas" in Sheet 1 and 2, so that your report is an extract of the summary sheet formulas and not just the sheets. I am sure this will work, give it a try and let me know if it helps.

    Cheers,

    Ipshita

    Ipshita Mukherjee

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!