This is a sample sheet with the content I need help with..

**What?s there?**

In column A, you see a standard blog post outline.

The outline is structured as follows...

The outline's subheadings (H2) are shown with a bulleted point, while each of the talking points needed to write those subheadings are below, in a single line with a comma as a delimiter.

**What do I want to do?**

I want to separate them, so they are shown in an individual cell.

Basically, one subheading per cell and all the talking points in another.

Just as shown in the first row on the TEST Sheet I share above.

Been testing multiple formulas like SPLIT and TRANSPOSE and even a combination of them without luck.

Thoughts?

Thanks ina advance!

In column A, you see a standard blog post outline.

The outline is structured as follows...

The outline's subheadings (H2) are shown with a bulleted point, while each of the talking points needed to write those subheadings are below, in a single line with a comma as a delimiter.

I want to separate them, so they are shown in an individual cell.

Basically, one subheading per cell and all the talking points in another.

Just as shown in the first row on the TEST Sheet I share above.

Been testing multiple formulas like SPLIT and TRANSPOSE and even a combination of them without luck.

Thoughts?

Thanks ina advance!

Hi,

I have an IF formula that works for me but I have to drag it down to work on other colums. It's currently

=IFS(M5:M="AO","S",M5:M="","")

Id like it to be automatic on every column. I have attached an example relation to the prior mentioned formula. Basically I need "s" to appear in column P when I type "AO" in column M. I think it may be an array formula. I've tried searching for this on YouTube to no avail.

Be grateful if you could help.

Thanks,

Chris

1.png

]]>I have an IF formula that works for me but I have to drag it down to work on other colums. It's currently

=IFS(M5:M="AO","S",M5:M="","")

Id like it to be automatic on every column. I have attached an example relation to the prior mentioned formula. Basically I need "s" to appear in column P when I type "AO" in column M. I think it may be an array formula. I've tried searching for this on YouTube to no avail.

Be grateful if you could help.

Thanks,

Chris

1.png

I have this Excel problem that's really giving me a hard time.

[B]Link To The Google Sheet Problem Is Attached Below/B]

I have two tables, the first is the**transaction list table** where i will determine the **charges **and profit for a particular transaction and the second is a **transaction rate table** where i will store the formula that will be used to calculate the **Profit**

My intention is here is that i will like to input an**amount (in the transaction list table)** afterwards **select a menu in the "Bill Type" column** and that will use the corresponding formula stored in the "**Rate (Transaction Rate table)**" to determine the "**Charges (Transaction List)**" while the formula stored in the "**ProFormula (Transaction Rate)**" column will be used to calculate the "**Profit (in the Transaction List table)**"

Now the problem i managed in the past to get this done but when i drag the formula down for use in other rows, the formula is not being dynamically referenced for use in other rows.

[B]Link To The Google Sheet Problem Is Attached Below/B]

I have two tables, the first is the

My intention is here is that i will like to input an

Now the problem i managed in the past to get this done but when i drag the formula down for use in other rows, the formula is not being dynamically referenced for use in other rows.

Hi there,

I've created an excel worksheet with the intention of adding data to a table every day.

The issue I'm having is the table won't auto expand. I'm fairly certain I have the correct settings on.

Additionally, the border between the bottom two rows appears as two parallel lines. I haven't noticed tables in other excel users' worksheets appearing this way. I'm wondering what these lines might mean?

Any help would be much appreciated.

I've created an excel worksheet with the intention of adding data to a table every day.

The issue I'm having is the table won't auto expand. I'm fairly certain I have the correct settings on.

Additionally, the border between the bottom two rows appears as two parallel lines. I haven't noticed tables in other excel users' worksheets appearing this way. I'm wondering what these lines might mean?

Any help would be much appreciated.

Hello, I would like to use the AutoFilter option on Google Sheets. But since im handling a significant amount of rows, i would like to have some "Subheader" to easier identify the Data

Other than adding a New Row for "Week" is there any other way to do that?

Attached is image of the Data.

AutoFiler.jpg

]]>Other than adding a New Row for "Week" is there any other way to do that?

Attached is image of the Data.

AutoFiler.jpg

I'm trying to use the formula below and add IMPORTRANGE but the importrange formula showing an error, would you please help?

Original formula:

=IFERROR(VLOOKUP(INDEX($B$2:$B$6,SUMPRODUCT(($A$2:$A$6=$A9)*($C$2:$I$6=B$8)*(ROW($B$2:$B$6)))-ROW($B$2)+1),$K:$L,2,0),"")

Added Importrange:

