Question description:
This user has given permission to use the problem statement for this
blog.
Need both of my formulas in column f and g to continue running down automatically when new rows are added, instead of having to manually drag them down.
Solved by E. U. in 22 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/06/2018 - 04:54
Hello, welcome to got it pro excel.
Excelchat Expert
14/06/2018 - 04:54
I understand you need help with formula running down the column.
Excelchat Expert
14/06/2018 - 04:57
Hello, are you there?
User
14/06/2018 - 04:57
Hello!
Excelchat Expert
14/06/2018 - 04:58
If you are, please respond and if possible send me the file.
Excelchat Expert
14/06/2018 - 04:58
Hey, great to have you with us.
User
14/06/2018 - 04:59
I went ahead and pasted the formulas and dummy sheet
User
14/06/2018 - 04:59
All I need is for column F and G to ALWAYS have those formulas in there.
User
14/06/2018 - 04:59
Without me having to drag it down
Excelchat Expert
14/06/2018 - 05:00
May I know what are you trying to do with the formula?
Excelchat Expert
14/06/2018 - 05:02
I'm asking cause I'm a little confused. COUNTIF is not an array formula. I can see you have COUNTIF as an array formula in F.
User
14/06/2018 - 05:02
For column F,
User
14/06/2018 - 05:03
It checks to see if the emails in column A sheet 1 are listed in column A sheet 2. I manually paste emails in sheet2 to make sure that they are all in sheet1. if it is supposed to be there then it states False. If it is not supposed to be there, it shows as true, and then is transferred to sheet 3, where it is counted as a no match.
Excelchat Expert
14/06/2018 - 05:05
It would have been much more helpful to work with the actual file. Nevertheless I will see what I can do.
User
14/06/2018 - 05:06
I apologize
User
14/06/2018 - 05:06
All I need is for the formula to continue down forever. for both columns
User
14/06/2018 - 05:07
that is what my sheet looks like right now
User
14/06/2018 - 05:07
there are no extra rows
Excelchat Expert
14/06/2018 - 05:07
I know and I understand the fact that you think this dummy data is sufficient enough to get the problem.
User
14/06/2018 - 05:07
every row is imported
Excelchat Expert
14/06/2018 - 05:08
I'm sorry, there are no more rows?
Excelchat Expert
14/06/2018 - 05:08
In your sheet?
User
14/06/2018 - 05:08
This is what it looks like. Every Row is imported through zapier automatically
User
14/06/2018 - 05:08
One by one
Excelchat Expert
14/06/2018 - 05:09
Okay, then I'm sorry to say if there is no row right now, you have to drag your formula for each new added row.
Excelchat Expert
14/06/2018 - 05:09
As you have mentioned you are importing data from zapier,
User
14/06/2018 - 05:09
Is there no way to keep that formula for that column???
Excelchat Expert
14/06/2018 - 05:09
I'm afraid no way.
Excelchat Expert
14/06/2018 - 05:10
And also integrating web services such as zapier with excel problems are out of our service scope.
User
14/06/2018 - 05:10
what about to change the g formula
User
14/06/2018 - 05:10
is it possible to have that formula register which row it is in, and change the cell number?
User
14/06/2018 - 05:11
accordingly
Excelchat Expert
14/06/2018 - 05:11
I'm sorry I didn't get the last part, what do you mean again?
User
14/06/2018 - 05:11
Formula right now for G2 is: =LEN(Sheet1!D2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!D2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
User
14/06/2018 - 05:12
What if i wanted to copy and paste that to my next row, how can you change that formula to match which row it is in?
User
14/06/2018 - 05:12
without me manually changing it
User
14/06/2018 - 05:12
the formula will already be in that row, the row # just has to change automatically based on what row the formula is in. Understand?
Excelchat Expert
14/06/2018 - 05:13
You can copy the formula and paste it to row 10 and excel will automatically change D2 to D10, it that is what you are asking for.
User
14/06/2018 - 05:14
Perfect
User
14/06/2018 - 05:14
Is that the same scenario for F as well?
Excelchat Expert
14/06/2018 - 05:14
Yeah, that too.
User
14/06/2018 - 05:14
Was it already like that?
User
14/06/2018 - 05:15
Appreciate the help by the way
Excelchat Expert
14/06/2018 - 05:15
Yeah, it was already like that.
User
14/06/2018 - 05:15
Oh okay
Excelchat Expert
14/06/2018 - 05:15
Thanks for understanding the limitations.
User
14/06/2018 - 05:15
Well thank you again
User
14/06/2018 - 05:15
I will leave a good review
User
14/06/2018 - 05:15
Thanks for clearing that up
Excelchat Expert
14/06/2018 - 05:15
Thank you for visiting us today, have a great day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
