About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Find and Update Excel Row, or Create if not found; with formula reference

  • 19 May 2021
  • 8 replies
  • 969 views
J
Userlevel 2
Badge +1

Hi,

 

I have a zap which finds an entry in an online Excel file.

 

I’d like it to be able to do the following, but can’t figure out how, id anyone can advise?

 

When the entry isn’t found, it creates a new row at the end. I’d like that row to include formulas with cell reference related to that row. Eg: If it’s inserts row 100, there will be cells with the formula A100-B100 (for example in). 

 

When an entry is found it adds an extra number into the first available blank cell past a certain column, Eg: If it was columns M onwards and there was already data in M1 and N1 it would add the data to O1.

 

Any help would be greatly appreciated :)

 

Thanks,

 

Jon

icon

Best answer by Jon Hall 1 June 2021, 07:56

View original
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

8 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @Jon Hall 

Check out this article for inspiration:

 

You may have to use a couple steps in your Zap to handle the logic you’ve described.

  1. Find Row
  2. Evaluate returned data
  3. Update Row
⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
J
Userlevel 2
Badge +1

Hi,

 

Thanks for your help :)

 

I’ve got it to add the formulas fine.

 

And I’ve got it to calculate which is the cell the new data needs adding to (O1 in the example).

 

I can’t quite figure out how to add data to cell O1 specifically though.

 

I can get it to select row O (in this case) and update the columns I select in that ‘Update row’ step, but can only seem to pick from the rows it gives as opposed to referencing O1.

 

Apologies if I’ve not made that clear enough and, again, any help is greatly appreciated :)

 

Thanks,

 

Jon

Troy Tessalone
Userlevel 7
Badge +14

Hi @Jon Hall 

Please provide screenshots to help clarify your current Zap step configurations.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
J
Userlevel 2
Badge +1

I’ve done a loom video which, hopefully, shows what I’m trying to do more clearly → https://www.loom.com/share/5b0677741a3d49139faa12b55d8656cf

nicksimard
Userlevel 7
Badge +11

Hi @Jon Hall!

I’m sure there’s a more glamorous coding solution but here’s something that seems like it should work, and is pretty clever if I do say so myself 😛

We have something called Named Variables (read this recent article we wrote about it for more info) and if you combine that with a Lookup Table you can produce multiple mappable fields from one lookup:


So I envision that you would use the number as you’re doing now but instead of outputting the letter, you would output all of the fields with only one of them containing a value:


It’s a bit tedious but it should get you what you need. In order to produce all of the mappable fields to set up Excel you’d want to do something like this (make sure to add the space so that the field outputs nothing as opposed to not being a field at all)…

… map the fields…

… then go back to the Lookup Table and create it with all of the key/value pairs you need:


Then like magic you’ll get the value in the spot that you need it :)

(I’m not able to embed the animated gif for some reason, but you can check it out here: https://cdn.zappy.app/7afeb0ef1b8e2764a99245afe7d9f3d9.gif)

J
Userlevel 2
Badge +1

Hi,

 

Thanks for this - greatly appreciated :)

 

I’ve got it now so I have an output of (for example) S123

 

How would I have it do another step where it inserts a value into that specific cell?

 

I can only see how to update the row and then have one of the columns in that row selected, which would be the same for everything coming through that step of the zap.

 

Apologies if I’ve missed something.

 

Thanks again,

 

Jon

J
Userlevel 2
Badge +1

Hi,

 

I still haven’t figured out how (if it’s possible) to insert a value into that specific cell if anyone has any ideas?

 

Thanks,

 

Jon

J
Userlevel 2
Badge +1

Hi,

 

I rebuilt the excel sheet so everything was on independent rows which are then added to to get this to work in the end.

 

Thanks for all the help and suggestions :)

 

Jon

Terms & Conditions