Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Search, find & display [Solved/Closed]

Report
-
 learningenquirer -
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 :)

4 replies

Answer 1 / 4
Posts
1886
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
February 26, 2021
 141
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
Answer 2 / 4
Posts
1
Registration date
Thursday November 17, 2016
Status
Member
Last seen
November 17, 2016
(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. :)
Answer 3 / 4
Posts
1886
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
February 26, 2021
 141
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.
Report

thank you so much, it worked!!!! i forget this is not simply an addition formula
Answer 4 / 4

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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!