Macro to display data from the last 7 days only and to move relevant comments

Hi all,

I have quite a complicated request that I hope someone can shine some light on because I'm hopeless at this sort of thing.

I have a list of data pulled from an SQL query. Each row has a date against it. In another sheet, I would like to display only the rows that contain a date from the last 7 days. This bit is not too difficult (I have achieved it with excel formulae) but this problem comes when I want to retain that data added to these rows.

For instance, if a new row is inserted since an event has happened today, it will appear at the top of the list. The other items in the list would have had extra info in adjacent columns written against them, and this data will remain stationary while all the other rows being pulled from the query page will move down, thus jumbling all my data up.

What I'm wondering is if theres a way to keep this data in line with the data it has been written against?

Attached is an edited version of the workbook I am using. The SQL data is in the 'Raw Mats' tab and the pulled data is in the other. Currently it is displaying the rows from the last 7 days with some random info that I typed next to it.

Example Workbook.xlsx

Hope you guys can help. I thank you in advance.

Originally Posted by kvsrinivasamurthy

Insted of typing data from column G to K in Sheet "Raw Mats last 7 days non-stds"., same you type in "Raw Mats" sheet and extract thro formula.

Hi kvsrinivasamurthy,

This isn't really possible since it is an SQL query and if you enter data next to the lines in the first tab and refresh the query, the order gets mixed up. The way I have said to do it really is the only way to do it.

Bump. Any other ideas?

Hi Sanish Tirkey,

Yes I think a macro would be the only way to do it properly.

Try this formula based workbook

1/. I have added to, and amended, your named ranges, these are now dynamic.

2/. The new Helper column, Column L "Valid Rows" , can be hidden with the +/- grouping button.
As it stands this will return results as your data stands. If the query doesn't sort the dates descending as your sample suggests, it's no big deal to reverse the order, or to handle random date order.

3/. the formula in A4 becomes

Formula:

Please Login or Register  to view this content.

Row 1 contains the column number to return from your Query table, again this can be hidden.
This way the formula can be dragged across as required. (To column G?)
You could hard code this into the formula by changing A$1,B$1,C$1... to 3,13,1...

[EDIT]
Note the formula in Column L

Formula:

Please Login or Register  to view this content.

You could use IFERROR() with 2007 and above

Hi Marcol,

Thanks for your help thus far. This formula seems to work for transferring the correct data from the last 7 days, but will the additional columns (columns H-K) follow the corresponding values as they fall down the list? Or will it just be a case of they never move up and down the list, and when there is no data there - you change the comments associated with that row?

Originally Posted by Marcol

What do you want to do, or happen, in Columns H:K?

I basically want to assign the data that is entered into H:K to the columns pulled from the query (A:G). I want it so that if the data in A:G moves up or down or drops off completely, that the added data in H:K moves or drops off with it. The problem is that the queried data isn't linked to the added data, so if things move around, the data in H:K will remain static and therefore refer to the wrong rows.

It's kinda messy but I don't know how else to explain it or go about it.

Okay, I think I can see where you are coming from.

I don't think that can be done with formula alone, and with vba it will be risky.

Can you return/enter the data you enter in Columns H:K to your database?

If not we will need a separate table somewhere to retain the new data as it is added/modified to this end a common index will be required.

Can you draw an index number from your database?

Originally Posted by Marcol

Okay, I think I can see where you are coming from.

I don't think that can be done with formula alone, and with vba it will be risky.

Can you return/enter the data you enter in Columns H:K to your database?

If not we will need a separate table somewhere to retain the new data as it is added/modified to this end a common index will be required.

Can you draw an index number from your database?

The values in H:K are to be inputted by the user, they do not exist in the database and therefore cannot be returned from the database.

A separate table to retain the new data would be fine. The 'NS EVENT ID' in column M of the first tab is a unique number to each line. There will only be on occurence of each number in the table. Does that work?

Originally Posted by Marcol

I was aware that the info wasn't in the database, what I was meaning was, can't this be added to the db as it is generated by the users?

As I see it this is really an Access job, not one for Excel.

How many users are we talking about, and is this intended to be a shared file?

Got to go for now, but will look back later, and see what might be possible, depending on your reply to this post.

I'm pretty unfamiliar with access but I'll explain what the document and the planned functionality is.

The document is a summary sheet of problems occuring within our manufacturing plant. Each day, the spreadsheet will need to be updated to reflect the problems we had the day before and where everything currently stands. The database is an oracle database - the info for this is inputted via many modules by hundreds of people and this cannot be easily changed to enter new details into it, without going through a massive chain of command to implement changes to the program.

The number of users of the file will be around 10 people. That's why I need it to be quite clear which is a new problem in the plant and for the comments to the follow the problem that they were assigned to - otherwise it would just get too confusing with so many people editting the file.

I really appreciate your help - please take your time in replying.

Originally Posted by Sanish Tirkey

Below are the two macros tht you would need. All you need to do is create two ActiveX buttons on your main sheet where you put your data. Go to "View Code" for the first button paste the code for "Pre Change". Make sure you remove the Sub & End Sub lines. Similarly paste the code for "Post Change" in the second button.

Now, before you extract your data from database to "Raw Mats" you need to click on the first button. This will take the backup of your mnually entered details against Non-Std No (I have assumed that this is the unique value for each row. Please let me know if otherwise).

After your data is updated, click on the second button. This will pick up the manually entered details from backup and put it against the respective Non-Std No.

Let me know if this works.

Pre Change

Please Login or Register  to view this content.

Post Change

Please Login or Register  to view this content.

Hi Sanish Tirkey,

Sorry for wasting your time and I appreciate your effort but I think I would prefer an answer that shows me how to link the data I have entered to the data from the database as this is a common problem for me in a lot of my work and I will need to be able to replicate it several times.

Edit: Created a new thread that correctly shows what I am asking for.
http://www.excelforum.com/excel-gene...html?p=3165821

Originally Posted by shas1809

Hi All,
i have excel sheet where data is coming from google form, for this sheet i want to create a macro(button) that should filter and give last 7 days data based on my current date whenever i run this macro.
Can someone please help me out with the code!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread