Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Feeding data from one spreadsheet to another.
Hello,
I hope you guys can help me, I've had this issue that I'm dying to solve!. I have a spreadsheet that has around 15 colums, each line has unique information about a pc/laptop.. For example Col A: Asset Id number Col B: Description Col C: SMS Last update Col D: Primary User Now, in another spreadsheet, which is a direct dump from our CMDB I have a similar setup where you have Col A: Asset Id number Col B: SMS Last update Col C: Primary User I would like this second spreadsheet to update the associated SMS update and primary user update.. So it would search the second workbook and go, ok so Col A in the first sheet is W1125021 and Col A in the second workbook is W1125021, what is column B's data in the second sheet, ok, I'll put that in column C's data in the first wookbook. Hope you can help, and I hope this was understandable.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Feeding data from one spreadsheet to another.
You could use VLOOKUP function.
Example:- Assuming that the first spreadsheet is sheet 1 and the CMDB dump is sheet 2 with the asset Id in column A in each case. Your formula in column C would be something like this =VLOOKUP(A2,Sheet2!$A$2:$D$30,3,FALSE) The following is a description of the formula:- VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) You can get more information in help but here is a little to go with it. Lookup_value is your Id number. table_array is the range to lookup on sheet 2. Note that this range is in absolute format. That is it has $ signs in front of both the column and row so that the lookup range will not alter as you copy the formula down the column. Also the column in which vlookup expects to find the match must be the first column of this array. col_index is the number of the column in the array to get the data to put into where the formula is. In the above it is column C which is the third column. false says to only accept exact matches. Regards, OssieMac "Jeffa" wrote: Hello, I hope you guys can help me, I've had this issue that I'm dying to solve!. I have a spreadsheet that has around 15 colums, each line has unique information about a pc/laptop.. For example Col A: Asset Id number Col B: Description Col C: SMS Last update Col D: Primary User Now, in another spreadsheet, which is a direct dump from our CMDB I have a similar setup where you have Col A: Asset Id number Col B: SMS Last update Col C: Primary User I would like this second spreadsheet to update the associated SMS update and primary user update.. So it would search the second workbook and go, ok so Col A in the first sheet is W1125021 and Col A in the second workbook is W1125021, what is column B's data in the second sheet, ok, I'll put that in column C's data in the first wookbook. Hope you can help, and I hope this was understandable.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Feeding data from one spreadsheet to another.
This didn't quite work, I get mismatch 13 error..
=VLOOKUP(A2,'[dump]dump-ovsd-dsk-lpt'!$A$2:$D$30,12,FALSE) Remember they are different workbooks.. I don't quite get what the $d$30 is for either.. Can you explain that one? "OssieMac" wrote: You could use VLOOKUP function. Example:- Assuming that the first spreadsheet is sheet 1 and the CMDB dump is sheet 2 with the asset Id in column A in each case. Your formula in column C would be something like this =VLOOKUP(A2,Sheet2!$A$2:$D$30,3,FALSE) The following is a description of the formula:- VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) You can get more information in help but here is a little to go with it. Lookup_value is your Id number. table_array is the range to lookup on sheet 2. Note that this range is in absolute format. That is it has $ signs in front of both the column and row so that the lookup range will not alter as you copy the formula down the column. Also the column in which vlookup expects to find the match must be the first column of this array. col_index is the number of the column in the array to get the data to put into where the formula is. In the above it is column C which is the third column. false says to only accept exact matches. Regards, OssieMac "Jeffa" wrote: Hello, I hope you guys can help me, I've had this issue that I'm dying to solve!. I have a spreadsheet that has around 15 colums, each line has unique information about a pc/laptop.. For example Col A: Asset Id number Col B: Description Col C: SMS Last update Col D: Primary User Now, in another spreadsheet, which is a direct dump from our CMDB I have a similar setup where you have Col A: Asset Id number Col B: SMS Last update Col C: Primary User I would like this second spreadsheet to update the associated SMS update and primary user update.. So it would search the second workbook and go, ok so Col A in the first sheet is W1125021 and Col A in the second workbook is W1125021, what is column B's data in the second sheet, ok, I'll put that in column C's data in the first wookbook. Hope you can help, and I hope this was understandable.. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Feeding data from one spreadsheet to another.
Hi again Jeffa,
I assume that 'dump' is the name of the other workbook and 'dump-ovsd-dsk-lpt' is the name of the worksheet in that workbook. If this assumption is correct then your only problem is that you are trying to insert data from column 12 (col_index_num) of the table array when there is only 4 columns in $A$2:$D$30. If you want to insert data from column 12 than it should be $A$2:$L$30. The $A$2:$D$30 with the dollar signs is called absolute mode. That is it does not alter as you copy the formula down to other cells. For example, if you did not have them then when you copy the formula down to the next cell then it would become:- =VLOOKUP(A3,'[dump]dump-ovsd-dsk-lpt'!A3:D31,12,FALSE) The table you are looking in is A2:D30, not A3:D31. This table range cannot change. However lookup_value does need to change. That is it changes from A2 to A3 and hence it is not an absolute value. A working example looking up data in Sheet1 of another workbook called Jeffa Lookup.xls and inserting data from column 4. Note that the first column of table_array must contain the data being looked up. =VLOOKUP(A2,'[Jeffa Lookup.xls]Sheet1'!$A$2:$D$30,4,FALSE) If the data is not found then it returns #N/A Hope this helps. Regards, OssieMac "Jeffa" wrote: This didn't quite work, I get mismatch 13 error.. =VLOOKUP(A2,'[dump]dump-ovsd-dsk-lpt'!$A$2:$D$30,12,FALSE) Remember they are different workbooks.. I don't quite get what the $d$30 is for either.. Can you explain that one? "OssieMac" wrote: You could use VLOOKUP function. Example:- Assuming that the first spreadsheet is sheet 1 and the CMDB dump is sheet 2 with the asset Id in column A in each case. Your formula in column C would be something like this =VLOOKUP(A2,Sheet2!$A$2:$D$30,3,FALSE) The following is a description of the formula:- VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) You can get more information in help but here is a little to go with it. Lookup_value is your Id number. table_array is the range to lookup on sheet 2. Note that this range is in absolute format. That is it has $ signs in front of both the column and row so that the lookup range will not alter as you copy the formula down the column. Also the column in which vlookup expects to find the match must be the first column of this array. col_index is the number of the column in the array to get the data to put into where the formula is. In the above it is column C which is the third column. false says to only accept exact matches. Regards, OssieMac "Jeffa" wrote: Hello, I hope you guys can help me, I've had this issue that I'm dying to solve!. I have a spreadsheet that has around 15 colums, each line has unique information about a pc/laptop.. For example Col A: Asset Id number Col B: Description Col C: SMS Last update Col D: Primary User Now, in another spreadsheet, which is a direct dump from our CMDB I have a similar setup where you have Col A: Asset Id number Col B: SMS Last update Col C: Primary User I would like this second spreadsheet to update the associated SMS update and primary user update.. So it would search the second workbook and go, ok so Col A in the first sheet is W1125021 and Col A in the second workbook is W1125021, what is column B's data in the second sheet, ok, I'll put that in column C's data in the first wookbook. Hope you can help, and I hope this was understandable.. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Feeding data from one spreadsheet to another.
ok, I think I'm getting there, thanks for your patience OssieMac.
So If my Dump spreadsheet has 5600 lines (assets) should I have $A$2:$D$5600? "OssieMac" wrote: Hi again Jeffa, I assume that 'dump' is the name of the other workbook and 'dump-ovsd-dsk-lpt' is the name of the worksheet in that workbook. If this assumption is correct then your only problem is that you are trying to insert data from column 12 (col_index_num) of the table array when there is only 4 columns in $A$2:$D$30. If you want to insert data from column 12 than it should be $A$2:$L$30. The $A$2:$D$30 with the dollar signs is called absolute mode. That is it does not alter as you copy the formula down to other cells. For example, if you did not have them then when you copy the formula down to the next cell then it would become:- =VLOOKUP(A3,'[dump]dump-ovsd-dsk-lpt'!A3:D31,12,FALSE) The table you are looking in is A2:D30, not A3:D31. This table range cannot change. However lookup_value does need to change. That is it changes from A2 to A3 and hence it is not an absolute value. A working example looking up data in Sheet1 of another workbook called Jeffa Lookup.xls and inserting data from column 4. Note that the first column of table_array must contain the data being looked up. =VLOOKUP(A2,'[Jeffa Lookup.xls]Sheet1'!$A$2:$D$30,4,FALSE) If the data is not found then it returns #N/A Hope this helps. Regards, OssieMac "Jeffa" wrote: This didn't quite work, I get mismatch 13 error.. =VLOOKUP(A2,'[dump]dump-ovsd-dsk-lpt'!$A$2:$D$30,12,FALSE) Remember they are different workbooks.. I don't quite get what the $d$30 is for either.. Can you explain that one? "OssieMac" wrote: You could use VLOOKUP function. Example:- Assuming that the first spreadsheet is sheet 1 and the CMDB dump is sheet 2 with the asset Id in column A in each case. Your formula in column C would be something like this =VLOOKUP(A2,Sheet2!$A$2:$D$30,3,FALSE) The following is a description of the formula:- VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) You can get more information in help but here is a little to go with it. Lookup_value is your Id number. table_array is the range to lookup on sheet 2. Note that this range is in absolute format. That is it has $ signs in front of both the column and row so that the lookup range will not alter as you copy the formula down the column. Also the column in which vlookup expects to find the match must be the first column of this array. col_index is the number of the column in the array to get the data to put into where the formula is. In the above it is column C which is the third column. false says to only accept exact matches. Regards, OssieMac "Jeffa" wrote: Hello, I hope you guys can help me, I've had this issue that I'm dying to solve!. I have a spreadsheet that has around 15 colums, each line has unique information about a pc/laptop.. For example Col A: Asset Id number Col B: Description Col C: SMS Last update Col D: Primary User Now, in another spreadsheet, which is a direct dump from our CMDB I have a similar setup where you have Col A: Asset Id number Col B: SMS Last update Col C: Primary User I would like this second spreadsheet to update the associated SMS update and primary user update.. So it would search the second workbook and go, ok so Col A in the first sheet is W1125021 and Col A in the second workbook is W1125021, what is column B's data in the second sheet, ok, I'll put that in column C's data in the first wookbook. Hope you can help, and I hope this was understandable.. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Feeding data from one spreadsheet to another.
Never mind, I figured it out :) and it is working!.
I can't thank you enough OssieMac, cheers for your assistance, you're blood's worth bottling! -Jeffa "OssieMac" wrote: Hi again Jeffa, I assume that 'dump' is the name of the other workbook and 'dump-ovsd-dsk-lpt' is the name of the worksheet in that workbook. If this assumption is correct then your only problem is that you are trying to insert data from column 12 (col_index_num) of the table array when there is only 4 columns in $A$2:$D$30. If you want to insert data from column 12 than it should be $A$2:$L$30. The $A$2:$D$30 with the dollar signs is called absolute mode. That is it does not alter as you copy the formula down to other cells. For example, if you did not have them then when you copy the formula down to the next cell then it would become:- =VLOOKUP(A3,'[dump]dump-ovsd-dsk-lpt'!A3:D31,12,FALSE) The table you are looking in is A2:D30, not A3:D31. This table range cannot change. However lookup_value does need to change. That is it changes from A2 to A3 and hence it is not an absolute value. A working example looking up data in Sheet1 of another workbook called Jeffa Lookup.xls and inserting data from column 4. Note that the first column of table_array must contain the data being looked up. =VLOOKUP(A2,'[Jeffa Lookup.xls]Sheet1'!$A$2:$D$30,4,FALSE) If the data is not found then it returns #N/A Hope this helps. Regards, OssieMac "Jeffa" wrote: This didn't quite work, I get mismatch 13 error.. =VLOOKUP(A2,'[dump]dump-ovsd-dsk-lpt'!$A$2:$D$30,12,FALSE) Remember they are different workbooks.. I don't quite get what the $d$30 is for either.. Can you explain that one? "OssieMac" wrote: You could use VLOOKUP function. Example:- Assuming that the first spreadsheet is sheet 1 and the CMDB dump is sheet 2 with the asset Id in column A in each case. Your formula in column C would be something like this =VLOOKUP(A2,Sheet2!$A$2:$D$30,3,FALSE) The following is a description of the formula:- VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) You can get more information in help but here is a little to go with it. Lookup_value is your Id number. table_array is the range to lookup on sheet 2. Note that this range is in absolute format. That is it has $ signs in front of both the column and row so that the lookup range will not alter as you copy the formula down the column. Also the column in which vlookup expects to find the match must be the first column of this array. col_index is the number of the column in the array to get the data to put into where the formula is. In the above it is column C which is the third column. false says to only accept exact matches. Regards, OssieMac "Jeffa" wrote: Hello, I hope you guys can help me, I've had this issue that I'm dying to solve!. I have a spreadsheet that has around 15 colums, each line has unique information about a pc/laptop.. For example Col A: Asset Id number Col B: Description Col C: SMS Last update Col D: Primary User Now, in another spreadsheet, which is a direct dump from our CMDB I have a similar setup where you have Col A: Asset Id number Col B: SMS Last update Col C: Primary User I would like this second spreadsheet to update the associated SMS update and primary user update.. So it would search the second workbook and go, ok so Col A in the first sheet is W1125021 and Col A in the second workbook is W1125021, what is column B's data in the second sheet, ok, I'll put that in column C's data in the first wookbook. Hope you can help, and I hope this was understandable.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I pull data from a spreadsheet to another spreadsheet | Excel Discussion (Misc queries) | |||
How do I copy spreadsheet data as shown to another spreadsheet? | Excel Discussion (Misc queries) | |||
Automatic Feeding | Excel Discussion (Misc queries) | |||
Charts not updating when data feeding chart is changed | Charts and Charting in Excel | |||
Macro - Feeding path and file name to PDF maker | Excel Discussion (Misc queries) |