How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6116
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

We are a wholesale nusrsery and I am looking to set up an

Customer Question

We are a wholesale nusrsery and I am looking to set up an excel file that allows me to see how many plants I have in a certain location and what the spare capacity is in each location.
We have a grow program for our nursery and I can extract from it an Excel sheet that shows all the Greenhouse Locations (64 Ave …. MI4I) in the top row and all the Plant Varieties (Item Desc) in the First column. See screenshot below
Each Column shows how many plants there are of a certain type in that location. I am looking for a summary sheet where I have an Auto Complete Drop Down List for the ‘Item Desc’ which then will only show me which locations the particular items are.
In addition, I want to import the maximum number of plants we can have in each location so that I know my spare capacity for each location.
Can you help and If so can you give me an estimate of costs?
Submitted: 4 months ago.
Category: Microsoft Office
Expert:  Jess M. replied 4 months ago.

Hi Harry, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Can you please send me your sample Excel data? You mentioned of an attachment but I cannot find it. You can use the Add Files button to attach your sample Excel file.

Please let me know by replying to me here so that I can help you further.

Best regards,
Jess

Customer: replied 4 months ago.
Attached is the Raw Excel files that has the data of how many plants are at each location. Pants are in Column A. Locations are in Row 1. The maximum capacity for each location is not on this sheet. I will have to set up a separate excel sheet for that, but that is the easy part I can handle
Expert:  Jess M. replied 4 months ago.

Thank you for the sample file. Please give me a moment to check it and your requirements.

Customer: replied 4 months ago.
Lets first see what you come up with, if I still need more help we can do the remote assistance
Customer: replied 4 months ago.
Also, can remove message to my cell phone?
Expert:  Jess M. replied 4 months ago.

Are you saying that locations are in F1 to AJ1?

Customer: replied 4 months ago.
Correct. Locations are: 64AVE, CB00 ............... thru WO07. More will be added in the future
Expert:  Jess M. replied 4 months ago.

Ok, so you want a summary sheet where you will pick from a dropdown list a particular plant, and then that is the only plant to be displayed in a row?

For instance, you select "Abelia × grandiflora 'Conti' 1G " so it will just show the entire row 2 showing location E3 to have 62 pieces and location E4 with 142 pieces. Is that correct?

Customer: replied 4 months ago.
That is correct
Expert:  Jess M. replied 4 months ago.

Thank you. Please give me a moment to create this kind of summary sheet for you.

Customer: replied 4 months ago.
I do not want to having to scan all locations to find out where the plants are. I want to only see the locations that have that particular plant. I do need Auto complete on the Plant Drop Down List as well so that I do not have to search through the whole list of plants
Expert:  Jess M. replied 4 months ago.

Ok, please give me a moment.

Customer: replied 4 months ago.
Hi Jess. Just a clarification. I will have to import a new raw data excel sheet from the Grow program regularly and then have the summary sheet draw the data from the new import sheet. Therefore the Summary Sheet will have to interface with the separate raw data sheet
Expert:  Jess M. replied 4 months ago.

I do not understand with "interface". Can you just copy and paste the new data from the new imported file to your template that you sent me?

Customer: replied 4 months ago.
I could paste the date into the summary file as a another sheet, but I thought it would as easy to just have the new raw date file always named the same, for instance 'loc_import' so that your summary sheet will reference the 'loc_import' file
Expert:  Jess M. replied 4 months ago.

That is ok. But I need to know if the contents of the IMPORT file is the same in STRUCTURE every time you do an import? That is, if the contents in column A is ALWAYS the same, A2 = Abelia × grandiflora 'Conti' 1G and A683 = Erica x darleyensis 'Mary Helen' 72 CELL. ANd that the only difference are figures but the structure of the table is the same.

Please confirm

Customer: replied 4 months ago.
The structure is always the same, but I will add more locations and more plants in the future
Expert:  Jess M. replied 4 months ago.

My approach is through the use of formulas to build your summary sheet. So as long as the TABLE is there, updated every time you import, your summary works.

But for this to work in the future, you need to create a main template first to include all plants and locations. Otherwise, you need to update the formula in the future.

Customer: replied 4 months ago.
I can either update the main template or update formulas, as long as I do not have to get into macros and programming.
Expert:  Jess M. replied 4 months ago.

My approach will not include macros or programming so that you can easily maintain it.

Customer: replied 4 months ago.
Hi Jess. I have to step out for 30min
Expert:  Jess M. replied 4 months ago.

Ok. I am still working on the summary shit and this can take some time. I will just get back to you when I have my first output file ready.

Customer: replied 4 months ago.
Hi Jess. Do you have an ETA?
Expert:  Jess M. replied 4 months ago.

Hi Harry,

Thank you for patiently waiting. Here is the basic layout of the Summary sheet you wanted:

http://filesxpress.com/d-e6581c3b

You just need to select the plant and all information about the selected plant ON the MAIN or IMPORT template will be pulled out and displayed.

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess

Related Microsoft Office Questions