Dynamic Database

[Alechko777]

Good Day People!

Can somebody help me with this issue? Dynamic Database
I need help in P/N quick finding.I've heard that Excel Forum is the best Excel site in the whole web!!I really wonder if somebody can advice how to do this: I have an enormous database with numerous number of P/N's, S/N's and etc'
I need to find quickly the P/N I work with by scanning into the Excel cell the barcode by a laser pistol and when I scan the barcode,in the cell we can see the P/N.Now I need excel will automatically fill the blanks cells: Producer Name,Model Name etc' and links me to specific appliance,spair part or document as well. Its really complicated for me, I'm new in Excel, but I love how it works !

There are many Excel Ninjas here....Try yourselves in this complicated mission.I attached the file with database to somebody who can help...

Thanx,People

[Tank997]

This is very simple if you setup your data correctly. Are you scanning a "P/N" or a UPC code? assuming it's the UPC code and it shows up in cell B3 on your 'Quality Inspection" sheet you can fill in the other cells with lookup formulas.
I would suggest you create one sheet with all the data from the other sheets in one place. The UPC # or P/N should be in the left most column so you can use vlookup to pull in the other data. You could do it with the data the way it is but it will be much more complicate and you may not understand the formulas.

[Alechko777]

Hi Dear Tank999!

Thanx for helping me! I've tried couple ways to complete it, but unfortunatelly couldn't because of unknown reasons.
I'm so fresh in Excel that I cannot find the right way to build the perfect Dtabase I need for my everyday use.
There are always mistakes in the code or in VLOOKUP function that I can't find and fix...I so despaired,believe me!

My hands are down, I see no help from forums... I couldn't believe that the mission so complicated!

If you can, if you have a time for this, please insert the formula there, lets see how it works.... I'll be beholden to you!

Thanx a lot, friend!

[Alechko777]

...P.S. I'm sorry Tank997 instead 999...

Yep... I scan the Barcode, "Zebra" I mean... and in the Excel cell the P/N is shown.

[Tank997]

Okay, I can help you out but you will need to do some of the file clean up first. Here's what I need you to do.

1. Create a new sheet in the workbook.
2. Create a header row with all the column headers you need from the other sheets, you may want to start with the ACC Manufacturer sheet since it has the most columns. One of the problems with you current sheets is the header names are not consistent from sheet to sheet.
3. Put the name for the number you will scan in the left most column header. if it's P/N use "P/N" and not "Part Number" make sure it's the same as what you have on the Quality Inspection sheet.
4. Copy all the data from the current vendor sheets into the correct columns on the new sheet. Some of this could be automated if your column headers were consistent.
5. Once this is done you will have one table for all vendors that will be very easy to look up in and add, remove and filter items in the future.
6. When you're done with the above upload the sheet again and I will work on it.

Tom

[Alechko777]

Thanx Tom!

I'll fix the file and replace the old one within couple days....
God bless you!

[Alechko777]

Hi Tom!
I've finished the requirements to the file, what next?QC-Inspection.xlsx

[Tank997]

Okay, I'm looking at the sheet now and I would like to suggest a few more changes if they will work for you.
1. Make sure the names in the lookup section in column A match the names in the column headers so Model Name needs to match one of the headers. Or tell me which one to use.
2. Add a column for the vendor and remove them from the header Merged Cells are not good in tables.
3. If possible move the P/N column to the first column on the left side of the table

I don't see SOP DOCUMENT as a value that can be looked up for each P/N
Same with S/O
If you add a column for the Manufacturer that's an easy lookup.

[Tank997]

Hi I added a copy of your sheet and made some of the changes for the first two vendors to show you what I mean.

Notice that I also removed the Merged cell and replaced it with "center across selection" which is in the format cells - alignment dialog box under horizontal.

Take a look and see if this is a good start...

[Tank997]

I made a few more changes and added some options for you to look at. This is closer to what I had in mind, take a look and give me your thoughts.

Tom

[Alechko777]

Hi Tom! How r u? Thanx a lot again for your help! I'm reviewing the file you've worked on and smiling... You're really Excel Ninja! I would like to be at least 10 persent of you! I'm almost done.
1.The SOP documents I place in the same folder as the QC-Inspection file. There are many of them... Each of them has its unique name and number. What I realy need-that in Option 1 opposite the SOP Document, after I SCAN the barcode, excel will generate the link to the right document from this folder.
2.In QC-Inspection file you wrote Not sure what to do with these???, above the ACC. tables. So....actually I need the same what you already did early.I want only the RED MARKED P/N. This is the Accesories KIT P/N. All of P/N in blue are only the components of the main KIT.