=IFERROR(VLOOKUP(INDEX((IMPORTRANGE("LINK","TAB!$B$2:$B$6")),SUMPRODUCT(((IMPORTRANGE("LINK","TAB!$A$2:$A$6"))=$A9)*((IMPORTRANGE("LINK","TAB!$C$2:$I$6"))=B$8)*(ROW(IMPORTRANGE("LINK","TAB!$B$2:$B$6)))))-ROW((IMPORTRANGE("LINK","TAB!$B$2")))+1),$K:$L,2,0),"")

]]>Original formula:

=IFERROR(VLOOKUP(INDEX($B$2:$B$6,SUMPRODUCT(($A$2:$A$6=$A9)*($C$2:$I$6=B$8)*(ROW($B$2:$B$6)))-ROW($B$2)+1),$K:$L,2,0),"")

Added Importrange:

=IFERROR(VLOOKUP(INDEX((IMPORTRANGE("LINK","TAB!$B$2:$B$6")),SUMPRODUCT(((IMPORTRANGE("LINK","TAB!$A$2:$A$6"))=$A9)*((IMPORTRANGE("LINK","TAB!$C$2:$I$6"))=B$8)*(ROW(IMPORTRANGE("LINK","TAB!$B$2:$B$6)))))-ROW((IMPORTRANGE("LINK","TAB!$B$2")))+1),$K:$L,2,0),"")

Hi,

I have made a Google sheet at work that suggests a grade (A,B,C,D or F) depending on what dropdown values have been selected, this all is working fine.

However, I am interested in knowing how I use a master Formula sheet that other sheets can connect to.

The reason I wish to do this is that each employee (around 30) uses their own sheet that all looks the same but is tied to them. This means any change in code I make, I then have to replicate across 30 or so sheets which can get time consuming and laborious.

So I would like to add my formulas to a master sheet that all the other sheets are connected to, thus allowing for a single update of the code on the master sheet and these changes are then done on the other 30 sheets.

I have a simple formula here =if(D3="F", "Scrap","") that is on my master sheet but when I attempt to take that code onto the other sheets all I get is a string and do not recognize it as a formula.

I hope this makes sense, I did do a search on this before posting this but I could not find anything, apologies if I missed it.

Thanks

Neil

]]>I have made a Google sheet at work that suggests a grade (A,B,C,D or F) depending on what dropdown values have been selected, this all is working fine.

However, I am interested in knowing how I use a master Formula sheet that other sheets can connect to.

The reason I wish to do this is that each employee (around 30) uses their own sheet that all looks the same but is tied to them. This means any change in code I make, I then have to replicate across 30 or so sheets which can get time consuming and laborious.

So I would like to add my formulas to a master sheet that all the other sheets are connected to, thus allowing for a single update of the code on the master sheet and these changes are then done on the other 30 sheets.

I have a simple formula here =if(D3="F", "Scrap","") that is on my master sheet but when I attempt to take that code onto the other sheets all I get is a string and do not recognize it as a formula.

I hope this makes sense, I did do a search on this before posting this but I could not find anything, apologies if I missed it.

Thanks

Neil

Hello

I am working on KPI file and I want the primes to enter the commentary if the variance is less or more than the budget.

if variance is less than the budget then they have to comment on what went wrong and how they will improve

if variance is more than the budget, then they have to comment on what went good and how they will maintain.

I am working in google sheet. Can someone please help?

This commentary field is mandatory and we need them to fill in the details.sample workbook- google sheet.xlsx

]]>I am working on KPI file and I want the primes to enter the commentary if the variance is less or more than the budget.

if variance is less than the budget then they have to comment on what went wrong and how they will improve

if variance is more than the budget, then they have to comment on what went good and how they will maintain.

I am working in google sheet. Can someone please help?

This commentary field is mandatory and we need them to fill in the details.sample workbook- google sheet.xlsx

Hello,

I am having trouble getting a formula to work in Google Sheets. The formula I am using is:

=IF(NOT(ISBLANK(Kalender!L28)), Kalender!$L$1&" "&Kalender!L28 , "")

The formula is supposed to check if the cell L28 on the sheet "Kalender" is empty and if it's not, it should concatenate the content of cell L1 on the sheet "Kalender" with a space and the content of cell L28 on the sheet "Kalender", and if the cell L28 is empty, it should not insert any content. However, when I enter this formula in a cell, it gives me an error message "Error parsing formula".

I have checked that the sheet name "Kalender" and cell addresses L1 and L28 are spelled correctly and that they exist in my spreadsheet. I also have the necessary permissions to access these cells. I also tried an alternative formula:

=IF(LEN(Kalender!L28)>0, Kalender!$L$1&" "&Kalender!L28, "")

But it also gave me the same error.

I've noticed that the formula =Kalender!$L$1&" "&Kalender!L28 does work, but I need to check if the cell L28 is not empty before concatenating.

I am not sure what the problem is and would appreciate any help or suggestions.

Thank you.

]]>I am having trouble getting a formula to work in Google Sheets. The formula I am using is:

=IF(NOT(ISBLANK(Kalender!L28)), Kalender!$L$1&" "&Kalender!L28 , "")

The formula is supposed to check if the cell L28 on the sheet "Kalender" is empty and if it's not, it should concatenate the content of cell L1 on the sheet "Kalender" with a space and the content of cell L28 on the sheet "Kalender", and if the cell L28 is empty, it should not insert any content. However, when I enter this formula in a cell, it gives me an error message "Error parsing formula".

I have checked that the sheet name "Kalender" and cell addresses L1 and L28 are spelled correctly and that they exist in my spreadsheet. I also have the necessary permissions to access these cells. I also tried an alternative formula:

=IF(LEN(Kalender!L28)>0, Kalender!$L$1&" "&Kalender!L28, "")

But it also gave me the same error.

I've noticed that the formula =Kalender!$L$1&" "&Kalender!L28 does work, but I need to check if the cell L28 is not empty before concatenating.

I am not sure what the problem is and would appreciate any help or suggestions.

Thank you.

Hello!

I wanted to see if there was a way to Use VBA to create a .scpt file in the application scripts folder ("/Users/ComputerName/Library/Application Scripts/com.microsoft.Excel") on MacOS? I am able to read files from that folder using

However I am unable to create a new scpt file using

I run into an error saying "Run-time error '75': Path/File access error".

I know that this might stem from sandboxing on MacOS and that a user needs to grant permission to access that folder. Is there any way to create files in that folder without the user having to do anything? Any help is appreciated. Thank you!

]]>I wanted to see if there was a way to Use VBA to create a .scpt file in the application scripts folder ("/Users/ComputerName/Library/Application Scripts/com.microsoft.Excel") on MacOS? I am able to read files from that folder using

Code:

`myfile = "/Users/" & Environ("USER") & "/Library/Application Scripts/com.microsoft.Excel/test.scpt"`

str = ""

Open myfile For Input As #1

Do Until EOF(1)

Line Input #1, textline

str = str & textline

Loop

Close #1

Code:

`strFile = "/Users/" & Environ("USER") & "/Library/Application Scripts/com.microsoft.Excel/test.scpt"`

Open strFile For Output As #1

Print #1, "on ShellEx(shellCmd)" & vbNewLine & vbTab & "do shell script shellCmd end ShellEx"

Close #1

I know that this might stem from sandboxing on MacOS and that a user needs to grant permission to access that folder. Is there any way to create files in that folder without the user having to do anything? Any help is appreciated. Thank you!

Hello

I am a complete newbie to google spread sheets so bear with me.

As a fund raiser for a local under 12s football club there is a weekly lotto draw. 15 pairs of numbers are picked from 1 to 30 chosen by a person and once a week 2 random numbers are drawn between 1 and 30.Whoever matches up with the numbers drawn wins the draw . The numbers chosen also count each way so if 9 and 19 was randomly drawn this would win and also 19 and 9 would count.

Is there a way of using google spread sheet to match numbers randomly drawn against name of person who may have picked those numbers , instead of going through lots of envelopes checking the 2 numbers against numbers already picked.

Regards

Adrian

]]>I am a complete newbie to google spread sheets so bear with me.

As a fund raiser for a local under 12s football club there is a weekly lotto draw. 15 pairs of numbers are picked from 1 to 30 chosen by a person and once a week 2 random numbers are drawn between 1 and 30.Whoever matches up with the numbers drawn wins the draw . The numbers chosen also count each way so if 9 and 19 was randomly drawn this would win and also 19 and 9 would count.

Is there a way of using google spread sheet to match numbers randomly drawn against name of person who may have picked those numbers , instead of going through lots of envelopes checking the 2 numbers against numbers already picked.

Regards

Adrian

Sorry guys wrong thread

I have 600 rows of data all containing domain names. 68 of the domain names have expired so i need to remove them from the list. They are in column A. Is there a way of removing these domains using a formula or do i need to delete 1 by 1 ?

Example

Column A

Car

Bus

Truck

Van

Lorry

Bike

Motorcycle

Red

Blue

Remove

Van

Lorry

Bike

Motorcycle

]]>Example

