Feb 17, 2020
Sum imported values of csv to existing names/values on sheet
We export scores into a csv and add them by hand, per person, to an existing google sheet.
I want to automate this.
I want to add/total the csv into column N on the sheet.
They look like this:
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Feb 20, 2020
Shannon,
Sorry, you are correct we forgot that part. Nothing needs done on the master sheet. You just need to select cell A2 on the Import Sheet then got to the FORMAT menu>CONDITIONAL FORMATTING and create two rules. For the first rule just put this,
Apply to Range: A2:B
Cell is Empty
Format: White background
Next Rule is
Apply to Range: A2:B
Custom formula is: =COUNTIF(INDIRECT("Oasis!A3:A"),$A2)<1
Format: Red background.
These can be seen in the conditional formatting rules in your sample sheet too.
Stephen
Original Poster Needs LOTS of help marked this as an answer
Hello Shannon,
Thank you for working with me on your sheet, it was very helpful. We made you a sheet called Import Sheet to import your CSV files to. You will just continue to append the new CSV files to the bottom of that sheet. Then this formula will sum together the data based on name matches.
=ArrayFormula(IF(A3:A="",,SUMIF('Import Sheet'!A2:A,A3:A,'Import Sheet'!B2:B)))
Stephen
Original Poster Needs LOTS of help marked this as an answer
Hello Shannon Trysta,
You can try something like a SUMIF() but I might not be understanding fully hwat you need to do.
If that doesn't work or you still need more help with this I suggest that you share an editable COPY of your sheet. Use your sample sheet to demonstrate what you'd like the sheet to do! Here are some tips to help you get a great answer quickly... Product Experts TIPS FOR SHARING
Tip: Read the Tips post above, then check your sharing settings before posting - you want "Anyone with the link can Edit"
Stephen
Original Poster Needs LOTS of help marked this as an answer
All Replies (35)
Feb 17, 2020
Hello Shannon Trysta,
You can try something like a SUMIF() but I might not be understanding fully hwat you need to do.
If that doesn't work or you still need more help with this I suggest that you share an editable COPY of your sheet. Use your sample sheet to demonstrate what you'd like the sheet to do! Here are some tips to help you get a great answer quickly... Product Experts TIPS FOR SHARING
Tip: Read the Tips post above, then check your sharing settings before posting - you want "Anyone with the link can Edit"
Stephen
Original Poster Needs LOTS of help marked this as an answer
Feb 20, 2020
You have been amazing.
AND Your last fix made everything I wanted to do, work perfectly.
I had referenced a few links incorrectly at some point. So I cleaned them up and will post the one with all of our side notes in it, here.
I needed to be able to have:
*A way to add a sum each week and have it give a running total
*flag anything not included
*protect formula in header
*sort with no errors
You did all of this and were very patient with a very unskilled person. This has been an amazing amount of help.
I am grateful and so are all the people on that list!!
Last edited Feb 20, 2020
Shannon,
You're welcome, I'm glad I was able to help!
Thank you for the feedback and kind words.
Stephen
Feb 22, 2020
This time, I have a sheet on the same doc named Spent Dkp Input.
Anything entered on that sheet, needs to be added to the Spent Dkp (column O) column on the master sheet AND give new total in column P (of column N-O=P)from the Available DKP
(column P).
If your feeling up to it :) enjoy
Lol
Last edited Feb 22, 2020
Shannon,
Sure no problem, I'm glad to help. This is the same as your Earned DKP. It uses the same formula with the only change being the sheet name and the header name and I placed it in O1 for you.
=ArrayFormula({"Spent DKP";"";IF(A3:A="",,SUMIF('Spent DKP Input'!A2:A,A3:A,'Spent DKP Input'!B2:B))})
Stephen
Feb 25, 2020
I copied it to the right place and I get an error that says #Ref!
I can put a link in the copy you worked on, as we did before, if you like?
May not need to, maybe we do. If you hover over the error what details do it give you? Common one with REF is overwrite. You must make sure all the cells where the formula is trying to write to are empty.
Check that and we'll go from there.
Stephen
Feb 25, 2020
I'm on mobile for the rest of the night but I think you got this. Sounds like column O has some other data it formulas in it. Just need to clear those cells and the formula should work.
Stephen
Feb 25, 2020
I’m mobile this evening also.
I’ll give this a try on pc tomorrow it think.
Your right tho, once the cells were cleared I didn’t get an error anymore, per se.... lol
I’ll give it another shot tomorrow.
I may just get this.
Last edited Feb 25, 2020
Stephen
Feb 25, 2020
I went in via PC today and got everything to work perfectly.
I even added the conditional formatting, so it shows me any errors in spelling/names.
Thank you again, you’ve been amazing.
Awesome! I'm glad you were able to get it working!
Stephen