Only looking up specific row to edit

ArthurC · 07/11/19

I have a list of 500 staff members with contact details. If one of my staff changes their phone number, is there a way to lookup the staff member's row and change the values of one of the columns (in this case Phone Number), but could be any other 30 columns.

Basically some sort of form: type name > details come up and change what needs to be changed. without having to search through 500 entries.

Vlookup of the name can return the values in the columns, but I can't update the values to reflect the change.

Andrée Starå · 07/12/19

Hi Arthur,

Here are a few options.

Filter to the name and then edit row or search row
Seach sheet/column and then edit or search row
Using a report to show fewer columns

Would any of those options work/help?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome · 07/12/19

You could also establish a Row ID column. Once you do that, you can use an INDEX/MATCH to pull the Row ID for the name you type in then use Ctrl + G to jump to that specific row number.

ArthurC · 07/12/19

1. I don't like the filter option as you have to create a filter to find the person, then delete the filter once you are done. The managers using the smartsheet would have a hard time using filters

2. search function may work, but are a lot of staff and we have fields such as email and LAN ID, which also use the name, so if i searched John, i would find John@smartsheet, AJohn = id, name, for each John there is

3. Reports may work, but i would have to create different reports with different filters, ie. report by location, report by manager. we have 20 locations and 40 managers. then the managers would have to find the specific report (i could put links on a dashboard)

i was hoping there would be an easier way i wasn't aware of

ArthurC · 07/12/19

to confirm: create new column "Row ID" which would be the row number. then do a vlookup somewhere, when i type the name, it returns the Row ID. then Ctrl + G, put in row number then find the entry?

is there a way to automate that process?

Paul Newcome · 07/12/19

That pretty much sums it up.

You could put an extra row at the top of the sheet where someone would enter the name, and in the cell next to it the row number would automatically populate. We could also build in additional conditions to narrow down the results such as entering a specific column to look in for the data entered.

From there, it would be manual entry to jump to the corresponding row.

.

I also have an idea for how to make a filter work where all they would have to do is type in a name, and the filter would automatically update, but that is something I need to test first.

Paul Newcome · 07/12/19

Upon further testing of the filter idea I had...

They would select from a dropdown menu which column they wanted to search in (lets just say they selected to search by Name for this example).

Next they would Enter the name they are searching for into another cell.

Then they would have to save the sheet and refresh it, and the results would automatically be filtered through.

Andrée Starå · 07/12/19

I saw that Paul answered already!

Let me know if I can help with anything else!

ArthurC · 07/15/19

Enter name of the person.would it also show the other columns like phone number address or just name?

when you filter the results, would you have to delete the filter to show all the results again?

Paul Newcome · 07/17/19

The way I have it set up is by using two helper columns. The first one will pull the column that is selected in the dropdown. So if you select "Name" in the dropdown, the first helper column will pull the data from the Name column on each row.

The second column is a checkbox column. What happens there is it searches the first helper column for whatever data is entered. If it finds that data, it will check the box.

The third piece of this is the filter. The filter is set to display rows that have the box checked.

Here are some screenshots.

A few notes:

1. I only have a Name, Phone Number, and Email address column on this example, but this will work for as many columns as you have.

.

2. The filter does not hide columns. It only hides rows that do not match. Therefore all columns will be shown for each row.

.

3. This is not cell linking. When a row is pulled that you want to edit, you are editing that row directly. No need to worry about using formulas to display data.

.

4. Because it is using a filter, it could show multiple rows if your search criteria is not specific enough. You can either edit your search criteria, or use your narrowed down list to find the row you are looking for.

.

5. In the screenshots, only the columns to the left of the gray bar would be visible. The columns to the right are the automation columns and would be hidden.

.

6. You will see a row (in this case row 10) that is basically just repeating the column names. This row is used in the automation columns, but will not be pulled by any filters.

.

7. The Name column is actually a dropdown type column. This allows you to enter whatever column names you want to give users the ability to search on.

...................................

Here are the steps by screenshot:

1. Filter is off showing "Master List".

.

2. User selects which column they want to search in (Name column used in this example).

.

3. [Search Data] column automatically pulls the data from the Name column on each row and the [Filter Match?] column checks the box for each row that contains what we are searching for. As of right now, we have not entered any criteria to search for, so all rows are going to "match".

.

4. We enter a name or part of a name to search on. In this example I will search for the last name of "Doe". Note that now (aside from the three rows at the top used as your "search bar", only the rows for Jane and Mike Doe are checked now.

.

5. Once the user saves the sheet and then refreshes either the sheet or the browser window, the filter will be activated and will display only the rows that match your search criteria.

Only looking up specific row to edit

Comments

Categories