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

Storing data retrieved from a date sensitive file

0

We have a workbook that we use for our work schedule.  As we change the date, the workers that are scheduled for that day are automatically entered into the  "Work Tech" cells in the "Schedule" sheet of that workbook.  I need to extract data from that workbook by the each date of the month, add up the names of the floor techs that worked that day, (an array), then multilply that number by 8 and save that data so we can look at a months worth of data (column "C" of attached file).  I combined all data needed into the attached workbook so  that it would be easier for me to try and manipulate.  Cell  "A1" is the date that will change daily.  Column "B" is all the days in October, and cell C1- C31 is the data for each day we need to save.  I used the following formula in column "C"  =IF(B1=$A$1,COUNTA($A$2:$A$9)*8,"").   My problem is that when you type in the next days date in "A1  the information in cell "C1" of the attached file disappears because it does not meet the criteria of the formula.

How can I create a formula that will keep the data from all the dates in a month?

If I am looking at this the wrong way, I am open for other ideas.  I thought the Counta was going to be the hard part but it turns out saving the individual days is what got me.

excel
MS Office Version: 2016
posted by: Odie (rep: 2) on Tue Oct 17, 2017 at 3:49 pm
Answer
Discuss

Discussion

There seems to be a logical problem, perhaps in the test you have set up. If you wish to know how many hours were worked on each day you need a list of days (your column B) and you need a variable list of names (extracted from "Work Tech" cells in "Schedule"). There is an example of this list in A3:A9 but using this list should give the same result for all days. The point is that this list is variable and, therefore, can't be on the same sheet as the report if the report is to cover all days.
Note also that you don't need a date in A1. As I understand you, it's the date in B1 which is relevant to your list.
Variatus (rep: 4889) Oct 17, '17 at 9:32 pm
Thank you for the response.  The "Work Tech" sheet of the "Schedule" workbook works just fine, and yes the names change daily, so setting up the array is should be satisfactory.  I realize that putting it on the same sheet will always keep that array the same.  However, my problem is once you change the date in A1 from 10/1/2017 to 10/2/2017 (which in our case is the date from the "Work Tech" sheet of the "Schedule" workbook, the data  disappears in cell C1 because the result relays false (the formula asks are you equal to 10/1/2017) and the data appears in C2 because that result  is true but disapears in C1 (the next day we are asking are you equal to 10/2/2017 and at that point in time the anwer is true (but it is no longer true to equal 10/1/2017).  I want to be able to ask on 10/1/2017, how many are working, multiply by 8 and store that data in a cell labeled 10/1/2017 (C1).   Then on 10/2/2017 ask, how many are woking, multiply by eight and store that data in a cell labeled 10/2/2017 (C2) and so on. 
I hope that makes sense.
Odie (rep: 2) Oct 18, '17 at 7:20 pm
It is possible to ask how many people were working on the day specified in A1. It isn't possible to determine how many people were working on all days of the month on the day specified in A1. For one day there is only one answer. Or, inverted, there can't be 31 answers to the question how many people were working on any one day.
The design of your worksheet doesn't support production of a list like the one you describe. The dates in A1 and column B contradict each other.
Variatus (rep: 4889) Oct 19, '17 at 4:58 am
Ok, hopefully this will make everything clear as to what I am trying to do.  I have attached a workbook called "Test Schedule".  Please open and go to the "Daily Sign Sheets" tab and go down to rows 41 through 46.  These are the floor techs that will be on duty for the date in row 4.  Please change the date in row 4 and notice that the names in rows 41 through 46 change.  I put the cells we originally had on tab "Sheet 1" (L1-L31 - M1 & N1 & O1.  Again what happens is that everytime you change the date in row 4 of the "Sign Sheets" tab, only the DATA for the formula for that date (I.E. 10/27/2017) stays in cell M27 or Sheet1.  If you change the date to 10/28/2017, the DATA in M27 disappears and it fills DATA in cell M28 of "Sheet1". Cell O1 is suppose to sum all of M1 though M31 to give me the months grand total.  I need all 30 days of info to total in cell O1 of "Sheet1"

My logic is flawed because the if statement is only true on the date in row 4 of "Sign Sheets" tab. 

I hope this makes sense, I don't know how to explain it any better.
Odie (rep: 2) Oct 26, '17 at 5:12 pm
It's starting to dawn. Obviously, the A4 value is of no use to you. I would like to have a look at what's in 'Daily Sign Sheet'!B42:B46 but can't due to workbook protection. Can you share the password?
Basically, you will need to recreate the effect of results in B42:B46 using the dates in Sheet1!L:L and use those virtual results to populate Sheet1!M:M
Variatus (rep: 4889) Oct 26, '17 at 11:28 pm
Unfortunately I do not have the password to the workbook, that is why I am doing the formulas on the "Sheet1" tab, Columns "L" through "O".  But the "Floor Tech" names are brought over from the tab "6 Week Schedule" corresponding to the date that you type into row four (Put in 10/27/2017 in row four) of "Daily Sign Sheets" tab.  In the "6 Week Schedule" tab, if you go to the column that is the date (10/27/2017) you will see either an "X" which annotates that they are off that day, or a blank which means they will work in the position listed in Column "B" or you will see a number whch means we are reassigning them to another area.  For example if you look at I5 cell (which is 10/27/2017) on the "6 Week Schedule" tab you will see in row 35 that Amanda would normally work in ED 4 but we put in the number 125 it reassigns her to ED 2, Cell A33, which when you go to "Daily Sign Sheets" tab it puts her into ED 2.  Never the less, the sheet works as it does and as you put the next consecutive date into "Daily Sign Sheets" tab row four, whoever is working that day is put into the schedule.  I just need to take 30 consecutive days and add up the total number of "Floor Techs" that have worked * 8 hours and give me the 30 day total of hours all techs have worked.  How can we do that?
Odie (rep: 2) Oct 30, '17 at 3:17 pm
Add to Discussion

Answers

0
Selected Answer

I doubt that this is the most elegant solution possible, but it works (I think and hope). Paste this in your Sheet1!M1 and copy down.

=(IFERROR(SUMPRODUCT(('6 Week Schedule'!$A$6:$A$72={133,134,135,136})*(INDEX(INDIRECT(ADDRESS(6, 4,1,1,"6 Week Schedule") & ":$AS$72"), , MATCH($L1,'6 Week Schedule'!$D$5:$AS$5, 0))="") ),0)+IFERROR(SUMPRODUCT((INDEX(INDIRECT(ADDRESS(6, 4,1,1,"6 Week Schedule") & ":$AS$72"), , MATCH($L1,'6 Week Schedule'!$D$5:$AS$5, 0))={133,134,135,136})*1 ),0)) * 8

Basically, the formula has two parts which are very similar to each other. The first part counts blanks in the date column of the '6 Week Schedule' in the rows identified by the job number in column A. The second one counts the number of times the job number itself is mentioned in the date column. The two numbers are added to each other and then multiplied by 8.

The term {133,134,135,136} is a virtual range. You might expand it to include 137 and 138 or wish to replace it with something like 

'6 Week Schedule'!$A:41:$A$46
. It's a question of how you want to maintain the formula. My personal preference would be to do neither of the above. I would name a range as "Techs" and use that name in the formula. The "Techs" range might be '6 Week Schedule'!$A$41:$A$46 or it might be a range you especially set up for this purpose, perhaps on a hidden sheet. The advantage of a named range is that you don't have to modify any monster formulas like the one above. You just modify the address of the named range whenever change is required.

Note that the term ADDRESS(6, 4 identifies the cell in the 6th row, 4th column, meaning $D$6. The entire range specified is $D$6:$AS$72. There would be huge advantage in replacing this range with a named one. "INDEX(INDIRECT(ADDRESS(6, 4,1,1,"6 Week Schedule") & ":$AS$72")", appearing twice in the formula above, could be replaced with a single descriptive name like "Schedule", and each time you need to change AS72 to some other cell you just amend the address of "Schedule" and never touch the formulas.

Anyway, it's your baby now and I wish you the best of luck with it.

posted by: Variatus (rep: 4889) on Tue Oct 31, 2017 at 6:54 am
Discuss

Discussion

Thank you, thought the problem was simple in the beginning but apparently not.  I appreciate your hard work and dillegence.
Odie (rep: 2) Oct 31, '17 at 3:53 pm
Add to Discussion
0

Namaste!

Sorry, it is not an answer to your question. In fact, I also want it. But we must accept the suggestion from Variatus:  "this list is variable and, therefore, can't be on the same sheet as the report if the report is to cover all days".

Here is just an alternative with the simple ideas you already know- vlookup and so on. Your purpose could be different.

In sheet2, you can type a number in the shaded cell for a short summary.

Edited: Workbook lets you to enter new names whereas Workbook1 is more like an attendance register for 14 people.

posted by: Chhabi Acharya (rep: 111) on Wed Oct 18, 2017 at 4:10 am
Discuss
0

Change your Orientation (careful)!  I changed your orientation to going vertically, which may make your task easier?  See attached.  Then if you need the data Vertically, you can do a range Transpose on the Dates & Hours.  (Otherwise in your original format, you would need 2 tabs:  1 to do your usual calculation then a macro to copy it as Range Value to correct date on 2nd tab...more work than needed and you still wouldn't keep track of the workers.  Good luck

posted by: queue (rep: 467) on Wed Oct 18, 2017 at 1:03 pm
Discuss


Answer the Question

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