change date and don't change it tomorrow

October 19, 2012 at 14:30:57
Specs: Windows 7
Hello, lets say I have 3 sheets. At sheet 1 I am changing values, "OK", "M1", "M2", "S" and at sheet 2 and 3 I have a vlookup function that auto fills column B with the these values. I need a macro which is updating the date at column C when Column B is taking any of the above values. The values are starting from B7 to B27 and are merged 2 rows (B7 merged with B8, B9 with B9 and so on.. i dont know if this is changing anything). I don't mind if the macro is running automatically or if I have to run the macro at the end of the date, but the date must not change the other day when I rerun it to update date at new cells.

Thanks in advance


See More: change date and dont change it tomorrow

Report •

#1
October 20, 2012 at 16:45:24
It is not clear to me when you want the date to change.

Where are you are changing values "OK", "M1", etc?

What are the VLOOKUP functions doing?

In addition, I don't understand this:

"...but the date must not change the other day when I rerun it to to update date at new cells."

Please provide some more details.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 20, 2012 at 23:58:58
Sheet 1 is a summary with 80 students. It is a table with their names and months and I write OK, M1, M2 when they pay (full or half month, it doesn't matter). then there are 80 more sheets which is a "card" for every student. the vlookup function is to search for the student that this sheet is refers to and update a table which it has the months at column A, column B is the same as the month row at Sheet 1 (empty if he didn't pay, OK/M1/M2 if he paid) and column C is the column where I want the date when column B is updated with OK/M1/M2.

"...but the date must not change the other day when I rerun it to to update date at new cells."

I mean i don't want the date to be change the other day like today() function.

sorry for my english, I am not an english speaking person.

thanks


Report •

#3
October 21, 2012 at 09:05:24
How is the summary sheet laid out? Names in Column A (or where?) Months in Row 1 (or where)?

Do the names of the individual student sheets match the names in the Summary sheet exactly? No extra spaces, underscores, etc. What I am trying to determine is if a change is made to a student's record in the summary sheet, can the student's name be used to determine the sheet in which to place the date?

Macro's can be written that will automatically run when a change is made to a worksheet but changes that are the result of formula so not count as a "change". Therefore, what I would like to do is monitor the summary sheet so that when you enter your data, the code will recognize that change, determine which student's information was changed and then place the date in that student's sheet.

In order to write that code, I need to know how your summary sheet is laid out and whether or not the student's name from the summary sheet can be used to access that student's sheet.

If the sheet names do not match the student's name, that can be done with a macro so that you wouldn't need to change 80 sheet names manually.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 22, 2012 at 07:57:06
Ok, Summary Sheet (named "MATHITES") is like this:
Column C: Names
Row 2 - Column D "EGGRAFI" (this is the registration fee)
Row 2 - Column E to N (SEPTEMBER to JUNE)

Individual student name sheets are exactly the same as MATHITES sheet and at row I have a function "=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255)" which returns sheet name.

Now student individual sheets have some information but what I am interesting of is:
Column A - Row 7+8 (merged) is "EGGRAFI"
Column A - Row 9+10 to 27+28 (merged every two) are the months (SEPTEMBER to JUNE)
Column B - Row 7+8 to 27+28 is the vlookup function ("=IF(VLOOKUP($A$1;MATHITES!$C$3:$N$92;2;FALSE)>0;VLOOKUP($A$1;MATHITES!$C$3:$N$92;2;FALSE);"")") the bold number is changing to 3,4,5,6,.. until 12.
Column C is the where I want the date

All 80 student sheets are the same, the only thing that is changing is the name

I don't know if the information I provided are enough. If you need anything else I suppose I can attach a picture?


Report •

#5
October 28, 2012 at 13:59:26
is there any update on this?

Report •

#6
October 28, 2012 at 16:42:34
The only update is that I have been enjoying my weekend by not spending a lot of time on the computer.

I went to a High School football game, rebuilt my gas grill and put up a bunch of Halloween decorations. Now it's time to carve some pumpkins.

I'll see what I can come up with for you tomorrow.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question