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

  • Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask. – Rubén Sep 24 '19 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 Sep 24 '19 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 Sep 24 '19 at 16:39
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.

  • 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 Sep 25 '19 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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