Formula Confusion!

Kerry Speirs · 09/26/19

We are trialling smartsheet and overall enjoying it so far however we have run into a bit of brick wall and any help would be appreciated!

We have a main sheet with all our orders on it, using an older version to muck about with layout at set up at this stage hence the old haulage dates! Each order has details of the sizes etc and then on the right hand side (not seen in the screenshot) are customer details for admin processing. What we want to do is be able to have a row at the top of the sheet where we can type in an order number and the basic order details (quantity, size, thickness, category etc) will populate the top row of it's column so we can get a quick overview of the production of the order.

We have been trying vlookup and index functions but obviously doing something wrong as it's not working. This is just the start of something bigger but we are falling at the first hurdle so any help would be greatly appreciated.

Thanks in advance.

Andrée Starå · 09/26/19

Hi Kerry,

Welcome to the Community and the wonderful world of Smartsheet!

You could either use a VLOOKUP or a combination of INDEX/MATCH.

Hope that helps!

有一个很好的一天!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Kerry Speirs · 09/26/19

Hi there, thanks for your reply. Yeah I tried both (see my attempt at a index/match) in the above screenshot but couldn't get it to work. I'm sure it is just something silly we have overlooked but can't see what!

Andrée Starå · 09/26/19

Happy to help!

Try something like this.

Update the range and then drag on the row and change the column number near the end.

=VLOOKUP($Order$1; $Order$3:$Quantity$11; 2; false)

The same version but with the below changes for your and others convenience.

=VLOOKUP($Order$1, $Order$3:$Quantity$11, 2, false)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Kerry Speirs · 09/26/19

Hi again, thank for your help. It looked like it worked however only for the first 3 rows! For some reason if won't automatically generate the data for anything after that. Any ideas?

Kerry Speirs · 09/26/19

编辑!

So one of the reasons we wanted to be able to look up the information was so we could generate a kind of simple order sheet we could then pass on to the workshop to stop us double handling the information. I have since tried the index function on the separate sheet and have managed to get it to work for the first 5 rows (up to No. 1089) however after that (No. 1118 and above) come back with #NOMATCH (see screenshots). I have played about with it and if I change 1118 to 1018, it works but I have no idea why and that really doesn't help me. Anyone able to give some advise or answers on this??

Thanks in advance.

order management.jpg

1089 working.jpg

1118 not working.jpg

Andrée Starå · 09/26/19

好的。

Can you describe your process in more detail and maybe share the sheet(s) or some morescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

Hope that helps!

Kerry Speirs · 09/26/19

Ok, so we I have been trying out a slightly different approach but same idea and here is where I am currently....

We have a main sheet that contains all our orders and the specifics of each order. They have their own unique order number which is contained in the first column. From there, sizes, product categories etc each have their own column and after that is info for admin purposes (contact details, invoice no. etc). See first screenshot.

We wanted to use formulas so we could have a section in a sheet where we could input just the order number and then the basic order information would automatically generate. Part of the reason we wanted to do this was so we could quickly create a simple order sheet with just the required information to pass onto our workshop. That way we didn't have to double handle information by having to retype all order information again onto an order sheet, this also helps prevent errors. So I have now set up an order sheet with index and match functions to achieve this. Unfortunately I am experiencing the same problems as above. I can type in order numbers for the first 5 rows (up to No. 1089) and the information generates as expected however using numbers after that, it comes back as #NOMATCH. Please see further screenshots of order sheet.

Hope this helps but please let me know if you need anything else! Happy to share the sheet if you can tell me how

order management.jpg