Feeding data from one spreadsheet to another.

[Jeffa]

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..

 

[OssieMac]

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,range_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]

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]

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'!A331,12,FALSE)

The table you are looking in is A230, not A331. 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

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?

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,range_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]

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?

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'!A331,12,FALSE)

The table you are looking in is A230, not A331. 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

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?

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,range_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






:

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..

 

[Jeffa]

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

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'!A331,12,FALSE)

The table you are looking in is A230, not A331. 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

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?

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,range_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






:

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..