Cell Referencing Issues when Sort By and Retrieving Data based on Dates

I want to copy an entire worksheet into another workbook, basically I create a new workbook, we will call it Master Log, and I have 2 other workbooks called Log1 and Log2, ok so I want to take the essential data out of Log1, and the essential data out of Log2 and import it directly into this "Master Log" workbook.

So it sounds easy, I copy the format to the new work book, open a new worksheet name it similiar to the one I want to copy and then go to cell A1 and say =Log1 (Master)!A1 and then drag across and down, so all the cells are cell referenced....and then I repeart this process into another sheet and viola, it works, everything looks pretty good; however, because all the cells are referenced, I can't sort by data, as there is all equations in the cells....ugh

So I have a couple of issues, first of all,

is it possible to delete all cell referencing but keep the initial data, without having to copy/paste special it manually into another worksheet? Is there a macro that will perform this task? Keep in mind, users are going to continually use the other worksheets, and I want to show the results into this master sheet, but at the same time, I want to be able to sort through this data.

Is there a cleaner way to copy a page, without all these cell references?On a similar note, is it possible to have a dynamically copy similar to dynamic ranges, so as you add data the work sheet,(keep in mind this data is pulling from 2 seperate workbooks) it automatically updates the page? Because of the formatting of the cells, when I have a Date and once I surpass the rows being used, it returns the 1/1/1900 in column A down about 5000 rows, and 0's in the rest, and when I try to sort the data, by date, everything is thrown off?!?

Now as you can see in the example, my front page (with much assistance from jaslake) is able to pull data by date, so if I want to look at one date or a week of data, I have the flexibility to do that, but it only works, if these cells are not formatted with the "references", as you can see. If you would take the information from Log1 (master) and copy it directly into Log1 and do the same for Log2, you will see it will work flawlessly. Just do not delete these 2 sheets or then the macro will be "wacked"...then you will need to start over with the original file.

Also, I was thinking about putting some active buttons at the top of the page, that would sort the information by columns without having to go to the sort option. So if you clicked on cell B2, it would sort by Line, if you clicked on C2 it would sort by SKU, if you clicked on cell D2 it would sort by Error Description, etc. I had played around with the thought of adding a Tab Strip, and trying to add the macros to this and naming each Tab a "sort by" field, but only to save space...not sure if any of this will affect the outcome of what I would like to accomplish in the long term...so figured I would ask in advance if this will affect how the outcome?

Ok as always, any suggestions tips ideas advice, is appreciated, until then I will keep moving forward with this on my own, and update my progress...Thanks

Hi 00Able

In your previous thread, we weren't working with cell references, we were working with values. Had we been working with references, I'd have suggested

Please Login or Register  to view this content.

This may well address this issue

keep the initial data

I'm not clear what you're looking for here (I got rather involved in your prior post so I had a pretty clear idea where you were going) but see if the above code snippet does solve one issue. If not, let me know and I'll look at it.

You've got a lot of issues in this thread; see if the above resolves the Data issue. I'll be glad, as will many others on the Forum, to help with the other issues.

John

You've got a lot of issues in this thread; see if the above resolves the Data issue. I'll be glad, as will many others on the Forum, to help with the other issues.

I know, I am trying my best to only deal with one issue at a time in a post, so that it stays structured and I stay within the forum rules, but at the same time, I want to give an understanding of the final project, in its entirity, so we can prepare for other issues that we may face, as I didn't think cell referencing would pose a problem...yeah a bit niave on that aspect..its all a learning curve... I will play with this and see what happens, will post more as I find out.

I'm not clear what you're looking for here (I got rather involved in your prior post so I had a pretty clear idea where you were going

Until them I will try to explain more clearly what I am hoping to accomplish. I have 2 shifts working, I have an identical folder layed out for both shifts with many excel workbooks for every department, and covering every aspect of production. It is my desire that all the information stays uniform, and both shifts are keeping the same records. I want to be able to retrieve the data they input, without affecting their ability to enter those files and make changes, as there are lots of files and lots of users. So I thought, the best way to do this would be to cell reference to those sheets in a "Master" workbook, and then I would be able to access the data I wanted whenever I needed without interferring with a users access. Since I do a lot of data analysis, I was thinking it would be nice to be able to sort the information into a front page,with just the dates that I want, and then to make things even easier, if I could then add macros to the top to sort by the columns. Also, this workbook will be copied and used in several similiar instances of course some will have more columns then others, the layouts will be similiar.

I know it might sound trite to add a macro to sort data, but I believe a good manager should spend more time managing and developing employees then managing spreadsheets and scorecards. So I am looking for an approach that allows me to find a few extra minutes to be on the floor. As you can tell, I spend a lot of my off time, working to find these solutions. I know how to add macros, and have confidence this in general wont be hard, however, the more I got to thinking about it, is if I had one prebuilt into this sheet would save me time from having to add it to every copy of this worksheet I make. My only concern would be how many columns would I need, ok well I will start with 10 and hope that is enough, however it will probably be more like 15, so I am going to have to look at that in depth. Just not to sure if the Tab thingy will work for that, or if I am overshooting it? Seemed like it might be a good idea. I hope this helps you understand some of what I am trying to accomplish.

Hi 00Able

Hope you're making progress. You're in the real world; I'm in a fantasy world (retired...don't have many responsibilities other than Family). I don't have deadlines; I don't NEED to make things work.