God Bless you Tom!!!

BR, Alechko

[Tank997]

Alaechko, mark up the last file I sent with your notes for changes you need or would like to see and I will work on it. Is it possible to incorporate the data in those bottom tables into the main table? If so you should work on getting the format to match what I have and maybe add some note into one or more of the empty columns to distinguish those items from the other data. Or you could move them to another sheet with there own format and set up a new table for those items.

I wish I could say it only took me a few minutes to create the sheet but the fact is I had some spare time while watching the NASCAR race to work on it If I was billing you it would be for about 4 hours work But I like these type of challenges so you are getting it for free. "This week only" LOL Take what I did and learn from it and you will master Excel in no time at all!!...

[Alechko777]

Hi Tom Again!

I see you're from Florida... My sister lives in Miami, Hallandale. My best childhood friend lives in Tampa... I've visited them last year.

I thing the best thing to do is to create the new sheet for ACC. Kits tables-Its Own TABLE...Because I may confuse because of all this data incorporated. You're right.
About SOP Documents again: The SOP documents I place in the same folder as the QC-Inspection file. There are many of them... Each of them has its unique name and number. What I really need-that in Option 1 opposite the SOP Document, after I SCAN the barcode, excel will generate the link to the right document from this folder. THIS IS VERY IMPORTANT TO ME! I can't upload all of these documents... of course. You created a link to some document somewhere in the net. How do I match this link to the folder with all of SOP docs?

[Tank997]

Alechko, the link I added was just an example, you can add a link to any file in a folder on your computer or a server or web address, where the file is located. For example cell M17 could just be a link to C:\SOP\304004.doc or any other address where your file resides. Play with it and you will get the idea. You just need to know the full Path or address of the file you want to link to and then add it to the table for each item.

Excel only knows what you tell it so adding the path, like I did, will return return it with the look up.

Test your scans and then see how the file works with them and let me know any issues you're having.

I am just up the road from your sister in Hallandale here in West Palm Beach, tell her I said HELLO!

[Alechko777]

WOW! You live in West Palm Beach?!?! What a Coincidence!!
Good for you!

Tom...look. I know how to make a link to some file I want. I give the right path and this is it. The problem is not here. The problem is as I said there are a lot of word and pdf files and as you see about 800 P/N's!!! If it possible to use VLOOKUP that Excel will find the word or pdf file that match our P/N? I can't imagine I rename 800 files and give them new filename with P/N. May be I just create a link to folder itself...that's all... I really got lost....

What do you do in your day-by-day life Tom?

[Tank997]

Alechko, I don't' know of a way to lookup or search for files just based on your P/N, without a cross reference list, that sounds like a Google desktop search from inside of Excel. It may be able to be done but it's above my pay grade Absent that you would need to add the document address in the column I added for it. I think there is a way to have excel give you the names of all the files in a folder which you may be able to then cross reference on a sheet and add it as a separate table that could work.

My day job right now is running my store as well as looking for a real job. The store pays for it's bills but not much more so I need real work to pay my bills.

[Alechko777]

Hi Dear Tom, friend of mine!
How r u there? I’m just enjoying your file you kindly built for me…listen you’re an amazing person! Really… However I would like to ask you couple more questions about the file.
1. The file we built is for outgoing and incoming inspections. Incoming is the first one that we perform at receiving products from manufacturer. Then goes outgoing inspection after upgrading them and before shipping them to the customer. There are PASSED and there are FAILED appliances, okay… I want that when the appliance that failed at incoming inspection comes for outgoing inspection, our QC Inspection file will remember it and mark it in red in the DEVELOPER object. I’ll add comments into the file itself. Is it possible Tom?
2. Please reveal how did you do that when I scroll to see what is in lower cells, the header suddenly freezes exactly below the ruler? This way I don’t need to use freeze panes option, and this is great!!!!
3. How did you format entire worksheet as a table?
What kind of store u run? Perhaps we could trade one with another…

[Alechko777]

Tom Hi!

Can u help me? Where are you? Is everything okay?

[Tank997]

Sorry Alecko777, just saw your posts and I will take a look at them now and get back to you. I am okay however everything here is not okay, my oldest dog who is 15 1/2 years old is on his last legs and will have to be put down in the next few days, we are having a very hard time dealing with that.

[Tank997]

