VLOOKUP, WEEKNUM & combining text into a weekly report

[mandii01]

Hi All,

The worksheet that I am referencing to is my "datasheet"

I have the following formula which is working and returning information from a particular row for a particular date, however I am wanting to lookup a range based on the week num (7 days) that I have in column A in my datasheet rather than a singular date. However everytime I try to reference the week num to lookup I get an error. Currently the columns for date in my workbook look like as follows;
WeekNum Date
12 18/03/2011
12 19/03/2011
12 20/03/2011
13 21/03/2011
13 22/03/2011
13 23/03/2011
13 24/03/2011
13 25/03/2011
13 26/03/2011
13 27/03/2011
14 28/03/2011

My function so far looks like this;

=IF(VLOOKUP(E4,DATASHEET!C2:AU501,42,FALSE)=0,"NIL",VLOOKUP(E4
,DATASHEET!C2:AU501,42,FALSE))

Where E4 = todays date
DataSheet is the worksheet where I am drawing information from.
Columns from C2:AU501 is the data area that I am referencing
42 is the column number that the information is being drawn from

This formula works a dream for plucking out the text that I want it to and put it in a daily report however I would like it to look at the weeknum and pluck out the information for a weekly report.

Can someone please help me

Thanks
M​

 

[Airfix9]

Do you want to sum the data for the weekly report? I am guessing it is numeric?

If so, use SUMIF

Assuming the weeknumber is in column A of "DATASHEET" and A1 of your report contains the weeknumber you are looking for:

=SUMIF(DATASHEET!A:A,A1,DATASHEET!AR:AR)

Edit to suit.

 

[mandii01]

Hi there,

Its actually text that I'm wanting to collate into a report.

The way the spreadsheet is set up (I would put on an example but I'm unable to upload attachments onto this thread).

Column A
Week Num

Column B
Date (dd/mm/yyyy)

Column AR
Text Cells

Each date has a corresponding cell in column AR which is where daily events have happened ie. INC-001 FAI - Cut Finger, INC-002 Non Conformance - Disregard of signage in place

What I need to do is combine all the cells in column AR that fall within a particular week num into one overall report.

 

[Airfix9]

OK, some questions before I can proceed.

Is there only ever a maximum of one line per day?

Is there always an entry per day?

 

[mandii01]

There may be multiple lines within a singular cell and some days may not have any entry at all

Cheers