Create an Excel file
$10-30 USD
Paid on delivery
Create an excel file following the structure of supplied file to produce calculated values in the dated area (Q1 to CNxx).
Code Logic: if headerdate=(startdate+activitydays),insertvalue of sum(quant/activityrate),"null")
Long-Hand Description:
IF the headerdate [as known from the first cell of this column] EQUALS the startdate [as known from the cell in the same row in the fifth column] PLUS the activitydays [as known from the associated croptype range determined from selectable named dropdown list from same row in the second column [croptype range on same sheet] THEN insert the value of the sum of the Quantity [as known from cell in the same row in the fourth column] DIVIVED BY the activityrate [as known from the associated croptype range determined from selectable named dropdown list from same row in the second column [different row as the acivitydays] OTHERWISE leave cell blank.
sample hard-coded values in row 8 and 9.
Column F to P hidden in normal use.
Unless a better method can be achieved easier
UPDATE Jan 20 12:30 AM UTC/GMT (or Jan 19 7:30 PM Eastern Time)
New File (sample-to-share2.xlsx), and adjusted info and clarification . Similar basic need.
Brief Description:
Using the file provided. create code so that the Hours Required sheet can display correct information based on selections and input made on that sheet. There are 5 other sheets for data and calculation support.
This file has 3 fields of calculated sample data in the main date display area to help facilitate the explanation. Hopefully it is accurate to the explanation below. Various other fields have sample or limited code.
To be built with dynamic relationships so that when we can …
change the date sequence [in F1 through CS1]: no code is negatively affected
change the cell contents in Crop, Quantity, StartDate columns:no code is negatively affected
add rows to HoursRequired sheet: no code is negatively affected
add columns and rows to supporting sheet tables
Code Logic as we understand it: if headerdate=(startdate+activitydays),insertvalue of sum(quant/activityrate),"""")
Explained long hand:
IF the headerdate [as known from the first cell of current column] EQUALS the startdate [as known from the cell that is in the same row but in the 'StartDate' column] PLUS the activitydays [as known from the associated croptype activityday determined from selectable named dropdown list from same row in the 'Crop' column [CropType range from CropType sheet, ActivityDay range from ActivityDay sheet]] THEN insert the value of the sum of the Quantity [as known from the cell in the same row in the Quantity column] DIVIVED BY the associate crop activityrate [determined from selectable named dropdown list from same row in the 'Crop' column [CropType range from CropType sheet, ActivityRate range from ActivityRate sheet] OTHERWISE leave cell blank."
Populate the cells that contain content in the calculated F2 to CS201 range (Hours Required sheet)
Total All Actvity hrs (currently line 202, sum of current dated colum),
Total Number of WorkDays required (currently line 203, sum of previous row of same column divided by WorkDayLength [fromWorkDayLength sheet])
Total the Sunday to Saturday WorkDays values to the approriate WorkWeekDay cell location (dynamic, as F1 to CS1 can be redated)
Total Per day Activity hrs
Note: Some existing Conditional Formatting:
for dynamic relationship displaying Sundays in Dated Columns
for alternating row shading
for dynamic relationship displaying Saturday in WeekWorkDays
for cells that contain content in the calculated F2 to CS201 range
for cells (currently in row203) that exceed a value of 1530
A Line Chart showing each of the 11 per day labour hours over time (11 lines over 3 months)
Project ID: #7037737
About the project
14 freelancers are bidding on average $37 for this job
Hello sir, I am PREFERRED freelancer in excel and related skills on this forum with 200+ five star reviews. I have understood your requirement and I am ready to start the implementation immediately. Looking forward to More
hey there - please share the excel file that you are using now...we like to have a look at it before we start..please reply...thank you...
Hello I am an excel expert and would like to bid on the project posted by you. I have completed more than 100 projects in excel. FYI: Freelancers are not authorized to send any attachments to the employer before More
Hello, my name is Cristian, I have a degree in Business and work with excel every day. I have much experience with spreadsheets, formulas and macros. Check my reviews. They speak for themselves. Best regards
I can do this. See my profile and reviews, contact me if you're interested. Thanks_________________________________________________________________
Dear, Regarding my qualifications, I hold a Master's degree in Electrical Power Systems and a Bachelor's degree in Engineering (Electrical) and I've been using Matlab/Excel/VBA for 5+ years. Regarding The project: P More
I have very extensive knowledge and experience (16 years) with Excel and VBA coding. The project that you describe is very simple and I believe that I've bid accordingly. I hope to have the opportunity to work with you More