Vlookup and Hlookup combined?

B

bluto

New Member
Joined
Feb 4, 2005
Messages
43
I have a workbook with 2 worksheets. The main sheet is for data entry, and the second has parts listed.

The data entry sheet looks like this:

Item: WidgetA Qty: 2 ("WidgetA" is in cel A5 and "2" is in cel B5)
Parts Req: Qty Req: (these cels [A8-A28 for part names and B8-B28 for quantities] should be populated based on cels A5 and B5 above)

The parts sheet looks like this (I used dashes for formatting purposes):

Part---WidgetA---WidgetB
abc-------1----------2 (these are the number of this particular part needed for each widget)
bcd------------------1 (these are the number of this particular part needed for each widget)
cde-------1----------- (these are the number of this particular part needed for each widget)
def-------2----------- (these are the number of this particular part needed for each widget)
efg-------2----------1 (these are the number of this particular part needed for each widget)

What I would like to accomplish is to have only the parts for a particular Widget from the parts sheet automatically listed on the data entry sheet, and the quantities reflect the number of parts per widget times the number of widgets. No widget uses more than 20 individual parts, but the parts list will contain 100 parts to cover all types of widgets. (Sorry I don't know how to copy and paste the actual spreadsheets to this message to make it easier to look at!)

Thanks!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Something lke this?
Personal.xls
ABCD
1PartWidgetAWidgetB
2abc12
3bcd1
4cde1
5def2
6efg21
7
8
9Partbcd
10
11WidgetWidgetB
12
13Formula1
This Month



P.S.
Sorry I don't know how to copy and paste the actual spreadsheets to this message to make it easier to look at!)
Click to expand...

did you try Copying and Pasting it ??
 
Upvote 0
When I copy and pasted the cels into the message body, it just did the text with no formatting. How do I get the whole sheet(s) to appear like you did? Thanks!
 
Upvote 0
Cool Thanks. I will try the formula and get back with you (and post actual sheets this time!) if I have any problems!
 
Upvote 0
Unfortunately, the previous formula didn't work.

This is what the data entry sheet looks like:
WorkOrder (test).xls
ABCD
3
4Item:Qty
5WidgetA2(IwillinputthetypeofWidgetandthequantityofeach)
6
7PartsReq:QtyReq:
8abc2(theindividualpartsandquantitiesofeachpartshouldbeautomaticallypopulated)
9cde2
10def4
11efg4
12
Sheet1


Cels A8-B11 (or higher, depending on the number of different parts) should be auto-populated by locating the matching type of widget on the parts sheet:
WorkOrder (test).xls
ABCD
2
3partWidgetAWidgetB
4abc12
5bcd1
6cde1
7def2
8efg21
9
Sheet2


Then, based on what is input in cels A5 and B5 of the data entry sheet, and what parts are required from the parts sheet, will auto-populate the parts and quantity summary on the order entry sheet (the greyed in area) excluding parts not required.
 
Upvote 0
Below is the main "Work Order" page I will input data into. I will use a dropdown to select the Widget Type (Widget1, Widget2, etc.). I will input a quantity of widgets I want.<HTML><HEAD><Script Langage JavaScript><!---
function ViewSource() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource = RetDeleted(HtmlSource);
document.write('<HTML><BODY BGCOLOR=#E0F4EA><CENTER><FORM><TEXTAREA ROWS=30 COLS=90%>');
document.write(HtmlSource);
document.write('</TEXTAREA></FORM></CENTER></BODY></HTML>');
}
function CopyToClipBoard() {
var HtmlSource;
HtmlSource = document.all.ForSubmit.innerHTML;
HtmlSource=RetDeleted(HtmlSource);
window.clipboardData.setData("Text",HtmlSource);
alert('Html source of above image\n\nhas been copied to your clip board\n\nJust paste it into Message Body\n\nIf you cannnot paste source from clip board,\n\nclick [View Source] button and paste manually.');
}
function RetDeleted(targetstring) {
if (targetstring.indexOf(unescape('%0D%0A')) > -1) rcode = unescape('%0D%0A')
else if (targetstring.indexOf(unescape('%0A')) > -1) rcode = unescape('%0A')
else rcode = unescape('%0D');
i = 0;
p = '';
while (targetstring.indexOf(rcode,i) != -1) {
m = targetstring.indexOf(rcode,i);
p += targetstring.substring(i,m);
i = m + rcode.length;
}
p += targetstring.substring(i,targetstring.length);
return p;
}</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.42]</FONT><BR><BR></CENTER><HR><BR><SPAN id='ForSubmit'>
Test WorkOrder.xls
ABCDEF
1WO#00000Date:
2Name:
3SelectWidgetType:Widget1SelectQty:2
4
5PartDescriptionQtyReq'dQtyPulledErrorComments
6 
7 
8 
9 
10 
11 
12 
Work Order
</SPAN><BR><CENTER><HR><FORM NAME='form1'><INPUT TYPE='Button' value='Please click this button to send the source to clipbord' onClick='CopyToClipBoard();'><INPUT TYPE='Button' value='View Source' onClick='ViewSource();'></FORM><FONT COLOR=#339966 SIZE=2> This free code was written by Colo and Ivan F Moala:[HtmlMaker 2.42] - 15th May 2003</FONT><BR><FONT COLOR=#339966 SIZE=2>Code mods by Ivan F Moala - 15th May 2003</FONT></HR></BODY></HTML>

Below is the Widget1 page (tab/worksheet) that has the different parts, descriptions, and quantities required for each Widget1.
Test WorkOrder.xls
ABCD
1C001-4Case1
2AAA-BAAABattery4
3SW-1Switch1
4LED-RRedLED2
Widget1


What I would like to accomplish is to have the parts, descriptions, and quantities (multiplied by the number of widgets ordered) automatically populate the blank area in the Work Order page, like this:
Test WorkOrder.xls
ABCDEF
1WO#00000Date:
2Name:
3SelectWidgetType:Widget1SelectQty:2
4
5PartDescriptionQtyReq'dQtyPulledErrorComments
6C001-4Case2
7AAA-BAAABattery8
8SW-1Switch2
9LED-RRedLED4
10
11
12
Work Order


I will have ~20 different Widgets so there will be ~20 different tabs/worksheets with different parts for each different widget. Therefor, depending on which widget is selected from the dropdown and the quantity ordered, I need a macro or formula that will automatically go to the appropriate widget tab/worksheet, pull the parts and descriptions and paste that on the work order page, as well as take the number of parts required and automatically populate the work order page after multiplying by the number of widgets ordered.

There will only be one widget per work order. The number of parts will vary, but none will have more than 15 parts, so I will want to copy and paste 15 rows from the parts tab/sheet to the work order sheet.

I hope this is clear. Since there will be ~20 different widget choices, if/then/else won't work (and would be major embedding!) so I hope someone has a "simple" solution. I investigated pivot tables, but those are mostly greek to me (no offense to my Greek friends!)...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
VBA for Generating Cycle Counts
2
Replies
17
Views
298
kvsrinivasamurthy
K
O
  • Question
Excel/VBA to conditionally send emails per schedule
Replies
0
Views
915
OC2PS
O
D
  • Question
Help with sorting through order data
Replies
5
Views
259
Fuji
F
C
  • Question
Duplicates with different data to combine with VBA
Replies
2
Views
373
chipsworld
C
A
  • Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
478
AlfredDB
A

Forum statistics

Threads
1,221,581
Messages
6,160,629
Members
451,661
Latest member
hamdan17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top