Hi
I have a workbook with a few sheets. I need the cells on sheet 1 to be auto populated from sheet 2. I need the data to search for each date (Row 1, sheet 1) on sheet 2. Then search for each course (Column A, Sheet 1) within the specified date range on sheet 2.
Thanks
"Then search for each course (Column A, Sheet 1) within the specified date range on sheet 2. "
and when you found it what do you want to do?
e.g. TR002803 for 23 January
- Battle without fear gives no glory - Just try
I need it to populate the associated cell in Sheet 1 with the total count of each course for that day. There may be multiple instances of the course on the same day.
It means for our example
e.g. TR002803 for 23 January
The result is 1 ?
I was wrong because dates don't match.
Can you give an example of the result you want
That would be correct. If the particular course was taught twice it would return 2 etc.
See file attached.
Take care that it needs an empty cell at the end of the date data set to make the limit between 2 dates.
Last edited by PCI; 08-26-2014 at 02:43 AM.
Thanks
This looks like it will work. I can't seem to get it to run in my other spreadsheet though. I am sure that it is something simple, but I can't figure it out.
Did you care to have an empty cell to shown the end of the data.
Just to explain how the function is working:
=SearchCourses(Course,Date,Range)
It looks in the Range for the Date and when found it, scrolls down until to find an empty cell counting the number of times Course exists
Else send a new file with the issue
Yes, I inserted the blank cells. The issue that I am having is executing the code. I copied the code to my spreadsheet, but am not sure how to get it to run. With previous uses of code, I inserted it into a userform that activated it. How do I get it to run to populate?
Thanks
It's a User Define Function.
Put the function code in a module
And then in sheet1 where there is the date and the courses reference put
=SearchCourses($A2,V$1,Sheet2!$A$1:$E$19)
A2 = course reference
V1 = date
Sheet2!$A$1:$E$19 = range where is the data
Afterwards if you prefer to have a macro launched by a button, a key tell it.
Please Login or Register to view this content.
I still can't get this to work. I opened the attached sheet called courses. I tried to add another course to sheet 2 and when I go back to sheet 1 all of the cells are 0. They will not update. Thoughts?
Can you send the file with the issue?
Or using the file attached explain step by step what you did.
I enabled macros and added a new course to Jan 21. When I go back to sheet 1. It is all zeros.
Thanks
Courses_rev.xlsm
Curiously formulas in sheet1 need to "refreshed".
If you copy one cell (e.g. M2) and paste it in the complete range it updates ....!
I have no clue why.
As for containment (temporary solution) see file attached.
That didn't work either. As soon as I enabled macros all data on sheet 1 reset to zero.
What's up if you copy one cell and paste it in the full range: Copy the formula in all others cells
Please check again with file attached
I copied a2 to all of the cells. Cell P2 contains =SearchCourses($A2,P$1,Sheet2!$A$1:$E$19)
I beleive that is correct, isn't it?
Same issue with Rev3.
Do the copy paste gave some results?
Yes formula is correct
I forgot to mention for test files sent, that you have to go in an another sheet that "sheet1" and come back to "sheet1"
Last edited by PCI; 11-12-2014 at 05:07 PM.
Same results.
I rebuild completely the file with my Excel, can you check.
It works the same way. As soon as enable content, it all goes to 0. I copied and pasted and went to sheet 2 back to sheet 1 and still 0.
Do you the chance to test it on another computer (another Excel).
Tried it on a different machine. Different version of Excel. Same results. All I have to do to make it populate is go to sheet 2 and back to sheet 1 and it should populate, correct?
It should.
In the last file sent to you (see attached), there is a macro launched when on access to sheet1 and this macro is refreshing one by one each cells formula.
It is a bit stupid, heavy and not smart but on my PC it works.
This evening I will see fora macro to do the complete job.
See attached a complete remake, a macro is used to update all cells in sheet1.
This macro is fired when accessing to sheet1 but can be launched manually.
Attached an update of the UDF turned in Volatile mode.
Of course I did not ask you to check that your Excel is in Automatic calculation mode ..!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks