Not sure... Possible IF,THEN or COUNT or combination

[Levi C]

I have 3 worksheets that each contain columns of data I need copied onto a separate worksheet in the same book. I need to automate the process to avoid mistyping or copy/paste errors from one worksheet to the other. The same three worksheets are used for every project, but the number of entries in the columns change with each project.

Is there a way to copy the contents of all of the cells and only the cells that contain data, either text or a number to the new worksheet any time data is entered into the other three sheets?

An example to help clarify...

Sheet 1 - "Minerals"
Name Fraction Net Acres
John 1/2 80
Jane 1/2 80

Sheet 2 - "Leasehold"
Name Fraction Net Acres
CELP 1/1 160

Sheet 3 - "ORRI"
Name Fraction Net Acres
Gibson 1/160 1

Can I copy the contents of the cells under the column headers onto a "summary sheet" in a similar format as above when I have no idea how many rows might appear under each column?

Thanks in advance.

 

[Levi C]

Forgot to mention

Sorry. I also forgot to mention. If it is at all possible I would like to avoid using any macros.

Thanks again.

 

[Oaktree]

Welcome to the board.

I really think the easiest way is going to be copy & paste from each of the three sheets. What "copy/paste errors" are you concerned about?

 

[Levi C]

Thanks for the reply. The "copy/paste" errors I'm referring to are as such. Each of the three sheets are related to the "new sheet" or recap page. The data will originally be entered into the sheets by me, but will then be handed to other people in other departments. If they have to make a change on one page I want the change to be reflected on the other pages so they don't have to copy and paste data four times.

In other words, if they change a cell on the "minerals" sheet it needs to also be reflected on the "recap" page without the user having to enter anything on the "recap" page.

Does this make any sense?

 

[Dan Waterloo]

Why is the data being entered into three diferent lists? Why not just use one long list to start with that includes an additional column called TYPE that would have a value of Minerals, Leasehold, or ORRI? You could use a pivot table or filter if you wanted to look at a subset of the entries.

 

[Levi C]

The data has to be in different lists because they are three different sets of data. The one long list is what I am trying to accomplish as a recap. But to arrive at the data each list uses a different set of formulas or a different way of arriving at the data. I'm basically trying to take a large workbook and put a simple recap page together automatically so that a billing department can use it. The users know even less than I about Excel and are so set in their ways they don't try to learn.

 

[Dan Waterloo]

If it's really necessary to combine three lists into one continuous list then I believe that you'll need to use a macro.

 

[Levi C]

Does anyone have an example macro to move the data from Sheet A to Sheet B?

 

[ADAMC]

Does this post help....?

http://www.mrexcel.com/board2/viewtopic.php?t=145246&highlight=open+files+workbook+folder

 

[Oaktree]

I think the bigger issue is how you're going to handle version control. For instance, if you send the report to Manager A and Manager B... what if Manager A makes changes to sheet 1 but Manager B makes changes to sheet 2 (or worse, what if they make *different* changes to the same sheet)? Who is going to reconcile the differences?

I'd still shy away from incorporating macros into the workbook (people will have security set to high and won't enable macros, etc.). Once you've answered the version control question, consolidating 3 sheets to 1 on the final workbook via copy and paste (even done manually) will take only a few seconds.

My advice is to keep the summary all-together out of the file you distribute, and send billing his own, consolidated copy once the final changes are made (which you or someone can build manually).