Formulas

Hi Guys, I am working on a formula/solution that gives me the result from a specific field. Maybe you can help me. So my first field is a single select field with the following possible selections: -“Lookup 1 selector” -“Lookup 2 selector” -“Lookup 3 selector” -etc. This is then followed by multiple lookup fields that look up data from different tables: Lookup 1, Lookup 2, Lookup 3, etc. I now want to create an additional final result field that contains the value of the Lookup field that has been selected with the selector. And to make it more complicated: I am trying to avoid a long IF() formula because I might be adding more columns and selectors at a later stage. The goal is that I do not have to touch the formula of the final result field ever again. If I do have to touch it again, it should be a simple one like a sum or multiplication without any nestling. Do you have any good ideas how to do it? Thank you in advance!

Another one for the masses. I have 2 columns that calculate days in arrears and rent arrears based on the days in arrears and taking into account a third column of a part payment. A fourth column is a vacate date. these are the formulas for each column: Days in arrears: (IF({Vacate Date}, DATETIME_DIFF({Vacate Date}, {Paid To Date In Property Me}, ‘days’), DATETIME_DIFF(TODAY(), {Paid To Date In Property Me}, ‘days’)))-(IF({Part Payment},0,0))-(IF({Part Payment},ROUNDUP({Part Payment}/({Weekly Rent Amount}/7),0))) Rent arrears: ({Weekly Rent Amount}/7)*IF({Vacate Date}, DATETIME_DIFF({Vacate Date}, {Paid To Date In Property Me}, ‘days’), DATETIME_DIFF(TODAY(), {Paid To Date In Property Me}, ‘days’)) -{Part Payment} These work fine until you put a vacate date in which stops the days in arrears and arrears amount changing. which is what I want… but… If a tenant pays money and the paid to date is changed, this is not reflected in the new arrears amount. so my 2 questions are: Is there a

I have a Date Field and a Checkbox Field I would like Records with Dates in the Past to be unchecked and Dates that are equal to today or in the future to be checked. Date Checkbox 3/16 True 3/15 True 3/14 True 3/13 False 3/12 False 3/11 False 3/10 False Thank you

New to airtable and formula’s so this should be easy for you but Im having brain fart moment! I an trying to get a column to populate the following monday date once a date field is entered into the Listed column. using this formula: IF(1-WEEKDAY({Listed})<=0,DATEADD({Listed},1-(WEEKDAY({Listed}))+7,‘days’)) I get the desired monday result but all the other cells contain #error! ( assuming its because the listed column cell for that row is blank) Tried using BLANK but I just cant get my head around it to get rid of the #error! Thanks all!

I’m looking at matching a number of sorority committee chairmanships with a number of women. Each will have a list of preferences. For example, those elected that will be overseeing the committee chair will rank their preference of the women who expressed interest in a chairmanship. The women will also have a list of preferences to which committee chair they would like.

Hi! I have a photo gear base with all my gear, I have one table with all the gear and i have one table with shoots where I pick out the gear for each shoot. I would like to add weight (as in kg) field to my gear table in the Shoot table i can have a summarized weight of all the gear for each shoot. Will this be possible? Thank you!

HI, I have a guest list for an event and want only the last /recent entered values to remain. Unique identifier is guest name guest code as F1,F2,F3,F4,F5. Suppose guest F2 again enters the form after 10 days with new arrival date and departure date - then he previous entry one should be hidden from my sheet and only this be considered in all rollups/lookups/linking.formula etc. I shall calculate rooms required, catering etc when guests enter arrival/departure/no.of persons etc. data using a form When there are two entries in the form for same guest, say F2 - I want the Last modified time to be picked up /maybe in a different view and the first one should be hidden and out of my calculations. Effectively- Duplicates in the same column (F2, F2) need to be removed and only the value (F2) with last modified time should remain. With this feature- I allow guests to change their plan/date/no.of persons requirement in future by entering their guest code.

We are looking to gather feedback on creative images. Usually we print and write feedback directly on the paper, but we are looking for a way to digitize this process. Is there a way to add comments directly to images on airtable and can we gather feedback and approvals from multiple people?

We’ve built a table to track and support our Corporate Events (webinars, seminars, etc.) I’m pulling in a data from a form, and using a Date Field titled “Event Date,” to calculate four dates on which we’d like to send out an e-mail sequence to get people to register for the event. Is there ever a way to change those calculated dates? At all? I know this flies in the face of why you’d do the calc in the first place, but the dates are often fluid and change. If I were to do the calcs in a separate table, and then link to the results in another table, would they be editable? Thanks for any help.

I have a budgeting table with earnings and outcomes, so for the monthly payments (like subscription etc.) I’d live to have that at a certain date a cell duplicate itself. I was thinking to use a checkbox so if it’s checked than it duplicate periodically, but I can’t figure out the right formula. Can you help me?

Hello, I’m rather new to Airtable and need help with a Formula. I have [worksheet 1] & [worksheet 2] Worksheet 2 has two columns, {A} & {B}. {A} has words or short phrases. {B} has a point value associated with the text in {A}. In worksheet 1, I have a column that those words will populate in. I’d like to use the column right next to it to show the point value of text that appears in that column. How can I make this happen. I’m thinking using a formula with IF. But I can’t figure out how to type it up. Thanks!

Hello everyone, I use Airtable to manage projects and elements (Balcony, Terrace, Stairs, Separator Screen) in projects. I look for a method to automatically create drawings numbers. Indeed in elements table I have a link to projects table and for each element I create a drawing. I want drawings numbers drawing like this: ProjectNumber-ElementCode-Number the numbers is reset for each different element in same project. Example: Project1-Balcony A-01 Project1-Balcony A-02 Project1-Terrace X-01 Project1-Terrace X-02 Project2-Balcony A-01 Project2-Balcony A-02 Project1-Terrace X-01 So, as you see, the numbers at the end are reset for each element in same project, and reset between project. Can someone help me? Thanks SDionne

