input criteria from multiple pages to master Closed

Question

Hello,

I'm very new to excel and here is what I'm trying to create.....

I have a workbook with several worksheets 15 or so.  These worksheets are types of companies ie c&d, batteries, hazwaste etc etc. tehse pages look like so...

[Company Name][address][phone][city][state][website]....[waste stream]
 [joes]..................................................................................[batteries]

some of these entries have more than one waste stream.

I created a master sheet which has all the companies listed in row 1 and all the waste streams in column 1

I then am in the process of putting an [X] in the cells that correspond between waste stream and company.

question:

Can I put a value into the master sheet data ranges for [X] that will 'vloockup'? through all the worksheets to see if the company and material match then place an [X] there

so companies is there ...............[joes shop]
material here [batteries].................[X] here

Then I would like to make another work book or sheet that will have a data validation so I can pick [waste stream] say batteries then it will give me the list of [companies] that handle this.

I would also like to be able to post this to the web via xml?

Thank you

confused

venkat1926 · Answer

let me t ry to rephrase your problem.  You have not said that how your data is configured if there are more than one "waste stsresm"(I do not understand the meaning of this technical term).  I can think of in what commodities the company deals either batteries or batteires and something else etc.

supposes your database is like this (columns other than company name I have omitted for example sake only)

company name	waste streaam	waste stream
joes	batteries	
abel	hazwaste	
cain	batteries	hazwaste

now you want to know in anoher WORKSHEET  the name of the companies dealing with batteris, the list of comfpanied dealing with hazwaste etc. please confirm my assumption is correct

aspecialdad · Answer

Ok here is the basic lay out of my spread sheet page 1 row 1 is companies starts in column C.
So row 1 column A (Materials) B(Companies) these are headers. C(A1 Organics)   D(Air Cycle Corp.), E(All American).

Going with some different verbiage to see if it is easier.....

Row 2 starts with the list of food, in column A going down 30 rows.  Column B has a hyperlink X to a corresponding worksheet where that food can be found ie: Gala Apples would go to the apples worksheet, Red and Sweet Onions would go to the Onions worksheet.  Starting in row 2 column C is my data plot area, so there is an X where the company and the food and company intersect.  So in the example below if it comes out right A1 Organics has green apples and sweet onions, then there is an X in box C4 on my spread sheet.


Food 	       Companies	             A1 Organics, 	Air Cycle Corporation   	All American
Gala apples............................................x
Fuji apples.......................................................................x
Red apples                                                                                                                    x
Green apples                                            x                                    x
florida oranges                                                                             x                                    x
navel oranges            
red oinion                                                                                  x                                     x
sweet onion                                            x

Now I have a number of worksheets that take the material in this example and groups them together so it would be like.........if the material were fruits it would list (worksheet) apples; gala, red, green, fuji, and (worksheet - onions) would list red onions, sweet onions etc on it along with where to buy them.

So on these worksheets is where each company is listed and information along with specific product.

What I want is 2 things I would like for either my scatter plot on the main page to auto fill in based on the information from each worksheet ie: apples, onions etc.

Then I would like to create a scenario where if someone chooses gala apples from a drop down list it will show  companies X, Y to get from.  Then they could click on company and go to the information page for that.

venkat1926 · Answer

still not clear.
suppose your first sheet is called "master sheet"
there are three (if necessary ) more sheets called "apples", "oranges" and "onions"

now "master sheet" is like this (click this webpage.-it is only a sample data)

http://www.speedyshare.com/files/21108067/aspecialdad.xls

see all the sheets.  how are the sheet in this file called apples,oranges,onions are configure
give one example for apples sheet only  or do you want to fill these three sheets from the data in the "master sheet"

where is the drop down lists. in which sheet in which cell

aspecialdad · Answer

Ok wow that makes it much easier to figure out.....

That is basically in watered down version.  So I have created multiple sheets probably more than I need.

https://authentification.site/files/21114365/aspecialdad1.xls

I made the changes.  Ideally I would like to fill in the master which has 30 some companies and 50 material(food) on it with a total of 15 different categories (apples, oranges, onions, etc.)

venkat1926 · Answer

I have uploaded file named aspecialdad2  the web page is (copy paste the address in your browser)

https://authentification.site/files/21126676/aspecialdad2.xls

Under this macro the data in sheet "master" is transferred to the various sheets like apples, oranges, onions
you are seeing the sheets after running the macro .   Your can recheck by again running the macro which is called "test" in the vb editor.  with this I feel your other requirements are duplicate needs.  From master sheet you want to move to particular sheet you have to click the cell adjacent to the name of the food. for example your see gray apples in A2 in "master" sheet. just click B2 you will go to apples sheet in which the companies dealing in this are there. 


You have made  those Xs as hyperlinks. keep them there because the macro used those hyperlinks.

aspecialdad · Answer

I've tried adding another company and running the macro but I get an error and the macro stops running.  What process should I use to add new companies?

aspecialdad · Answer

aybe i"m making this more difficult than it needs to be.  I've tried running the code on my database and no go.

So I have the master list which I would like to update the master company list and each individual sub sheet.

I have uploaded my actually workbook here. 

https://authentification.site/files/21222735/Recycle_template-12.xls

I have completed the main table with (X) hyperlinks to each page.  I would like for the master list sheet to update my master companies sheet with the waste that it has assigned.

example

mater list shows A1 Organics takes food waste, which the (x) is linked to food waste.
Air Cycle Corporation takes Fluorescent Straight, Mercury Vapor Bulbs, Non-PCB Light Ballast, Paper, cardboard, mixed.

so then if I click on the master companies list it should show.
A1 Organics, address, city, state, phone, website, info, then waste streams (food waste)  I want the food waste to be populated by the macro.

I'm having a bugger of a time here.  I'm new to VB and macros use in excel.

I also want it to update the sub-sheets like the first macro.  Every time I run it in my spreadsheet it gets errors and I can't get all the information to go through.

Hope this helps.  

Thank you..!!!

venkat1926 · Answer

1.have you added the new company in the master file in G1
2. have your inserted Xs against some commodities
3. have you made these Xs hyperlinks the corresponding company in row 1 in the same column in this case new company 
4. did you open a new sheet for this new company. 
5. did you enter this company in all the sheets with phone etc (your have to fill cols B C D

if you have done some or all these does the macaro stop and if so in what statement and what is the error messge

aspecialdad · Answer

Ok I did that on the test sheet I think that I must of had one of the links messed up it worked great there.

Going to try and update it to the master sheet i'm working on thank you,


J

Input criteria from multiple pages to master

9 responses

Similar discussions

Subscribe To Our Newsletter!