Hi guys,
I would appreciate a bunch if you could help me with the following issue. (it's excel 2007 I'm dealing with)
I have table with about 1k rows and 9 columns. It serves as a small db.
Each column contains some data in text format.
Using the automatic filter I filtered out sub-arrays based on one of the columns. So for example for Joe Doe I get 200 rows of data.
Now, I took this sub-array filtered out for Joe Doe, and copied only the visible cells to a new worksheet, say JD.xlsx. So now I have the JD.xlsx worksheet with 9 columns and 200 rows. Next, Joe Doe will add some info in this worksheet. All the info is added in columns 8 and 9. In the end he will send it back to me.
Once I get it back the problem starts. Ideally I would open the JD_completed.xlsx copy the 200 rows, go do my main worksheet(main.xlsx), filter out the rows meant for Joe Doe and paste it over. Well, it doesn't work this way.
Could you please suggest me an approach that does not require VBA?
Here is what I have tried:
- I found that the Open Office does work this way. Yet jumping from one software to another makes this approach quite cumbersome. So I would open JD_completed and main.xlsx in OO, copy the 200 rows table and paste it over the filtered entries of main.xlsx. Then I would close it and reopen it in MS Excel. Yet, there are compatibility issues, especially since on another sheet i am using the data from the table in various formulas.
- Second approach is the reference approach. I use the VLOOKUP function. To this end, for each row I compare the info from column 3(which is pretty much unique over the table, but might not be one day,and here is another potential weakness) in the main.xlsx file with the info in column 3 in JD_completed.xlsx. When matching I refer to the info from the same row for column 8 or 9. Again a shaky approach.
Still, I am looking for a easier approach since I have to demonstrate it to a bunch of people who are quite novice with excel. I would go with Open Office spreadsheet, but again I cannot force the guys to install it.
So is there anything out there that would allow for some sort of straight forward copy+paste process?
... and thank you for reading this long post
I would appreciate a bunch if you could help me with the following issue. (it's excel 2007 I'm dealing with)
I have table with about 1k rows and 9 columns. It serves as a small db.
Each column contains some data in text format.
Using the automatic filter I filtered out sub-arrays based on one of the columns. So for example for Joe Doe I get 200 rows of data.
Now, I took this sub-array filtered out for Joe Doe, and copied only the visible cells to a new worksheet, say JD.xlsx. So now I have the JD.xlsx worksheet with 9 columns and 200 rows. Next, Joe Doe will add some info in this worksheet. All the info is added in columns 8 and 9. In the end he will send it back to me.
Once I get it back the problem starts. Ideally I would open the JD_completed.xlsx copy the 200 rows, go do my main worksheet(main.xlsx), filter out the rows meant for Joe Doe and paste it over. Well, it doesn't work this way.
Could you please suggest me an approach that does not require VBA?
Here is what I have tried:
- I found that the Open Office does work this way. Yet jumping from one software to another makes this approach quite cumbersome. So I would open JD_completed and main.xlsx in OO, copy the 200 rows table and paste it over the filtered entries of main.xlsx. Then I would close it and reopen it in MS Excel. Yet, there are compatibility issues, especially since on another sheet i am using the data from the table in various formulas.
- Second approach is the reference approach. I use the VLOOKUP function. To this end, for each row I compare the info from column 3(which is pretty much unique over the table, but might not be one day,and here is another potential weakness) in the main.xlsx file with the info in column 3 in JD_completed.xlsx. When matching I refer to the info from the same row for column 8 or 9. Again a shaky approach.
Still, I am looking for a easier approach since I have to demonstrate it to a bunch of people who are quite novice with excel. I would go with Open Office spreadsheet, but again I cannot force the guys to install it.
So is there anything out there that would allow for some sort of straight forward copy+paste process?
... and thank you for reading this long post
