search, find & display Solved/Closed

Question

Hello,

I have been manually filling out the week no's, in a budget sheet, but want to try and auto populate.  I have a table with dates and Week No's listed against as reference. 

I thought it would be easy enough.  

Find this date e.g. "sheet 2 B3",

WK # DATE
1 31/12/2015
? 4/01/2016
2 6/01/2016
2 8/01/2016
3 11/01/2016
3 12/01/2016

Search "sheet 1 B2:H6", and return week no reference from A2:A6.

Week No.        Start Date      
1 27 Dec 15 28 Dec 15 29 Dec 15 30 Dec 15 31 Dec 15 01 Jan 16 02 Jan 16
2 03 Jan 16 04 Jan 16 05 Jan 16 06 Jan 16 07 Jan 16 08 Jan 16 09 Jan 16
3 10 Jan 16 11 Jan 16 12 Jan 16 13 Jan 16 14 Jan 16 15 Jan 16 16 Jan 16
4 17 Jan 16 18 Jan 16 19 Jan 16 20 Jan 16 21 Jan 16 22 Jan 16 23 Jan 16
5 24 Jan 16 25 Jan 16 26 Jan 16 27 Jan 16 28 Jan 16 29 Jan 16 30 Jan 16

I have tried SEARCH, and MATCH functions without success.  At this rate it would be easier to manually imput.  

Surely someone on here can help me please :)

Mazzaropi · Answer

noob, Good afternoon. 
 
Supposing your example:
Sheet1 --> B2:I6
Sheet2 --> B3:C8

Try to use:

Sheet2
B3 --> =SUMPRODUCT((Sheet1!$C$2:$I$6=C3)*(Sheet1!$B$2:$B$6))

Copy it down.

Is that what you want?
I hope it helps.
 --
Belo Horizonte, Brasil.
Marcílio Lobão

learningenquirer · Answer

(Previously noob)

Thanks for that.  I don't think that is what i was after.  I don't think i explained it very well.

Second attempt.

Sheet 2 Cell B142 is the cell i am putting my formula in. I am hoping to drag down the formula in column B.

Sheet 1 is my week # reference. 

I want the formula to use the date in Sheet 2 Cell C142 (in this case 16/06/2016) to search my list in Sheet 1 and find the date (16 Jun 16 in Cell F110) and the corresponding week.  Answering the formula with the week # (25 from Cell A110).



Would love to find a way to make this happen.

Thanks in advance. :)

Mazzaropi · Answer

learningenquirer, Good morning.

"...Thanks for that. I don't think that is what i was after..." 
The formula that was suggested to you is exactly what you need.

"...I don't think i explained it very well..."  
Your need is a simple process to understand.

Since you have sent us only one image of part of your spreadsheet, I will adapt the formula by changing the intervals for what we can see.
You should adjust your formula to the reality of your data.

Sheet2 --> B142

=SUMPRODUCT((Sheet1!$B$2:$H$111=C142) * (Sheet1!$A$2:$A$111))

If you prefer, save your worksheet to some free website, www.sendspace.com and place the download link here.
It's faster and easier for us to help you.

x · Answer

It looks like the dates are consistent enough to just use 
=WEEKNUM(C142)
in the B column rather than having to use a look up table

Search, find & display

4 responses

Similar discussions

Subscribe To Our Newsletter!