-
Send Form for Completion to Individuals with Pre-Populated Fields taken from Data in the Sheet
I have a sheet called 'Farmer Access Survey' which will contain the current data we hold on record for 130 farmers. I have created a form, which I want to send to each farmer to confirm the current data we hold, and collect additional information from them. Can I send a link to the Form to each individual in an Outlook…
-
Can you skip a row and still use Column formulas?
Hi! I've got this sheet where all columns use the same formula except for the first row which is a kind of "header" and is a text row, if a use column formulas that text is erased, I was wondering if there's a way to use column formula and skip that row. The formula I'm using is =SUM(CHILDREN()) but every time a create a…
-
Collecting valued with IF Dependencies
I'm stumped in trying to average the values in a column ( CMO Dispo) that meets the criteria of CMO column - "Agilent" DOM column - 2022 year Current non-working formula: =AVG(COLLECT([CMO Dispo (Business Days)]:[CMO Dispo (Business Days)], MFG:MFG, ="Agilent", DOM:DOM, =YEAR(2022, 1, 1))) I'm pretty sure its not working…
-
How can I count blank or filled in cells?
Ultimate goal: To have status column update to Complete once all cells are filled in Problem: I'm trying to count 15 cells on a row (not a range), find out if they are all filled in, then once all the selected cells are filled in, the column I'm using for this formula will turn into a check box where I can create…
-
Formula for Current and Past Projects
Hello! We're developing a master project index sheet, dashboard, and reports. We want to showcase summary data on the dashboard for the total # of projects in the current year, upcoming year, and past (any date before the current year) based on the project's Start Date and if the Est. Completion Date (i.e., if it's started…
-
If cell matches cell in another sheet, return value from a column in that other sheet
Im working on a formula that will compare my Client ID column from my current sheet current row, to my Client ID column on my second sheet (called Production Log). When the client ID matches, I want the check date column from my second sheet to be returned. This is my formula. It is giving me an unparseable error.…
-
COUNTIFS formula for tasks due between 8 and 14 days in future
I'm trying to write a formula to count the number of tasks due between 8 and 14 days in the future. I started with a formula that is working for the number of tasks due in 7 days: =COUNTIFS({Schedule - % Complete}, <1, {Schedule - End Date}, <=TODAY(+7)) I could repeat the formula, replacing the +7 with +14, but I don't…
-
Nested Index/Match
Hello, I am currently trying to pull data into a primary sheet from several ancillary sheets. I thought about taking the data from the ancillary sheets, 8 of them, and putting them into one sheet but there are simply too may opportunities to mess the data up. I also hoped I could create a report that pulled in the…
-
How can I sum amounts only once from duplicate texts?
I'm trying to figure out a way to sum the amount column using only distinct clients and amounts that are tied to duplicates once. for the sake of the example let's say this is my data, the actual file has thousands of rows: in this instance, I want to create a formula that will sum 200 (for FIFA) + 100 (for Mcdonald’s) +…
-
Formula challenge
I want to remove the subjectivity in reporting on project domains. The aim is to use a formula to determine the 'summary rating' of a domain eg risk. This will be based on the content of the risk log (sheet). Community help is requested on the formula. The table below maps the thinking to develop the formula. I think it…