• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Conditions

G

gumbles

New Member
Hi there, I am struggling to find the best structure for a formula that must meet several conitions before taking the value from one worksheet and putting it in another.


Due to IT security at work im unable to upload any samples so will do my best to explain my situation.


I am taking the No. of hours per booking code from a weekly report(which is changed weekly without saving) and storing them in a worksheet as records.


Condition 1: The number of hours must only be stored/collected if the current week on the weekly report (weeknum(now()) matches the week heading on the Booking Code Record Sheet.


Condition 2: The names of the employees must match up


Condition 3: The Booking Codes must match up. But there are two lists of Booking Codes and I was thinking some sort of OR function for this.


I will try and upload an example from home if i get a chance.I know this is not much information to go on but any ideas that you could recommend for me to try would be very helpful.
 
Hi gumbles,


I'm afraid it's as you implied, somewhat confusing and I'm having trouble understanding the exact layout. Also, are you looking for a formula or a macro to transfer data? If the weekly report is not saved, it would seem to make sense that it's a macro. In that case, having exact layout would be key. If you can share some sanitized data with layout, that would be very helpful.
 
a sample worksheet would be great or try to type it out here in the same layout/format


Col.A | Col.B | Col.C |

Date | Names | No. Hrs|
 
Will try and get a sample to you guys this evening but might be tomorrow evening.


Ideally i was trying to use formula and not macros as I am only working here for another 6 months and other team members are very uncomfortable with macros.


I was wondering if it would be possible with multiple nested IF functions or something like this. Obviously it will be clearer with an example so will work on getting that to you.
 
This is a plain version of what i have.


https://www.dropbox.com/s/61cns2cwbmzcmej/Mock%20Weekly%20report%20with%20Booking%20codes.xlsx


Hope it makes things clearer.


Gumbles.
 
in Weekly Report!C4:
Code: 
=SUMPRODUCT(('Booking Codes'!$D$3:$BB$3=$B$1)*('Booking Codes'!$B$4:$B$12=$B4)*('Booking Codes'!$C$4:$C$12=$D4)*('Booking Codes'!$D$4:$BB$12))


Copy down


To understand how this works please read: http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
 
Hi ,


I think a formula solution is difficult , since the week number in B1 will keep changing every week ; ideally , you should have a macro which you can run once , whenever the data has been entered in your Weekly Report tab ; the macro can always ensure that if any column cells in the Booking Codes tab already have data in them , a prompt is displayed , to ask whether data can be overwritten or not.


Narayan
 
If your worried about validation of the data entry you can have a count or some other check next to the answers warning that the data may be incomplete
 
@Hui,


Thanks very much for the SUMPRODUCT suggestion, Im looking at the link right now, but I need the hours from the weekly report to be logged in the booking code, as information is put into the weekly report and the booking code log should be automaticly copied. Because of this im unsure how to copy accross the No. of hours worked as there is nothing for them to equal(except the row no. which has both the name and booking code on)


@Narayan,


I know that a macro wold probably make my life alot easier, but because I am not working here on a permanent basis they are extremely reluctant to use macros. They have said they would not like to use macros because no one would be able to fix/alter them when I leave (Dark Ages I know...)


Gumbles
 
Hi Gumbles ,


The macro would not be a complicated one , since all it has to do is match the week number in B1 ( on the Weekly Report tab ) with the numbers in the range D3:BB3 ( or even BC3 / BD3 / BE3 ) to see which column should receive the data from column C on the Weekly Report tab. The problem with formulae is that the data which has been copied is through a formula , so how can the formula ensure that the copied data stays when the week number changes the next week.


Narayan
 
Hi Narayan,


Do you think you could give me a code example then? Being an excel Ninja, im sure your code would be alot neater then mine lol.


Gumbles
 
Hi,


Creating four named ranges:

Code: 
_hours: ='Booking Codes'!$D$4:INDEX('Booking Codes'!$4:$12,,COUNTA('Booking Codes'!$1:$3))`

[code]_week: ='Booking Codes'!$D$3:INDEX('Booking Codes'!$3:$3,,COUNTA('Booking Codes'!$1:$3))`

[code]Booking_Code: ='Booking Codes'!$C$4:$C$12

Team_Member: ='Booking Codes'!$B$4:$B$12[/code]


then use this formula in Column C (Weekly Report): =INDEX(_hours,MATCH(B4&D4,INDEX(Team_Member&Booking_code,),0),MATCH($B$1,_week,0))[/code]


hope this may fulfill your need and accommodate the increasing weeks.


Regards,


Ahsan
 
@ Narayan,


Thankyou, I can wait no problem.


@ Ahsan,


Thanksyou, but I think you may have misinterpreted my problem, as I need the information to be copied FROM weekly report TO booking code so the formulas would need to be in the booking code cells not in weekly report worksheet. The information is entered into cells c4:c12 so the calculations are around them.
 
Hi Gumbles ,


Can you check the file here ?


http://speedy.sh/m5Wqk/Mock-Weekly-report-with-Booking-codes.xlsm


I have defined some named ranges.


Narayan
 
Hi Narayan,


Im afraid the company firewall doesnt allow me access but I will check it this evening and report back either this evening or tomorrow.


Thanks again for your time.


Gumbles.
 
Hi Narayan,


Im not sure if I am being silly here, but I cant see any of the code you have written? Do you know why this might be?


And it looks great by the way.


Gumbles
 
Hi Gumbles ,


I downloaded the file from the link I posted here earlier , and checked in the VBE in the Modules section , in Module1 ; the code is there under the name Button1_Click.


Narayan
 
You must log in or register to reply here.
Back
Top