Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copying data from worksheet to another in a random numerical order

0

Hello, Can You please help. I am totally stuck in what formulae or function I need to use to complete the task. Don you have seen this exercise before and were a great help in spotting the deliberate mistake. I have now been asked to extend the Workbook and add an "Application received" page to it. I have created a Working Spreadsheet to run our Seniors Opens on at our Golf Club. Orginally the entry form took the lead page of whatever Open competition the entry was for i.e. Open 1, Open 2 or Open 3. Now I would like to include an "Application received" form. This form will have exactly the same data as the original Open 1 entry form etc. However I want this form to be in numerical order but the data on the form to be input against a tee and time slot which will not necessarily be in numerical with all fields across the form to be completed automatically. Meaning that I could receive an application form say number 1 but the players don't want to Tee off until 11am for example, conversly applicant 100 may want to tee off at 8 am, and applicant 3 may want 8.30am. Therefore I need the form to look not only at the available time but against that time to input the application number and the applicants full details. On the Application form currently the Tee and Times columns are static but I would imagine that I would have to put Controls on the rows in those columns to correctly insert the requested Tee times., in order that the Application form will remain in Numerical order. I have attached a copy of the Workbook. Only 1 Application form is currently there as I want to get that right before adding the other . Any help would be appreciated. Thanks    

excel
MS Office Version: Office 2019
posted by: Jim W (rep: 14) on Sun Nov 26, 2017 at 10:54 am
Answer
Discuss

Discussion

Let me get this straight, you want people to be able to send you application forms where they include the time that they want to tee-off and then you want a way to automatically put the times from that form into the worksheet that compiles everything and lists everyone's tee-time?
don (rep: 1989) Nov 27, '17 at 6:25 am
Correct, obviously the information from the Application form would be manually input onto the Application entry page, and then automatically copied to the correct tee time on the Open Entry Form which is linked to the Startsheet. 
Jim W (rep: 14) Nov 27, '17 at 10:00 am
I'm thinking that you can solve the issue with some lookup formulas, but, you need to fill-in the spreadsheet with some sample data and explicitly state how all the data is related in order for me to get a better idea of what is supposed to happen with the inidividual pieces of the data. At the moment, it seems like the only trouble you are having is getting the time slots to fill-in automatically from the Application entry page, which isn't a big issue; I just don't want to spend my time working on the wrong thing lol.
don (rep: 1989) Nov 27, '17 at 11:27 am
I have done as requested and placed sample data onto the Spreadsheet and copied it to the Open Entry Sheet at the correct times. Application forms only ask when does a player wish to play normally giving a time zone not a specific tee slot. Therefore the application entry sheet could have the times thereon as in the Open Entry Sheet, but the Application list is then not in numerical order or a Control to put the data into the correct time slot as specific times are rarely if ever matched, due to the 8 minute intervals between the tee times. But yes you are right, if I receive an application form requesting to play within a specific time zone I will allocate the tee time, and by transferring the data from the written application form to the program I would like it to automatically complete the second page i.e. Open Entry Form, this form will eventually be a hidden form but currently has all the cell links associated with it to complete the other forms within the worksheets.
Jim W (rep: 14) Nov 28, '17 at 9:01 am
Add to Discussion

Answers

0
Selected Answer

See if this is what you are looking for: (file attached).

On the Application Entry tab, make sure everything in Column D is entered as Text.

On the Open_1 tab, I added a column, Column D, so that it would match up with the format on the Application Entry tab. You could remove the need for this extra column if you change the times on the Application Entry tab to actual times instead of decimals.

In cells E10, F10, and G10 I used a lookup formula that pulls in the data from the Application Entry tab. The formula looks like this:

=INDEX('Application Entry'!B10:B111,MATCH(Open_1!D10,'Application Entry'!D10:D111,0))

It's like vlookup but much better.

The only thing to change in the formula to get the rest of the data is B10:B111 - change the column references to the column that has the data you want to retrun on the Application Entry tab.

I'm still not 100% sure if this is what you are looking for but check it out and see how far it gets you.

posted by: don (rep: 1989) on Wed Nov 29, 2017 at 5:37 am
Discuss

Discussion

Hello Don. Now I am confused I dont know or cannot figure out what you mean. I have tried copying your formulae to the sheet Open_1 and nothing happens. 
Jim W (rep: 14) Nov 29, '17 at 2:54 pm
Did you download my sample file and look at it? The formula is already on sheet Open_1 in cells E10, F10, and G10.
don (rep: 1989) Nov 30, '17 at 5:42 am
Sorry Don, Yes certainly have and printed it out as well. I have had another go at what you suggest, it makes sense but I keep getting N/A errors. I have had a look at your website with regard creating a form and starting from fresh for the application forms, but I canot see how i would get the application form into numerical order with the entries going into the correct time time slots. (you may have gathered that I am not very clued up on the intricasies of Excel)
Jim W (rep: 14) Nov 30, '17 at 6:11 am
Hello Don. More time spent on it, Still cannot get it to work as is seems to look at the same data on the second row within the same time slot as it does on the first row. Putting the formulae across the worksheet does get the required result. But how to prune it to look at the next line on the application form I dont know, but thanks for your efforts, appreciated. Jim
Jim W (rep: 14) Dec 1, '17 at 3:58 am
Sorry for the late reply, I'm sometimes caught up with work. The formula that I gave you is a rather complex one to a novice user of Excel. It is not really in the format that allows it to be copied correctly as I just wanted to show you one that you could build-on.

You have to change the column letter here B10:B111 to the column the contains the data you want to return from the Application Entry page.

To be able to copy the formula down, it would need to look like this:

=INDEX('Application Entry'!B$10:B$111,MATCH(Open_1!D10,'Application Entry'!D$10:D$111,0))

You still cannot copy the formula to the right - to do that, you have to manually copy/paste it for each piece of data that you want and change the column reference as mentioned above.
don (rep: 1989) Dec 1, '17 at 11:30 am
Ok Don , I most certainly will give that ago, as I have said your help is really appreciated. 
Jim W (rep: 14) Dec 2, '17 at 8:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login