1. The file we built is for outgoing and incoming inspections. Incoming is the first one that we perform at receiving products from manufacturer. Then goes outgoing inspection after upgrading them and before shipping them to the customer. There are PASSED and there are FAILED appliances, okay… I want that when the appliance that failed at incoming inspection comes for outgoing inspection, our QC Inspection file will remember it and mark it in red in the DEVELOPER object. I’ll add comments into the file itself. Is it possible Tom?
2. Please reveal how did you do that when I scroll to see what is in lower cells, the header suddenly freezes exactly below the ruler? This way I don’t need to use freeze panes option, and this is great!!!!
3. How did you format entire worksheet as a table?
What kind of store u run? Perhaps we could trade one with another…

1. I'm not sure I fully understand the question but the way I read it, I don't know how you would do that since you are scanning and inspecting a "Part Number" assuming you have more that one item per part number, how would the file know which one it is? I think you would need serial numbers that are unique to each item to do what I think you are asking for. You may want to look a a manual tagging system to handle this, but I may be misunderstanding your question.

2. I assume you mean when you scroll down on the sheet the table header names replace the column letters on the sheet correct? This is a built in feature of the tables in Excel 2007 and above and yes it's a great feature of the tables.

3. To convert a range to a table you just need to setup the data correctly with header row names above the data in each column. Once your range is setup just select one cell within the range and go to the insert tab on the ribbon and click on the table icon and it will popup a window where you will see the range it selected, you can change it there if needed and a check box for the header rows. You should just setup a sample sheet and play with it and I'm sure you'll pick it up in no time.

Good Luck and my store is a Mattress Store, I buy the beds from one company here in Florida. Thanks for the offer thou!

[Alechko777]

Hi Tom!
I'm really sorry to hear about your dog...miserable one...and people who own it miserable twice!!! Dog doesn't understand the sorrow of the friend loss as people do.
Animals have only an instinct, but one upon a time I saw dog crying like a man next to its owner's grave! I opened my mouse widely and start crying too....
Be strong Tom... Do what you have to do for your dog. It is your only friend that never betrays you... I'm so sorry again...

About the thread...
1. Yea.. You right. I've explained myself wrong. There is a need for S/N... true. I've built another file where I need this option that every time the appliance I scanned on Incoming Ispection (s/n of course, not p/n)-Excel will mark it in red if failed, in green if passed and no color if the appliance wasn’t tested at all by sampling. I'll upload the file on Sunday morning Tel-Aviv time...
2. Yes.. But how do I do it that header names will replace the column letters? Where this option hides?
3. I’ll try to do it on Sunday, thanx Tom.
4. Did you see the new thread I posted? About the links...

[Tank997]

Alechko, Thank you for your thoughts and kind words!

For #1 I would need to see your new/other file to understand what you want to do here.
For # 2 I don't think you can replace the column letters completely but you can try several things to hide them. If you move your inspection form to another sheet and move the table up to row 1 when you move down the sheet the column letters will be replaced so you can always see the table header names. Another option would be to turn off the "headings" on the view tab and then freeze the top row with freeze panes. This will remove the letters but you also hide the row numbers, if that's a problem you could add row number in column A and freeze that column as well. If you want to turn off the table headers and stop them from replacing the letter I'm sure there are several ways to do that as well but I've never tried that.
#4, I just looked at your other post and if I understand you correctly, you just and any text to the SOP Name cell and use a hyperlink to open a folder or another document so if you had "SOP Folder" in a cell just right click on it and click hyperlink, you will get the insert hyperlink dialog box where you can point to any folder or document on your computer, server or web site as well as other places. Not sure if that answers your question but play with it and see if it gives you what you need. Good Luck

[Alechko777]

Hi Tom!
How are you there so far in warm and sunny Florida?
How's your dog? I really hope he's in good health. You obviously remember how you helped me with my work in excel table. I really appreciate what you've done for me. I even asked our G*d to help your dog and success to business you run. I don't know if it helped, but I had good intentions. Now I have another challenge in excel database building...and I face difficulties to finish the table (see attached). Look at the table. We have 5 tabs. Pay attention only on EVAL and RMA tabs. When we receive the appliances from customers or another warehouse we divide them on several categories: RMA, EVAL, CONSIGN and etc’ because of some criteria’s. If the appliance is CONSIGN for example I just have to change the carton or nylon of it. When the appliance is defined as RMA I simply need to repair it: to replace failed components. The problem is the customers start to complain that we demand payments twice: as EVALand as RMA defined. For example S/N 0918B00838 is both demanded to pay as EVAL and as RMA.
The function I really need to find, will work that way: If the appliance in the EVAL tab it never can be in RMA tab. How to exclude double payments?