0

So an assignment fell in my lap at work that is a little out of the ordinary for my skill set. My boss wants to create a google sheets data base to track our rental equipment. I created a handful of data validation Columns to track Unit#, Customer Location, Maintence required, etc. Example

Unit      Location   Maint
321266     128       Oil Change
322267     600       None
323264     950       Bad Breaks

I've locked the Unit and Location to only allow the correct info to be input and allowed Maint and other fields to be input manually because they vary to much to be validated.

Now I need to spruce it up and turn it into a working spreadsheet, not just rows of info. Is there a way I create a new sheet so when I imput data into a cell that is data validation locked to only accept correct Unit numbers, and then auto populate the location/maint/etc columns?

Say I type in 323264 in a cell on a second sheet, i want it to fill in 950 and bad breaks into the next two cells.

Is this possible or am I barking up an empty tree? I've searched through the stacks for awhile and found lots of ways to pull data from one sheet to another, but not pulling data tied to one cell when that cell moves throughout the spreadsheet.

Thanks

Improve this question
3
  • Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask.
    – Rubén - Volunteer Moderator - ♦
    Commented Sep 24, 2019 at 15:27
  • What do you mean by "when a cell moves throughout the spreadsheet"? How it will move? Will you cut and paste that cell to move it? Have you already tried to use cut and paste?
    – Rubén - Volunteer Moderator - ♦
    Commented Sep 24, 2019 at 16:33
  • I will break down a spreadsheet by Customer, and our warehouses, As the equipment moves from a warehouse to a customer, I want to be able to just type the Unit# in the correct area and have it auto populate the required fields. We currently manually type the information in and there is no permanency to the data, as when it leaves one location, its erased and when it arrived at a new location, it is typed in again. Data tied to the unit # has to be re-entered and can potentially be lost.
    – charlie4
    Commented Sep 24, 2019 at 16:39

1 Answer 1

Reset to default
0

This can be acomplished wiht the vlookup formula.

Guessing that your are using columns A, B, C of the Sheet1 and on sheet2, use next:

Sheet2 - A1 Put the value to search

Sheet2 - B1 =ARRAYFORMULA(IFERROR(IF(A1:A<>"",VLOOKUP(A1:A,'Hoja 1'!A:C,2,0),""),"Not Found"))

Sheet2 - C1 =ARRAYFORMULA(IFERROR(IF(A1:A<>"",VLOOKUP(A1:A,'Hoja 1'!A:C,3,0),""),"Not Found"))

With the above if the value is not found will trow a Not Found message.

On this link you have a real example with the provided data and the formulas i've used before and also a similar way.

Improve this answer
1
  • 1
    This was pretty much exactly what I needed. The array formula is pretty handy. I appreciate the real world example, that helped me understand the formula better. Thanks
    – charlie4
    Commented Sep 25, 2019 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.