Solved Having trouble creating a If/then,Vlookup statement in Excel

February 11, 2015 at 10:50:38
Specs: Windows 7
Is it possible to create an if/then and Vlookup statement in Excel that copies a whole row of information and populates a different workbook once a key value is entered into a specific column on that row?

See More: Having trouble creating a If/then,Vlookup statement in Excel

Report •

February 11, 2015 at 11:41:24
You can not use a single formula to populate an entire row, but you can (obviously) use formulas in each cell that you want to populate. Other than that, you probably need a macro.

Please explain what you are trying to do with a little more detail and we'll see if we can help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 11, 2015 at 11:58:50
Thanks for the response.

What I am trying to do is by using a master list of accounts, I want to take each account I enter into a row and copy it over into the corresponding worksheet based on the business we receive the account from. Below is an example of what one row of information from the "master list" of accounts would look like.

2/2/2015 APP192056179 American Classics Package 4,900.00 Bagwell

"Bagwell" would be the key phrase that triggers the row of information to copy into another worksheet that harbors all the "Bagwell" accounts.

Hopefully that makes sense, and thanks again for your help.

Report •

February 11, 2015 at 12:41:36
✔ Best Answer
It's not exactly clear to me what your process is.

Are you saying that you would be entering that info into the master list and as soon as "Bagwell" is entered, that row should be copied to the next available row on the "Bagwell" sheet?

If that is the case, then you would need a macro to accomplish that goal. The copying of a single line to a specific sheet based on the entry in a given column is fairly simple. Where things get complicated is when you make other changes to the master sheet, such as deleting data or make an error. "Whoops, I entered Filbert when I meant to enter Bagwell."

A task completed by a macro can not be undone with a simple Ctrl-Z. In fact, it can rarely be undone, period. Macros are really, really great at doing things but very, very poor at undoing them. The undo-ing in most cases needs to be a manual process done by the user. There is only so much that can be written into a macro to try and make the code idiot - errr - I mean - user proof. For example, if Bagwell is misspelled and a sheet with the misspelled name can't be found, that type of error can be caught and dealt with. However, if a sheet named Filbert exists, then the data is going to be copied to that sheet whether it belongs there or not. Once the user changes Filbert to Bagwell, the data will then be copied to the Bagwell sheet but it won't be deleted from the Filbert sheet.

This might be a case where a real database program would make sense. I have not developed any databases on my own, but it is my understanding that database applications, such as Access, can "re-associate" data much better than an Excel macro can. There would be a field for a "name" and all associated fields for that record would be associated with whatever name appears in that field. If you change the name in the name field, all of the other fields would follow the change. Reports would be pulled based on the name field and all of the data associated with that name would come along with it. In addition, reports could be built based on other fields, pulling matching data from multiple "name" records.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

February 11, 2015 at 13:04:51
Yes you were correct in what I am trying to accomplish. So it looks like I am going to weigh the options of trying out a macro or using Access to help sort my data more efficiently.

At least now I know how to fix my problem, thanks for your help.

Report •

Ask Question