You can do this with a formula and copy down:
="Hi "& B4 &", I'm reaching out to you today..."
Hi there! I have a workbook that I use to track all my reading related activities, one of them being a TBR (to be read) list which includes titles, authors and genres of books I want to read. It also has a column indicating whether or not I already own the book. All this data is pulled from a master sheet in the workbook.
In the same sheet, I have a “random book generator”. I select the genre I want to read then click the check box to randomly generate a book that is of said genre. (It will also tell me if I own the book or not). What I am trying to do is display text like “you do not have any books on your TBR that identify as the selected genre” if that criteria is met. Right now it just displays the default #N/A. I think I copied the random generator formula from the internet somewhere so I’m not too comfortable messing with it. I’ve tried changing the IF to IFS and adding M7=“#N/A”, “you do not have any books on your TBR that identify as the selected genre” At the end but that didn’t work. It was a half hearted attempt lol.
Any help would be appreciated!
Picture 1: the table of books Picture 2: the random book generator Picture 3: the formula in cell M7 which would either display the title of the book, or in this case #N/A since I don’t have any nonfiction books on my list. This is the cell that I want to display “you do not have any books on your TBR that identify as the selected genre” instead of #N/A.
formulas - How to add text conditional on numerical ranges in Google Sheets? - Web Applications Stack Exchange
Formula to display text based off a cell in another sheet - Google Docs Editors Community
google sheets - Is it possible to give a cell a different value than it displays? - Stack Overflow
In Sheets how to fill a cell based upon a value in another cell. - Google Docs Editors Community
You can do this with a formula and copy down:
="Hi "& B4 &", I'm reaching out to you today..."
Not sure if this is entirely what you want but this will do a replacement. It looks for a structure like this {{A2}} with a cell in A1Notation in the center. You must selected the cell with the text that you want to perform the replacement in.
function mergeReplace()
{
var re=/\{\{[A-Z]{1,2}\d{1,3}\}\}/g;
var ss=SpreadsheetApp.getActive();
var sht=ss.getSheetByName('Matches');
var rng=sht.getActiveRange();
var a1=rng.getA1Notation();
var s=rng.getValue();
var matches=s.match(re);
for(var i=0;i<matches.length;i++)
{
var r=matches[i].slice(2,-2);
s=s.replace(matches[i],sht.getRange(r).getValue());
}
rng.setValue(s);
}
Here's what my data looked like:
Before Replacement:
After Replacement:
Hello!
I am creating myself a grocery list that will display the name and location of an item on a list on another google sheet (same workbook) based on if the quantity of a cell <0 (if I need it or not) but I don't know how to display another cells data based on the first cell. If someone could help me I would gladly share a Blank copy for anyone to use once its completed because I know its going to be super useful, Thanks!
Hey all,
Looking for some help finding the right function! I am trying to make one cell make a list based on text from another column if a cell in the same row has a number below a certain value. Here's an example for context:
| List of Staff With Expired Certification: | [This is where I want the list - Ideally this would say "John, Jack" based on the table below] |
|---|---|
| Staff | Expiration Year |
| John | 22 |
| Jean | 24 |
| Jack | 19 |
In this example, it's pulling John and Jack as their certifications are expired. Otherwise, if there is a better way to organize this list, please let me know!
As a bonus, if there is a function to count the number of staff with expired certifications, that would be great too!
Thanks everyone!