Column A

Car

Bus

Truck

Van

Lorry

Bike

Motorcycle

Red

Blue

Remove

Van

Lorry

Bike

Motorcycle

Hi,

I am trying to split text by multiple delimiters ; and CHAR(10) in Google Sheets with the below formula.

=SPLIT(F2,{";",CHAR(10)},1,1)

It only is splitting for the first delimiter listed in the formula, so in the above formula it is only splitting by the ";". If I move the CHAR(10) to the be the first one, it only splits by that.

What I am doing wrong here?

Thanks

]]>I am trying to split text by multiple delimiters ; and CHAR(10) in Google Sheets with the below formula.

=SPLIT(F2,{";",CHAR(10)},1,1)

It only is splitting for the first delimiter listed in the formula, so in the above formula it is only splitting by the ";". If I move the CHAR(10) to the be the first one, it only splits by that.

What I am doing wrong here?

Thanks

So this is going to be a messy post but I'll try to make sense of it all...

It's the start of a new year, and like many others I have all the intention to get my a$$ in gear this year. With the help of Medium, Twitter, Google, and a lot of trying, I built myself a habit tracker that allows me to track my top 10 habits that I want to improve on.

So far, so good right? Well, this is where I get lazy . As manually tracking these habits every day is a habit that I know that won't stick, I have built a Form that allows me to track from my phone.

