Copy more cells based on another cell's value

October 12, 2010 at 13:04:43
Specs: Windows XP
Hello!

I need help with multiple linked values and their sorting, copying..

I have one workbook with values like this (informative only)
A | B | C | D | E | F | G
ID No. | Height | Weight | Age | Color | Rating | Price

ID number is the most important value, based on that value all other values are entered manually and that's ok. Id number grows as rows add (row 2= ID 252, row 3 = ID 253, etc.).

I need to copy those values to another workbook but in different order. ID numbers are already in the other workbook, I just need to copy other values connected to ID number in order like this:

A | B | C | D | E | F | G
ID No. | Rating | Price | Color | Weight | Height | Age

So basically I'm constantly adding, copying values to Workbook 2 manually..
Can it be done more time saving and how?

I was thinking of a macro button next to each ID Number which will copy values in corresponding columns into the other woorkbook in different order. Or, can that be automatic? With one button press?
I really don't know how to do that. I want to get rid of manually copying of every value to another workbook, so I need advice..

Also, since those workbooks have large amount of data, would it be better to store them in access. I'm pretty newbie to this so any advice, guideance will be helpful..

Thankks in advance!

Vedran C.


See More: Copy more cells based on another cells value

Report •


#1
October 12, 2010 at 13:14:14
If the ID numbers are already in Book 2, you can use VLOOKUP to "import" the values from Book 1 in any order you want.

=VLOOKUP(A1, [Book1.xls]Sheet1'!$A$1:$F$5, 4, 0)

Read up on the VLOOKUP function in Excel help and come on back if you need any assitance.


Report •

#2
October 13, 2010 at 09:36:10
Thank you!

It was very helpful, it works very well..

Now some more questions regarding that database..

1. Can cells which still have no value (#N/A) be somewhat different from other cells and without that text #N/A. It would be nicer to look at, especially if they would be only light coloured..

2. Also, once cell looks-up for a source value, can it be turned into text/number/combination, so that it wouldn't be a formula anymore? Once cells have values they don't need to look it up anymore.

3. Since there's a lot of information, lots of data (until now 37055 rows, 21 columns), would it be safer, easier to store that database in access format and manipulate with it in likewise manner? If yes, please recommend me where to start with reading

Those databases are generated manually, entering one value after another, so maybe in Access it could be automated somehow?

Thanks!

Vedran C.


Report •

#3
October 13, 2010 at 11:23:09
1. Can cells which still have no value (#N/A) be somewhat different from other cells and without that text #N/A. It would be nicer to look at, especially if they would be only light coloured.

This will produce an empty cell instead of a #N/A error:

=IF(ISNA(VLOOKUP(A1,[Book1.xls]Sheet1!$A$1:$F$5, 4, 0)), "",VLOOKUP(A1, [Book1.xls]Sheet1!$A$1:$F$5, 4, 0))

This will try a VLOOKUP and if it returns #N/A, the IF statement is TRUE and the value_if_true ( "" ) is returned. Otherwise, it returns the result of the VLOOKUP.

2. Also, once cell looks-up for a source value, can it be turned into text/number/combination, so that it wouldn't be a formula anymore? Once cells have values they don't need to look it up anymore.

You can manually perform an Edit...Copy...PasteSpecial...Values.

The other option is a macro to replace the formula with the value.

3. Since there's a lot of information, lots of data (until now 37055 rows, 21 columns), would it be safer, easier to store that database in access format and manipulate with it in likewise manner? If yes, please recommend me where to start with reading

I know nothing about Access. In addition, while we get a number of Access related questions in this forum, they don't get a lot of answers. There just aren't any Access experts that hang out here. You might want to try a forum more firmly dedicated to Access.

Those databases are generated manually, entering one value after another, so maybe in Access it could be automated somehow?

See above.


Report •

Related Solutions

#4
October 14, 2010 at 09:44:19
Thank you!

I'm almost done with it..

Another question:
I modified my database, added another column which is now the first column. So vlookup works with cells in B column.
That first column represents let's say a city, so one more category. For now it will be entered manually. What I want is to separate rows with identical City value, and copy them to another workbook, one after another, like in original workbook, but without empty rows where was different city value. Something like a separate collection of data corresponding to each City value.
This kind of report would be done once in a while.

How can I manage that?

Vedran C.


Report •

Ask Question