Create an Excel file

Completed Posted Jan 26, 2015 Paid on delivery
Completed 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)

Data Mining Data Processing Excel

Project ID: #7037737

About the project

14 proposals Remote project Active Jan 31, 2015

Awarded to:

macroPOL

Hello, I think I could complete this project. I could upload a YouTube video tommorow where you could see if it is done properly. I have a question whether the cells are valid values: Q9 (1) AD8 (null) AL8(em More

$30 USD in 3 days
(6 Reviews)
3.0

14 freelancers are bidding on average $37 for this job

Teloquence

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

$50 USD in 0 days
(261 Reviews)
7.2
zExcel

A proposal has not yet been provided

$48 USD in 1 day
(141 Reviews)
6.8
expertatwork

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...

$45 USD in 1 day
(86 Reviews)
5.3
rohitpithisaria

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

$30 USD in 1 day
(37 Reviews)
4.8
P0L

A proposal has not yet been provided

$25 USD in 1 day
(27 Reviews)
4.7
elqueabandona

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

$50 USD in 3 days
(25 Reviews)
4.6
tomislav1975

I can do this. See my profile and reviews, contact me if you're interested. Thanks_________________________________________________________________

$45 USD in 1 day
(22 Reviews)
4.6
teeares

I have completed many tasks in Excel. I can sure do this one also. However, some seems amiss in you long hand description. First the number of opening square brackets [ is not equal to the closing square brackets ]. I More

$25 USD in 1 day
(23 Reviews)
4.4
boriscikotic

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

$50 USD in 1 day
(1 Review)
1.9
mezzanayne

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

$20 USD in 1 day
(0 Reviews)
0.0
boglet

A proposal has not yet been provided

$55 USD in 2 days
(0 Reviews)
0.0