Ask a question Report Reply to this topic

Search, find & display [Solved/Closed]

noob - Nov 15, 2016 at 11:00 PM - Latest reply: learningenquirer

- Nov 29, 2016 at 05:07 PM

Excel Ask a question Report Reply to this topic

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

5 replies

Report Respond to Mazzaropi

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Nov 16, 2016 at 11:13 AM

Helpful

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

Report Respond to learningenquirer

learningenquirer 1 Posts Thursday November 17, 2016Registration date November 17, 2016 Last seen - Nov 17, 2016 at 03:00 AM

Helpful

(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. :)

Report Respond to Mazzaropi

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Nov 17, 2016 at 08:33 AM

Helpful

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

learningenquirer - Nov 29, 2016 at 05:07 PM

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

Report Respond to x

x - Nov 17, 2016 at 10:43 AM

Helpful

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