Hi there I’d like to make a formula that whenever I mark the checkbox (I’ll unmark it later again), it will add +1. It’s a counter for how many times I already did a recurring task. Any ideas on how to do that? Many thanks in advance Cheers Marc

Hi There, I have a staff plan of 220 people whose absence I want to add up. In column A all names are below each other and the other columns consist of the weekdays, each divided into blocks of 2 working hours (so 4 per day for a working day of 8 hours). These cells are dropdowns including all kinds of projects, but also ‘absent’. The list is moved every week from ‘Week 0’ (current week) to ‘week -1’, and there is also a forecast ‘Week + 1’ (which then becomes ‘week 0’ every week) From the moment that someone is absent (that can be at the beginning of the week, but also somewhere in the middle) I would like to see the name and the number of hours of absence in another column. Because all weeks are archived in the same base, I would also like to add up the number of hours of absence, so that we can see a total of hours of absence after about 3 months. Of course I have already tried to figure out how I can build this, but what I come across is the fact that it can be the same names. a li

Hello there For recurring tasks, I’d like to calculate the data depending on what I select in the single select field. For example: if “2 weeks” choose the formula which adds 2 weeks to a date or if “1 month” then add 1 month and so on. Is that possible? Thank you. Cheers Marc

Column #1 {Need Hotel?} Yes or No Column #2 {Hotel Cost (Estimate)} {Estimated # Staff} * 250 * {Duration (Days) (Do Not Edit)} What I need is to change the column in #2 so that If {Need Hotel?} = Yes, then Do {Estimated # Staff} * 250 * {Duration (Days) (Do Not Edit)}

Ok so I got one that really threw me. I have a lookup field on Tab 1, referencing Tab 3, I need that info in Tab 2, but rolled up, so I created a rollup field in Tab 2 that rolls up the lookup field in Tab1 (Tabs 2 & 3 aren’t linked). The contents look like ABC, DEF in Tab 3, and the lookup field in Tab 1, but the rollup field in Tab 2 produces “ABC, DEF”. Where the heck did the quotes come from? Using ARRAYUNIQUE. Get this, I go back to the original record, ABC, DEF and change it to ABC-DEF and bam, no quotes any more!! I’m really confused, if anyone could make a suggestion would be much appreciated.

Hi! I’m new to Airtable and getting our bases set up. I’d like to have a field/column to notify us at quick glance if something has passed the “Anticipated Due Date” AND not been marked as “Completed” or “Not Applicable” for the Status. I’m struggling to find the correct formula for this. Is it also possible to also have this formula set to change color, say to red, when “Overdue” as you could do in excel? I’m terrible at properly formatting these formulas… sigh.

Greetings! I am trying to track event registrations. My table has these fields: Primary First Name Primary Last Name Event #1 Status (Single Select: Yes, No, Cancel, Didn’t Attend, Hasn’t Replied–applies to attendee coming alone or to attendee bringing a guest) Event #1 Meal for Primary First (Single Select: Meat, Veg) Guest First Name Guest Last Name Event #1 Meal for Primary Guest (Single Select: Meat, Veg) How best to see a total sum of all the meat meals and all the veg meals? Thank you–any assistance is greatly appreciated!

I’m trying to ROUNDUP() the value of a “Wall Width” / “Usable Roll Width” but I’m getting an error in the formula. See screenshot here: UGH why can’t I upload a screen shot!! t’s a very straight forward formula and I can’t figure out why it’s not rounding up to 4, in this case.

Hello, I am a student in art history and I find that Airtable is a good way to make my own database. But, I would like to know If I can use it for the whole year ? I feel afraid to lost all my datas… Thanks for your answer ! Alithéia

Hi everyone, I’m not sure if I’m missing something or what but when I clear out all the data in a column and then paste new data into the column, it still has the previous data saved? I know this because when I switch from single line text to multiple select, the old data shows up as a bunch of choices. Any help? The solution I found is to delete the column and make a new one but I have a ton of data and this seems like it may lead to issues half way through my project. Thanks.

I need help with an IF Formula. I have the following “Category” Field - Single Select “Est Rate” Field - number currency “Rate to Quote” Field - number currency (where I want the formula) Looking for a formula that does the following: If the the “Category” is NOT "Wedding, then “Rate to Quote” = “Est Rate” + 25%, If “Category” is “Wedding”, “Rate to Quote” = “Est Rate”

I’m struggling with the following problem. I’ve made a small system for a clock in a clock out, which tells how much money we’ll be invoices at the end of the month. However sometimes staff manage to clock in and out twice, which makes my end of month salary calculations in accurate. I’ve been trying to think up a way to check if the same staff has logged the same date twice. I’ve found a way that involves a sperate linked table where each row contain it’s own date and each staff has its own column with a bunch of look ups. However its very messy an inflexible and way more time-consuming than meticulously combing through all the clock ins at the end of the month. Sofar I’ve come up short, I have the feeling it’s not possible, but is there anyone who has any suggestion?

I’ve got a table of rates with “effectivity dates”. Such that when an order comes in, I need to find the “current” rate associated with that order, on the order’s date. My structure so far is as follows: Table Order - Line Items (Link to Order Line Items) - Order Date Table Order Line Item - Order (Link to Orders) - Order Date (Lookup to Orders.Date) - Line Item Total - Line Item Vendor (link to Vendor) - Effective Vendor Rate (?????HOW????) Table Vendor - Name - Rates (Link to Vendor Rates) Vendor Rate - Start Date - End Date - Rate (Percentage)