VLOOKUP, WEEKNUM & combining text into a weekly report

mandii01

New Member
Joined
Mar 21, 2011
Messages
11
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​
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

New Member
Joined
Mar 21, 2011
Messages
11
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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

New Member
Joined
Mar 21, 2011
Messages
11
There may be multiple lines within a singular cell and some days may not have any entry at all

Cheers
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,421
Messages
5,547,824
Members
410,813
Latest member
Vhinzvirgo
Top