[SOLVED] Google Sheets Help

[BostoMark]

Hello, I need some help regarding google sheets, basically, I need to automate part of my sheet to help with the workflow.

First Problem

this is an automated sheet that is updated with jotform that updates whenever we submit a new order, this works perfectly. but I need the B column to find the Company name in the different tabs I have, so for example, when the "Completed Card Order Form Data (DO NOT TOUCH)" tab is updated, google sheets will go, "IF company name = 6th Gear Contracting, or any company name, THEN open tab "6th Gear Contracting or another company name, I am just using this for an example.

Second Problem

so, after the sheet finds the company tab, the sheets should now look at column C, and go "IF card number matches card number on the "6th Gear Contracting" page, delete row"

Third Problem

after the sheet deleted the row, it should now look at column D and go, "IF any new numbers present, Add row and populate with Unit # and Card #"

but I will have multiple tabs with different company names, it would be nice if I could somehow automate this. any help would be greatly appreciated.

 

[Ralston18]

As a starting point take a look at the LOOKUP Functions (Type and Name).

https://support.google.com/docs/table/25273?hl=en

VLOOKUP may prove useful as well.

https://www.timeatlas.com/google-sheets-vlookup/

You can easily google for more tutorials, "how-to's", and examples.

= = = =

That said, a database is more fitting as I understand your requirements.

Overtime, such spreadsheets become cumbersome and difficult to manage.

 

[USAFRet]

As above, this use case screams for an actual database, not a spreadsheet.

I do this literally all the time....Converting a 'spreadsheet' that has seriously outgrown the capabilities of Excel or Sheets.
Yours is starting at that point.

 

[ex_bubblehead]

I will third the sentiments above. You are most definitely not using the proper tool for the job. You are firmly into database territory.

 

[BostoMark]

yeah, figured as much. just trying to see if sheets would work cause of the "free" aspect of it

any ideas as to how to do this with a database? ive used microsoft access before, but am a bit rusty

 

[USAFRet]

yeah, figured as much. just trying to see if sheets would work cause of the "free" aspect of it

any ideas as to how to do this with a database? ive used microsoft access before, but am a bit rusty

I use Access daily.
Access has a bunch of built in templates, for all sorts of uses.

One of those is almost certainly close to what you can use.
Not 100%, but will get you most of the way there.
Orders, inventory, billing, etc, etc.

Sheets and Excel are great for what they are...calculators. Very very complex calculators.
Similarly, Word is great at what it does. A typewriter.
Access and its equivalents are great (well good) at what they do.
Don't cross the streams.

 

[BostoMark]

I use Access daily.
Access has a bunch of built in templates, for all sorts of uses.

One of those is almost certainly close to what you can use.
Not 100%, but will get you most of the way there.
Orders, inventory, billing, etc, etc.

Sheets and Excel are great for what they are...calculators. Very very complex calculators.
Similarly, Word is great at what it does. A typewriter.
Access and its equivalents are great (well good) at what they do.
Don't cross the streams.

which template are you referring to? that sounds like what we need

 

[USAFRet]

which template are you referring to? that sounds like what we need

What is the actual function?
Orders, inventory, task management...

The generic Northwind template, in Access 2019...

 

[BostoMark]

alright, here's another question, how would you link numbers and names together?

 

[ex_bubblehead]

Here's a couple of good places to start:

https://webs.wofford.edu/whisnantdm/courses/cs101/pdf/database/Relational_database_concepts.pdf

https://weblearn.ox.ac.uk/access/co...rningRes_Open/Article_RelationalDatabases.pdf

This isn't something you learn in an afternoon.

 

[USAFRet]

alright, here's another question, how would you link numbers and names together?

Depends...What numbers?

But, this is the basis of a Relational database.

 

[BostoMark]

thankyou for that paper, those instructions definitely clear things up, and i am linking these two items together:

https://imgur.com/QSnYzvg

View: https://imgur.com/QSnYzvg
so that way you would only have to type in the group number and the company name would already appear