Help!!!!!

OHIYO

New Member
Joined
Aug 8, 2009
Messages
30
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,627
Office Version
2019
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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.
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,627
Office Version
2019
Platform
Windows
Out of curiosity Peter, do you have an alternative method for this task?
 

OHIYO

New Member
Joined
Aug 8, 2009
Messages
30

ADVERTISEMENT

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

New Member
Joined
Aug 8, 2009
Messages
30
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Aug 8, 2009
Messages
30
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,135
Messages
5,509,373
Members
408,730
Latest member
Kayslover

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top