I am making a simple map in excel. I have my floor plan on sheet 1 and the location of product on the floor on sheet 2.
Currently I have some formulas on sheet 1 that link to some sales data on sheet 2, but what I would really like to be able to do is use the search function on page 1 and it give me the location of the products in a given area.
The data is broken up into group location on the second sheet.
Post a example workbook, not pictures.
Make sure the sample workbook clearly shows the Sheet2 listing of products and locations. Also, where you want to be conducting this product search. If you have a properly organized table of products and locations on sheet2, I imagine a VLOOKUP or INDEX/MATCH would serve.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
All of the data on sheet two corresponds to similar locations on sheet 1
OK, so what cell should we be looking at? Can you provide an example of what you're going to type and where, and what you want to appear nearby...and where? Complete "sample" goals makes it easy for us to help.
To make it simple I want like to link the data on sheet two under intimates, onto sheet one, the intimates section is the upper left section of the map. I don't really care which cell it is, just somewhere in that general area. I don't need the data to be listed on sheet 1, I would just like to have the ability to see the location of a style on the map sheet.
I would eventually like to do that for every general area.
Interesting, and still you've not told me the simple thing I need to give you a solution.
"Based on the example sheet I uploaded, I would like __________ from cell _______ sheet1 to appear in cell _________ on Sheet2. Then _________ from sheet1 cell ______ to appear in cell _________ in sheet 2. Etc."
Several actual examples of what you want make this less of treasure hunt for me.
I would like to be able to search for the data on sheet 2 A643 thru A674, and it be linked somewhere around P13 on sheet 1.
That's 32 possible values in Sheet2!A643:A674
What value would you like to appear in P13 and why? What's the logic that says 1 value is correct over all the others? Is this a one-time link or are you expecting to be able to edit a cell on Sheet1 and the value in P13 change to something else.
If you don't provide more information, the best I can do is tell you how to make A643 appear in P13:
=Sheet2!A643
I'm sure that's not what you want. Perhaps if what you want is the first value after the work INTIMATES, then this:
=INDEX(Sheet2!$A:$A, MATCH($E$7, Sheet2!$A:$A, 0) + Row($A1)
You can copy that down and each cell will have the next value down from "Intimates". Is that something you can work with?
I would like to be able to search a style code from sheet2, directly on sheet1.
Currently, the only way I know of finding the location of the text in a cell is by going to the specific sheet is located on, in this case sheet2. After finding it under a certain heading, like intimates, then going back to sheet1 and finding the intimates area on the map.
I would like someone that isn't as comfortable with excel to have the ability to search for a style while sheet1 is open, as it is easier for them to understand. It would be easier for someone to open the finder while in sheet1, input the style code, and see that style code in the area where it is located. Kind of like Map Quest, you don't have to find a city in a list, and look up the quadrant, you just type in the data, and a location pops up.
I guess I need a way to link those text values on sheet2, to sheet1, and be able to search them on sheet1.
A true database has all the pertinent data on one row. Then normal worksheet functions can be used to spot any unique value in the database and return other info from the same row.
In the attached version of your sheet, I put in a column B on sheet2. Each item has a location listed on its own row.
Then on sheet1 you can enter any code into O2 and the location will appear in O3.
Very Clever, Thanks, I would have never thought of that, I was a little to deep down the rabbit hole.
Another benefit of a true database format is that since each record is now complete (row), you no longer have to group the items in the database in unfriendly ways.
How much simpler is it now to remove the "group" cells in column A since they just disrupt the database, then sort the database into by column A into an easy to read format. Sort alphabetically would be the most common. But with that, you can now jump directly to any item in the database if you wish to update the record.
============
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks