I am having people submit a form. In this form, Column/Question 2 corresponds to a Multiple Select field where they can select their name from a dropdown. Column/Question 3 is where they input a time and then they hit submit
I want the value from Sheet 2 Person A Column 3 to add with the value on Sheet 1 Person A Column 3, value from Sheet 2 Person B Column 3 to add with the value on Sheet 2 Person B Column 3 etc.
I have spent the past 3 days trying to figure it out with rollup and I’m still super confused.
Any help is appreciated.
Thanks in advance!
Page 1 / 1
My first questions would be: What do the sheets represent? And what do the columns represent?
It sounds like you are treating Airtable like a spreadsheet rather than a database. If both Sheets 1 and 2 contain the same list of people then you can probably combine the data into one table.
My first questions would be: What do the sheets represent? And what do the columns represent?
It sounds like you are treating Airtable like a spreadsheet rather than a database. If both Sheets 1 and 2 contain the same list of people then you can probably combine the data into one table.
I answered before you edited your post.
Question 2 where they select their name should probably be a Link to Record field that links to the other table (rather than a Multiple Select).
Thanks for the reply!
Sheet 1 will have 2 columns;
Column 1 - Name
Column 2 - Hours
Everyone in Sheet 1 will have a predefined number of hours.
Sheet 2 will be linked to a form; once the user submits the form, the info will be logged into the Sheet 2 of the AirTable. Sheet 2 will have multiple columns, but to minimize confusion, we’ll just say Column 3 is hours and Column 4 will be some sort of variable.
What I’m looking for is if I write “ACCEPTED” in Column 4 for one of the rows, that rows’ Column 3 hours get added to Sheet 1 Column 2.
Thanks for the reply!
Sheet 1 will have 2 columns;
Column 1 - Name
Column 2 - Hours
Everyone in Sheet 1 will have a predefined number of hours.
Sheet 2 will be linked to a form; once the user submits the form, the info will be logged into the Sheet 2 of the AirTable. Sheet 2 will have multiple columns, but to minimize confusion, we’ll just say Column 3 is hours and Column 4 will be some sort of variable.
What I’m looking for is if I write “ACCEPTED” in Column 4 for one of the rows, that rows’ Column 3 hours get added to Sheet 1 Column 2.
I used an IF statement on Table 2 to populate the Table 1 Hours column. IF(Variable,Hours)
You can then link Table 2 records with Table 1 records using your form and use a lookup on Table 1 to bring over the hours that are Accepted.
It might be cleaner to do all of this on one Table, in the manner I described, either the form user or the the back end Airtable user will need to link the record from Table 1 to Table 2. The form would look like this:
I used an IF statement on Table 2 to populate the Table 1 Hours column. IF(Variable,Hours)
You can then link Table 2 records with Table 1 records using your form and use a lookup on Table 1 to bring over the hours that are Accepted.
It might be cleaner to do all of this on one Table, in the manner I described, either the form user or the the back end Airtable user will need to link the record from Table 1 to Table 2. The form would look like this:
Thanks so much for this. I edited your table and made it to my liking.
However, currently, if someone submits the form, the hours are added automatically into Sheet 1 and the end result seems to be “NaN”. How can I do it so that if the variable in Sheet 2 is “Accepted”, only then are the hours added in Sheet 1?
In addition, I made a nested formula in the column “Field 6” and the formula doesn’t seem to pick up duration in time. How do I fix this?
Thanks so much for this. I edited your table and made it to my liking.
However, currently, if someone submits the form, the hours are added automatically into Sheet 1 and the end result seems to be “NaN”. How can I do it so that if the variable in Sheet 2 is “Accepted”, only then are the hours added in Sheet 1?
In addition, I made a nested formula in the column “Field 6” and the formula doesn’t seem to pick up duration in time. How do I fix this?
Looks like you pulled a formula out of my Table 2, “Table 1 Hours”, which is “IF(Variable,Hours)”. That formula controls whether or not “Accepted” hour make it over to Table 1.
Take a look at this, I think I sorted your NaN error out:
Looks like you pulled a formula out of my Table 2, “Table 1 Hours”, which is “IF(Variable,Hours)”. That formula controls whether or not “Accepted” hour make it over to Table 1.
Take a look at this, I think I sorted your NaN error out:
I think I need to edit this so it works with the hh:mm format but I’m not sure how.
In additon, I’d like the original hours to add with the Accepted Hours. Not sure why it isn’t adding anymore.
Any idea anyone? Especially @Mac, you’ve been really helpful
