paste on visible cells only

lserge

New Member
Joined
Jul 14, 2010
Messages
4
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
 

mcbetts

New Member
Joined
Apr 22, 2010
Messages
23
Is it possible to add another column?

If so, I would suggest adding a unique ID (for example, the new column could show the row number). That way, you have something that will always be unique and you can perform a vlookup on this.
 

lserge

New Member
Joined
Jul 14, 2010
Messages
4
Thx for the reply mcbetts.

I think i could add another column. It definitely helps.
another thing I was worried about is the reference to an external file i need to do for each of the sub-arrays.
Still I have a way around for the latest, which is again a "creative" fix. I was hoping to find smth rather simple and elegant.

But, honestly I am puzzled and disappointed that MS didn't include this option in the paste special section. It is quite intuitive that you would like to paste over the visible/sorted out cells. Open Office has this as default.
 

Lubomir

New Member
Joined
Jul 22, 2010
Messages
2
Lserge, can you please tell me how to do this in Open Office? I use OO version 3 and I can't find the way :)

Thank you a lot...
 

lserge

New Member
Joined
Jul 14, 2010
Messages
4
Lubomir, my OO version is 3.2.1. The process is quite intuitive. It is a matter of simple copy paste.
Try updating it and let me know if it works.
 

Lubomir

New Member
Joined
Jul 22, 2010
Messages
2
Lserge, I was trying to paste over hidden cells and not over filtered cells. You can not paste over hidden cells but you CAN paste over filtered cells. I'm sorry, my misstake. Thank you for the info about Open Office. It helped me A LOT :)
 

Forum statistics

Threads
1,081,526
Messages
5,359,280
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top