Keep me posted. Be happy to help out where I can.

You'll need to give feedback as to what helps and what doesn't.

John

PS: I'll look at Test Tracker 11.xls and make recommendations as to how to bring Values over. Still not clear if this will resolve the issue.

Hi 00Able
Took a look at the code for Public Sub PullData()

Changes are underlined (see lines 310, 500 & 670)

Please Login or Register  to view this content.

The code will pull ACTUAL VALUES into Front Page rather than cell references. Let me know how things progress.

John

Hi 00Able

Alrighty then! Let me know how I can help.

John

Hi 00Able

I've added a couple lines of code to deal with this

it will not bring the original format (grid) with it

and added/modified some lines to deal with this

when I have a page that uses 12 or 13 columns, it will only pull the first 11 columns

The code will now pull as many columns of data that exist in the source worksheet

Please Login or Register  to view this content.

Let me know if this works for you.

John

The formatting looks great now, Thanks!

Ok, I took a screenshot of the original file notice how it ends at column K, then I took a screen shot of the one you just provided, notice the difference. I made all the changes that you showed, but my screen still looks like the origninal, which poses no problem for this layout, but if I need to add columns it will. What changes am I missing?

Hi 00Able

In one workbook, columns to the right of K are hidden. In the other workbook, they're not. Unhide the columns and see if that resolves the issue.

John

never realized you could Ctrl+Shift+Right, then Hide all columns, thats a nifty trick that I am going to keep in the bag, I like it.

Well, I have a compatibility issue between Excel 2010 and 2003. Ok, I made 10 macros, to sort by columns, when I make the macros, I use the Record Now mode, then I highlight the cells I want to sort by, press ctrl+shift+down, then I go to sort, and choose the column I want to sort by, I then click on an empty cell, and hit stop recording. All is good,it works fine. My problem, is when I take this to 2003, it will not work, as Excel 2010 has more rows then 2003, so the Macros return an error out of range. Ok so then I have to recreate all the macros using the 2003 program, deep sighs. This again sounds trite, but usually I do not have the extra time to do this at work. Is there a way I can do this without manually scrolling down to the max row level that 2003 offers?

Hi 00Able

Are we still talking about "Front Page" when you say you want to

sort by columns

Tell me what you'd like to do. This can all be handled, regardless of what version of Excel you're running (said the blind man, not certain of 2010, don't have it). Do you want options to sort on ANY column? Do you want to sort on only specific columns?

Let me know.

John

I have been attempting to upload my updated file to show you, and been trying to revise that last post, so let me see if I can do it now, I have been having issues on this site for the past hour...nope won't let me attach it

I added a tabs at the top of my page for labeled each column name, and then tied a macro inside it. to sort by that field, the site won't allow me to attach an excel file, but it will let me show a picture, so I will show you what I did...but...yes, all I want to do is sort the information on the "Front Page", nice to do it with all columns, again, as I plan on using this sample for lots of worksheets in the future, and do not know what columns are where...so I will just do it to all columns and I will have it if I need it, right?

Hi 00Able

Yeah, I've been having issues with the forum tonight also (like walking through mud). Pictures don't really help ME. Others, they might. My mind doesn't work that way.

Your file is getting rather large (although the last file wasn't huge). Try zipping the file then attach. I'd prefer to work with YOUR ideas rather that interjecting mine. It's YOUR workbook.

John

Sure enough you were right again...my file was under 800, so I thought I was good...lol

Again, the macros should work in your version, if not let me know, as I also have 2007 excel that I run on my laptop, I just hate my laptop, as I do not like typing on it...I don't understand everyones fascination with them, ok it comes in handy when traveling, but seriously thats about it...alright enough ranting...let me know your thoughts on how I can change the macros...

Hi 00Able

HA!!! I abandoned my desktop well over two years ago. Only use it when I need to test Office 2000 applications with Outlook 2000 (can't run more than one version of Outlook on the same platform).

Have TWO laptops running here! Vista and Office 2007 on one, XP and Excel 2000, Excel 2007 on the other. Gives me lot's of flexibility to help with issues like yours.

Alright, as you said, "enough ranting". I'll look at this perhaps tonight. If not, in the AM.

I like the "concepts" you come up with. We'll see if we can make them work.

John

Hi 00Able

I've only looked at your code briefly tonight as it's well past my bedtime. I can tell you this. I don't believe this will work in Excel 2003

Please Login or Register  to view this content.

I've run into that issue MANY times working between Excel 2000 and Excel 2007. The good news is that it can be fixed. Not tonight.

John

Hi 00Able

Change your sort routines to look like this for each of your buttons

Please Login or Register  to view this content.

This will work in all versions of Excel (I think).

John

I have made all the changes based on what you show, but I get an error, shown in the attachement with the ColumnLetter part of the code?
Any thoughts?

Hi 00Able

Sorry about that. I forgot to post this

Please Login or Register  to view this content.

Add this code to the bottom of Module2.

John