I need to cross reference from one tab to another

[TomRet]

First of all I would like to give a huge shout out to natefarm who created this awesome code for me and tweaked several of my formulas as well. I would also like to thank those who had a part in building this worksheet even though you may not have known it.

Now, if you look at the posted workbook you will see that everything in column M matches a colored tab; this allows the code to transfer the 'show days' to the appropriate pages. My problem is that you can clearly see Merry ordered his ticket for the wrong day and now will be viewing the show by himself. In the 'Changes' tab I have made a note of that but in order for me to make sure that he gets to enjoy the evening with his friends I must then manually change it on the actual 1Fri page. This is not a problem except when I rerun the macro it will force him back to 2Fri.

So, is there a way to modify the code taking into account the 'Changes' page?

Cheers
Tom

[FDibbins]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[TomRet]

Bump!

Thanks,
Tom

[TomRet]

Bumpity Bump!

Cheers,
Tom

[millz]

Hi Tom, try adding this macro.

Please Login or Register  to view this content.

and in your GetSalesData macro, add this line:

Please Login or Register  to view this content.

This should update any changes found in the Change worksheet and also cancel any thing found in Cancelation worksheet.

Hopefully this is what you want.

[TomRet]

Will these be two separate codes then? Meaning, will I have to run each one?

thanks
Tom

[millz]

No, adding the line Call FindChangesCancellations will instruct the GetSalesData macro to run it. So you just have to run GetSalesData.

[TomRet]

Will these be two separate codes then? Meaning, will I have to run each one?

thanks
Tom

[TomRet]

Cool, I'll try that momentarily. I am not sure why this posted a second time...

Cheers
Tom

[TomRet]

That is so awesome! Now for the kicker...first, while it ran very well it deleted the 'Change' and 'cancelation', I think I will need those to remain on their respective pages. The hard part (well, hard for me) is that I need Merry's ticket to change for only the third show of the season (which I realize now I did not notate). Is something like that possible? Would I need to set up the Change page like the sales page with the 'Events' in column A?

Regards
Tom

[millz]

If you want the data to remain in Change and Cancelation, just remove or comment off these lines:

Please Login or Register  to view this content.

I don't quite get your second point.

[TomRet]

Ah. there are four plays represented by the four different colored tabs. On the sales page there are also four area of colored cells matching the tabs with the exception of the clear cells at the top which represent season tickets (tickets to all shows). Now, Merry has purchased Season tickets with the showing of each show on the second Friday (2Fri). He wants to change only the day of the third show, not every show. Can this be done?

I edited out the code that you mentioned...it doesn't work anymore. :S

Cheers
Tom

[TomRet]

EDIT:

I edited out the code that you mentioned...it doesn't work anymore. :S

I realized my mistake on this and fixed it. Works fine now with one minor exception. For record keeping I need to it to also retain the 'From' and 'To' data (columns G and H respectively) on the Changes Page and I need to keep the original data from Column N on the sales sheet.

Thanks
Tom

[millz]

I don't know how you tested but it still works, except in 'Change' worksheet now shows From 1Fri to 1Fri, since the "From" part is Vlookedup from Sales worksheet.

There are no information shown on the Change tab to indicate which "show" or "season" that line is changing for. So I believe yes, you need an additional column to show that. I only compared the ticket "number" with the Sales worksheet.

[TomRet]

Yes, I mentioned above in the 'Edit' that I failed in my test. The code does work now that I figured out what I was doing wrong. This worksheet was a product of someone else and I am trying to work a few kinks out of it thus the 'change' page is not set up the way the sales page is (I did not consider the need for the changes when natefarm was helping me). I am attaching a new workbook with the change page set up a bit differently in hopes that it may make a bit more sense. People never change their entire season from on day to another, its just a provision set up in case they have purchased a ticket for a certain day (in advance) then realize nearer to the show date that they will be unable to attend. That is why it must change only one day.
If I am still not making sense please let me know.
Thanks
Tom

[TomRet]

Bump?

Thanks
Tom

[TomRet]

BumP! Help needed!!

Cheers
Tom!

[millz]

Instead of simply "Bump"ing your thread, you could have tried to explain further on what you're trying to achieve, or at least try something yourself. There are reasons why some people get help almost instantaneously while some don't, even after an eternity. Your problem is considered complicated if people need to guess what outcome you wish to achieve.

Here are a few problems I found out:
-You mentioned you want to change for the third show, so what is an indicator of the third show? P1 = third show?

