Collect Data Based on Today's Date

Hi all.

I'm trying to do something that I'm not sure if it's possible or not.

We have a sheet that we are importing where the first column lists areas within our factory. Each column after that has a column name of a date. So 12/15/2025, 12/16/2025, 12/17/2025, etc.
In each of the date columns is a target production number.
Using a form on a different sheet, we are collecting actual production numbers.
On the actuals sheet I would like to pull in the target production numbers.

I need a formula that compares the factory area on the actuals sheet, to the factory area on the target import sheet, and the date specific column that matches the created date (or today's date) on the actuals sheet, to pull that target production number for just that area and date.

I'm concerned that it might not be possible to have a formula search through the columns to find a specific date. I'm not sure how to work around this. The imported file comes from a different department, and I'm not sure if we can get them to adjust it in any way.

Any suggestions would be greatly appreciated!

Find more posts tagged with

Formulas

Accepted answers

All comments

Ashley D. at Gen II

Unfortunately you are not able to make the column reference dynamic in a formula. You might be able to solve this with a pivot table and some helper columns. Do you know if you have the Pivot app from Smartsheet on your account?

Angelina

Yes, we have the Pivot app, but I haven't used it at all so far.

Ashley D. at Gen II

It's difficult to know if you will be able to achieve your goal without seeing the two sheets. @Paul Newcome have you tackled this scenario with Pivot app before?

marc4

Why not rotate the data?

Columns are areas in the factory and rows are the dates.

Each area has 3 columns. Plan, Actual and difference. You import the data. Each day you have an automation to ask for an update for the row with that day's date asking for an update on the Actual column and then use a column formula to compare the two.

/marc

Paul Newcome

I also tend to lean more towards restructuring the look-up sheet if possible. If not, are you able to provide some screenshots for context? How many date columns do you have? How many areas do you have?

Angelina

Thank you @marc4 and @Paul Newcome.

The file has 28 areas and daily dates from Dec 1st to the end of March. The biggest issue with making any changes to this file is that it is put together by a completely different team, and is used by several other teams. It is unlikely that we can get them to make any major changes to the file. We would have to make any changes ourselves. We were trying to find the most automated route to update our information, so we were hoping that a direct import of the sheet would work. This was an ask that was given to us yesterday haha, so I'm still trying to figure out the best way to handle all of this.

The full scope of this task is:

A form where individuals enter production numbers every 2 hours for one of 10 areas. (There are 28 on the import sheet, but we only need 10).
Import sheet has total production target numbers for each day, for each area. Import the sheet to pull the target numbers.
Need a sheet to then pull the actuals from the form collection sheet, and the targets from the import sheet. This sheet also needs an accumulation calculation over the course of the day, per area.
Combo graphs (will have to make them separate in SS), that show actual vs target numbers by every 2 hrs, for each of the 10 areas. Then Line graph that shows accumulation target, and accumulation actual.

That is what we're trying to accomplish. So since the form collection sheet is going to have actuals by each 2 hrs and by area, I need a way to search by the time and area to pull the actuals. And then I need a way to search the import sheet by area and date for target numbers.

Paul Newcome

Are you able to provide some screenshots (sample data is fine) for context?

marc4

Do you have access to someone that can use the API?

My first reaction is to create a daily collection sheet that has the 10 rows for the areas and columns for the two hour intervals. On the far right are two columns, one summing the hourly entries and one that shows the current day's date (=DATEONLY(TODAY())).

Then at the end of the day you export the data into a csv with that day's date and clear the entered data. You can then merge the data with the other sheet.

The next day the same sheet is ready for them to enter new data.

/marc

Angelina

@marc4 I am the owner and system admin on our account. We have Control Center and most of the premium apps, though we don't have DataTable.

@Paul Newcome Here are some screen shots of the excel sheets that we're trying to recreate in an automated way within Smartsheet.

Target import sheet (need only the top 10 areas, cells A2-A11):

Manual Update Excel Sheet for Actuals (yellow highlighted cells are the areas). This is what we want to use a form for. Information is updated every 2 hours. So currently our form has 4 questions: Area, Time, Actual, and Reason. Then we added a column to the sheet to pull in the target and calculate the gap, and added the Created Date column.

And lastly the graph that we're trying to recreate in SS. Obviously this has to be split out into two since SS can't do combo graphs atm.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.