Help!!!!!

[OHIYO]

SHEET "july"

Images removed - They were blowing up the page - Moderator

It was essentially:

Month | Day1 | Day2 | Day3 | -->Day30
Qty -->

SHEET "aug"


SHEET "sept"


SHEET "report"

report

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 106px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">DATE</TD><TD style="TEXT-ALIGN: center">QTY</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffcc">7/25/10</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">7/26/10</TD><TD style="TEXT-ALIGN: right">13</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">7/27/10</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">7/28/10</TD><TD style="TEXT-ALIGN: right">55</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">7/29/10</TD><TD style="TEXT-ALIGN: right">22</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">7/30/10</TD><TD style="TEXT-ALIGN: right">109</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">7/31/10</TD><TD style="TEXT-ALIGN: right">55</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">8/01/10</TD><TD style="TEXT-ALIGN: right">10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">8/02/10</TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">8/03/10</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 32px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">8/04/10</TD><TD style="TEXT-ALIGN: right">40</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">8/05/10</TD><TD style="TEXT-ALIGN: right">50</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">8/06/10</TD><TD style="TEXT-ALIGN: right">33</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">8/07/10</TD><TD style="TEXT-ALIGN: right">60</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">8/08/10</TD><TD style="TEXT-ALIGN: right">120</TD></TR></TBODY></TABLE>


I need to creat a 15 days report for production qty.
In the report sheet, I need to set a date on cell A2, then the result of qty list in column B.

Please help how to find it out?

yoyo

 

[jasonb75]

You need consistancy to make this work.

Option 1

name your month sheets using jul, aug, sep, etc, first 3 letters for each month, then in Report B2

=HLOOKUP(A2,INDIRECT(TEXT(A2,"mmm")&"!A1:AF2"),2,0)

and copy down.

Option 2

Name month tabs in full, July, August, September, etc, then in Report B2

=HLOOKUP(A2,INDIRECT(TEXT(A2,"mmmm")&"!A1:AF2"),2,0)

and copy down.

Note that there is almost no difference between the 2 formula, but using the wrong one will cause errors.

 

[Peter_SSs]

And, if you haven't got it already, on the report sheet in A3, simply
=A2+1
and copy this down to A16. Then you only have to change A2 to get a different report.

Also (though not important in a sheet only small like this), since the dates on each sheet are ordered (and assuming all dates for each month are there) you could omit the ",0" at the end of the formula and it would be a slightly faster formula.

Edit: A more descriptive title (eg 'Lookup from different sheets') will usually get you faster/better help. There are a number of the experts on this forum who would just bypass a thread with a title like this one.

 

[jasonb75]

Out of curiosity Peter, do you have an alternative method for this task?

 

[OHIYO]

Thank you both of you, it works perfect.
I think yours solution definitly help many people who posted the similar question like mine. Some of the suggest using HLOOKUPALLSHEET, THREED, I tried but unfortunately, it doesn't work out.

Again, thank you so much,

yoyo

 

[OHIYO]

I find that if one tab namely "data", then the formula doesn't work, the outcome is #ref.

or all the worksheets should be in identical name? not allowed to add sheets with other names?

may i asked how to solve this problem?

yoyo

 

[Peter_SSs]

Out of curiosity Peter, do you have an alternative method for this task?

No, I had developed an identical solution to yours (apart from the exact match issue already mentioned) but when I went to post it I noticed you had already done so.

 

[Peter_SSs]

I find that if one tab namely "data", then the formula doesn't work, the outcome is #ref.

or all the worksheets should be in identical name? not allowed to add sheets with other names?

may i asked how to solve this problem?

yoyo

Please explain more fully. Perhaps a screen shot or two again would help?

 

[Smitty]

Please explain more fully. Perhaps a screen shot or two again would help?

If you PM me your e-mail address I'll send you a copy of the board's HTML Maker, which will let you post workable screen shots.

 

[OHIYO]

Peter,

what I mean is that I add one extra tab, namely data, which I recorded the production line information. This sheet format is not the same as sheets "jul","aug" or "sep"

Because of this extra sheet "data" added in the same workbook, the formula from Jasonb75 act in cell B2 didn't work properly, instead the outcome is #REF.

I hope you understand what I mean.

yoyo