-The ticket number in the Change worksheet is 400003, which is actually a ticket for "ALL" shows as in Sales worksheet. How should it look like after the change is done? These are all the little details you should have provided.

-After change is done, based on Change worksheet, you mentioned you want the changes to remain, so should there be a Status column or something indicating the change is made? Otherwise it would keep repeating every time you run the macro? Is that how it should work? Otherwise how should it be working? If you don't have a very clear view of your own process, no one else would.


^ the last reason was why I removed the data from Change worksheet once changes are done.

[TomRet]

I did try to be as concise as possible, I apologize that it was not clear enough...I am not certain how exactly to word things because while it may be clear to me to other (who interpret things differently) it may not. I posted a new workbook in post #15 wherein I changed the 'Change' page to look more like the 'Sales' Page. I will attach another new one with some note in it that will, hopefully, explain things better.

-You mentioned you want to change for the third show, so what is an indicator of the third show? P1 = third show?

P3 is the third show (I noticed that I placed the number in the wrong 'category' in the last upload. Again I apologize.

-The ticket number in the Change worksheet is 400003, which is actually a ticket for "ALL" shows as in Sales worksheet. How should it look like after the change is done?

I uploaded a new worksheet with everything done manually so you can see it. there are four groups of 1Fri, 1Sat, 1Sun...in the tabs, each group colored differently in the color that matches the fill in the Sales sheets. Red is P1 (or First show) Yellow is P2 (second Show) Green is P3 (Third Show) and Blue is P4 (fourth show). What I am trying to accomplish is to change ticket 400003 to tab P3 1Fri only (the green group) while everything else remains the same.

-After change is done, based on Change worksheet, you mentioned you want the changes to remain, so should there be a Status column or something indicating the change is made? Otherwise it would keep repeating every time you run the macro? Is that how it should work? Otherwise how should it be working? If you don't have a very clear view of your own process, no one else would.

A status column would not be a bad idea just to show that the change has been made, I never thought if that.
Is it bad to keep repeating each time the macro is run? I think that the 'Get Sales Data' macro just repeats each time. (that in mind I will need to run this many times over the course of a year so I am not sure what the best way is)

I do not have a clear view on this process...that is why I am here.

Thanks so much for your help, it is appreciated. If I can make it any clearer please do just ask, it is difficult for me to make myself clear when I am not even sure the best way to do this.

Regards
Tom

[millz]

I don't have much available time (for several days) to really look into this, so if someone can jump in to help please do so.

[TomRet]

Hey man, I completely understand a lack of time. thank you so much for your help thus far!

Tom

[millz]

Managed to squeeze some time to take a look. Seems to have something wrong with attachment manager, I can't really attach the file. Try adding using these code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Note that it does not do anything to "cancellations" at the moment. I removed it. You did not give any details on that as well, so I have no idea how you want a cancellation be displayed.

[TomRet]

Should I replace all he previous code or add this somewhere?

[millz]

Add the line highlighted in red to GetSalesData and replace FindChangesCancellations.

[TomRet]

Oh my gosh!!! This is absolutely perfect! I'll need to run a few more tests on it to make sure but the initial run worked like a dream. You are so awesome!

As for the 'cancelations' I may just leave that out. I am new to the whole process but I have never heard of anyone actually cancelling their ticket. People that have not been able to use them have typically just given them to friends.

So far so good, thanks a million
Tom

[TomRet]

Ok, on a second run I did not input any 'changes' it brings up an error (Run-time error '1004': No cells were found.) I assume that is because there were no changes? I looked over the code but I am not sure how to modify it (if it is possible) where it only runs if there ARE changes to be made?

Regards
Tom

[millz]

Hmm my bad, did not test that.

Please Login or Register  to view this content.

[TomRet]

Awesome. Working well again. I'll run a few more tests in the clear light of day. I wish I could add more rep to you for this but alas you were the last to receive from me.

thanks again
Tom

[TomRet]

Third test was an attempt to 'change' multiple tickets. each time I try more than one ticket it fails and I get a 'subscript out of range error. Here is where the error takes place.

Please Login or Register  to view this content.

Any thoughts?

Thanks
Tom

[millz]

Hmm, I should really test more scenarios.

Please Login or Register  to view this content.

[TomRet]

After a large series of tests I think that this is good. Well done, and thank you. Also, I wanted to say (even though I noticed it was in there earlier) that I love the message box you put in. It will allow me to look for names as people call to change their reservations. as far as testing...even I didn't know what I wanted because I am new at this box office thing...I hope I do not find any other provisions that I need for this.

Thank you for your patience and your help, it has been fun.

Regards,
Tom