Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with multiple sheets in a workbook
Hi,
I have 2 seperate sheets in my spredsheet that contains different data each, but both sheets have a comon factor of inv number and line. I want to be on sheet 1, lookup the info on sheet 2, if the Inv number and line agree, then select column c from sheet 2, and insert it into sheet 1. Example: Sheet 1: Culumn A - Invoice No. Column B - Line No. Column c - Customer Sheet 2: Column A - Inv No. Column B - Line No. Column C - Dimension So if both column A&B match for both sheets, then take column C from sheet 2, and insert it in column D on Sheet 1. Can this be done. I tried the VLOOKUP, the IF, the OR, the AND, and nothing seems to work. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with multiple sheets in a workbook
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Malcolm wrote: Hi, I have 2 seperate sheets in my spredsheet that contains different data each, but both sheets have a comon factor of inv number and line. I want to be on sheet 1, lookup the info on sheet 2, if the Inv number and line agree, then select column c from sheet 2, and insert it into sheet 1. Example: Sheet 1: Culumn A - Invoice No. Column B - Line No. Column c - Customer Sheet 2: Column A - Inv No. Column B - Line No. Column C - Dimension So if both column A&B match for both sheets, then take column C from sheet 2, and insert it in column D on Sheet 1. Can this be done. I tried the VLOOKUP, the IF, the OR, the AND, and nothing seems to work. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet to worksheet . . . | Excel Worksheet Functions | |||
How do I copy print formatting to multiple sheets in a workbook? | Excel Discussion (Misc queries) | |||
saving multiple sheets in a workbook | Excel Discussion (Misc queries) | |||
Opening multiple Excel files that contain varied selected sheets | Excel Worksheet Functions | |||
how do i switch between multiple sheets in the same workbook (key. | Excel Discussion (Misc queries) |