help with setting up stock ordering system using excel

[nikm]

Dear forum,

It's my first post so apologies in advance for any mistakes.

I was hoping to get some help setting up a stock ordering system in Excel. I have been sent a spreadsheet with about 400 items I've ordered before and about 15 columns of descriptions which include the name, category of item, manufacturer, cost, number etc.

I would like to set up a simple Excel spreadsheet which staff can use when they want to order some stock, which will automatically fill in the adjacent cells with the product category and unit cost, once the name has been typed into the first cell. Once I add the SUM formula I can therefore have an immediate total of how much each staff member is spending on stock that week, without having to look at the invoice I get back from the supplier.

I know Excel has a predictive text style feature (autocomplete?) but I don't know how to get it to automatically fill several cells in a row with data that is linked to the first cell.

Hope that makes sense!

Many thanks,

NikM

[Fotis1991]

Welcome to the forum. Can you upload a small sample workbook?

To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[nikm]

Hi, I've attached a sample of the final workbook I'd like to use, as well as a small sample of the data I've been sent.

Hope I've done it right.

Thanks!

NikM

[Fotis1991]

Apologize for the dalay. I missed your reply!

I am not able to understand your goal. You have filled only 1 row in your first workbook.

Which is the expected result in your other workbook? Do you mean something like this?

='[Items Purchased sample.xls]Sheet1'!I$2

To say the true, i believe no!

[nikm]

Dear Fotis,

Sorry for not being clear. The 'items purchased sample' is a sample of the stock details I've been sent by the supplier, to illustrate how many columns there are (there are about another 400 rows of stock items). The stock ordering one is the layout of how I would like to final sheet to look. The idea would be that the staff would start typing in column one - which in this sample would be 'tytin' - and Excel would then automatically populate the next four fields with the correct information (eg code 180553, cost £68.59 etc). They would then select the quantity and after they've finished (they may have filled in 10 different rows with different items), excel would automatically calcualte the cost of the total order. This part I could manage, by using the SUM formula on all the G column values.

Sorry for not explaining this very well! Should I upload more data from the 'items purchased' sheet?

Thanks for your patience!
NikM

[Fotis1991]

I give you an example in one workbook. Use the same way ..

=IF($A2="";"";IF(ISERROR(INDEX(Sheet1!J$2:J$100;MATCH("*"&$A2&"*";Sheet1!$I$2:$I$100;0)));"";INDEX(Sheet1!J$2:J$100;MATCH("*"&$A2&"*";Sheet1!$I$2:$I$100;0))))

[peterrc]

Hi nikm,

Have you thought of using VLOOKUP with the spreadsheet (that has 400 line items (rows) and 15 columns) as the source ?
Data Validation would give you a drop down list of all the items in the first cell and VLOOKUP would do the rest.
The only thing you have to input is the quantity.

Regards

peterrc

[nikm]

Dear Fotis and Peterrc,

Thanks for the tips. I will try them out and report back.

Kind regards,
NikM

[Fotis1991]

Waiting for your news..!

[nikm]

Dear Fotis,

Thanks for the formula!

I have now copied across all the order data into sheet1, and when I type the first few letters of an item into column A, it helpfully fills in columns B to E for me. At the risk of sounding cheeky, could you please help refine it a bit further for me? At the moment, when I'm typing in column A, Excel only remembers 'Tytin' or something I've already typed in. Is there any way to amend the formula so the result sheet will look at every entry in column A in sheet 1 and do the predictive text auto-complete?

Also, is there a way to do the same thing (i.e. autocomplete the fields) if one of the nurses fills in the stock code first? Some of the staff are very good at memorising the codes for ordering?

I've uploaded an updated spreadsheet based on the one you made for me with all the data.

Really grateful for all the help so far!

Peterrc - I had a look at the vlookup function but I couldn't understand how to make it work (sorry, my knowledge of Excel isn't that advanced).

Kind regards,

NikM

[Fotis1991]

I believe that search works fine. I have done some tests and i did not found any problem...

See the example..

..Also, is there a way to do the same thing (i.e. autocomplete the fields) if one of the nurses fills in the stock code first? Some of the staff are very good at memorising the codes for ordering?

No in this kind of solution. You have to choose 1 or the other way. Not both !

[nikm]

Hi Fotis, Thanks for bearing with me.

Is there a way for Excel to look at what's being typed in column A (short description) and then autocomplete the complete short description from sheet 1 column I (even if you haven't typed it in on the result sheet yet)?

At present, if you type the first word (eg tytin, ketac etc) it autocompletes the rest of the fields, but sometimes there are a few different items that start with the same word (eg hs needles, sempercare etc)?

Without the full text from 'short description' in sheet 1 it is a bit difficult to know if the right item is being selected.
Hope I'm making sense?

Thanks again,

NikM

[Fotis1991]

Nik. Another way is to create a list(see sheet 1-column q) and then use a validation list

in column A of result sheet to choose your option by this way. So you'll be sure of what you choose!

[nikm]

Dear Fotis, Thanks for this. I have modified the data validation 'list' to include all 383 stock items, and modified your formula to look at all 383 cells as well. It seems to work very well and I've attached a copy - would you mind checking I've done it okay?

Thanks, NikM

[nikm]

sorry, data validation didn't save! attached it as newer version of excel.

[Fotis1991]

you just need to expand the list range from Q2:Q18 to Q2:Q373!

Read here how to make Named Ranges and here how to use Validation Lists.

[nikm]

Thanks Fotis, you've been a great help.

The spreadsheet looks really good now. Will I be able to copy your formula into different workbooks in future? I would like to use this system for all of our ordering from now on (I did try changing the = to a ~ then copying and pasting into another workbook but it didn't work).

Thanks - i think this forum is great!

NikM

[Fotis1991]

You are welcome!

Copy -paste shold work but you need to be caredull with sheet names and ranges...


As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.