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:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,959
Office Version
  1. 365
Platform
  1. 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
49,219
Office Version
  1. 365
Platform
  1. 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
12,959
Office Version
  1. 365
Platform
  1. 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
49,219
Office Version
  1. 365
Platform
  1. 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
49,219
Office Version
  1. 365
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,582
Messages
5,659,652
Members
418,518
Latest member
chantel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top