**Problem 1**

After transposing the Form input and lifting the date from the time stamp, I tested it and it worked by using this formula
(*where L1 is the date on the tracker, Form Responses 1! O1 is the extrapolated date from the form timestamp and O4 is the input to one of the questions, Ja=Yes*).

Again, so far, so good. The day after I broke that logic. I filled in the form for that day, then later on realised I made a mistake and filled it out again. Now, the logic of L1='Form Responses 1'O1 no longer works. I tried to correct it by including HLOOKUP (without knowing exactly what I was doing)
. Using this formula, it ticked the box. That would have been great if it wasn't for the fact that I used the formula on a date in the future, so it should have given a FALSE return.

I know when I'm beaten, so I hope that better minds can help me. What I need from the formula is the following:

**Problem 2**

Once the problem with the tracking is solved, I get to really see how I'm doing. And I don't know about you guys, but I'm a sucker for streaks (Duolingo anyone?!?). However, the streak count I currently have only looks at the streak for that month and forgets all about it once the month is over. Formula is

Therefore what I would like to build is a continuous streak. So the "current streak" of the previous month is the starting value of the new month. However, it shouldn't show on the next month until it is actually there. Also, I would like the "Longest streak" to remember a streak value even if it has not occured in this month.

I hope this all makes sense and that there are GSheet Gurus out there that can help me track my habits.

If you made it this far, I thank you already and I look forward to any feedback/tips.

It's the start of a new year, and like many others I have all the intention to get my a$$ in gear this year. With the help of Medium, Twitter, Google, and a lot of trying, I built myself a habit tracker that allows me to track my top 10 habits that I want to improve on.

So far, so good right? Well, this is where I get lazy . As manually tracking these habits every day is a habit that I know that won't stick, I have built a Form that allows me to track from my phone.

After transposing the Form input and lifting the date from the time stamp, I tested it and it worked by using this formula

Formula:

=IF(AND(L$1='Form Responses 1'!O$1,'Form Responses 1'!O$4="Ja"),True,False)

Again, so far, so good. The day after I broke that logic. I filled in the form for that day, then later on realised I made a mistake and filled it out again. Now, the logic of L1='Form Responses 1'O1 no longer works. I tried to correct it by including HLOOKUP (without knowing exactly what I was doing)

Formula:

=IFERROR(IF((HLOOKUP($M$1,'Form Responses 1'!O$1:$MM$13,3,TRUE)="Ja"),True,False),"")

I know when I'm beaten, so I hope that better minds can help me. What I need from the formula is the following:

- The date on the tracker needs to match the date on the 'Form Responses 1' tab. Where it does not, it should give a FALSE return
- If the dates match, it should look for the value on the correct row (Ja=TRUE, Nee=FALSE) and feed this back to the tracker
- Where there are multiple occurances of the same date, it should always look for the most recent one

Once the problem with the tracking is solved, I get to really see how I'm doing. And I don't know about you guys, but I'm a sucker for streaks (Duolingo anyone?!?). However, the streak count I currently have only looks at the streak for that month and forgets all about it once the month is over. Formula is

Formula:

=if(iserror(match(true,J5:AN5,0)),0,len(index(split(substitute(substitute(join(",",J5:AN5)&",","TRUE,","x"),"FALSE,",","),","),,counta(split(substitute(substitute(join(",",J5:AN5)&",","TRUE,","x"),"FALSE,",","),",")))))

Therefore what I would like to build is a continuous streak. So the "current streak" of the previous month is the starting value of the new month. However, it shouldn't show on the next month until it is actually there. Also, I would like the "Longest streak" to remember a streak value even if it has not occured in this month.

I hope this all makes sense and that there are GSheet Gurus out there that can help me track my habits.

If you made it this far, I thank you already and I look forward to any feedback/tips.