View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Lemmesee Lemmesee is offline
external usenet poster
 
Posts: 31
Default Consolidate Lists from Multiple Sheets on another Sheet

The Item# 4 gets there by a variation of the formula like this
=IF(B9="","",MAX('Purchase Order'!A23:A35)+1).
I need them numbered in order based on the ITEM#.

And yes I can send the file to you, but where do i send it?

"Sheeloo" wrote:

It helped...

What I don't understand is how you get item# 4 in sheet 2? If you have
formula as described, both sheets will have items 1,2,3,...

I think you want to get all items from sheet 1 and sheet 2 on sheet 3
but I do not understand how you want them numbered?

Can you send the file to me?

"Lemmesee" wrote:

I knew that I could not explain it right. So let me try again.
On the first and/or second sheet, I enter a Quantity, The Item# is then
automatically generated(as described in my original post).
Then I add a description, the description of this ITEM may be One line or
multiple Lines.
Then I enter another QNTY, at least, one row below the final row that
contains the description of the previous Item. That said, there may be blank
rows between the various Items.
Now on the third sheet it will be a consolidated list, omitting the empty
rows, ordered by the ITEM#.
Does this make better sense?

"Sheeloo" wrote:

Dear Let Me See :-)

Sorry, your problem is not clear... Your example does not have same item on
both sheets. How will you treat this case?

Also how do you order the item names?

What you should do i have a helper column which gives the sequence number
within a group. Once you have that it is easy to consolidate, merger, sort
etc...

To me it looks like that you want to merge the two sheets and if the same
item is on both sheets have the item no. only once by eliminating the
quantity with only one of them....

"Lemmesee" wrote:

I have 2 sheets with Item Numbers, Quantities, And Descritpions.
The Description of the Item May be 1 row or Multiple rows.

On a third sheet, I need to consolidate the list from "sheet 1" and "Sheet
2" elimating the Item Number and Quantity if the Description is more than 1
Line.

Note:
On the First 2 Sheets I have a formula for the Item Number, similar to:
=IF(B2="","",MAX($A$2:A10)+1) , So that once a QNTY is entered in Column "B"
it gives a new Item Number. Allowing "Blank" rows and/or "Multi-Line
Descriptions".

I am not sure if you can make sense of this example below, But If you can I
would appreciate it.
Thanks in advance.


"Sheet 1"
A B C
1 Item# QNTY Description
2 1 10 Apple
3 Red
4
5 2 11 Apple
6 Green
7
8
9
10 3 1 Orange

"Sheet 2"
A B C
1 Item# QNTY Description
2 4 2 Orange
3 Rotten
4
5 5 1 Banana

€œSheet 3€
A B C
1 Item# QNTY Description
2 1 10 Apple
3 Red
4 2 11 Apple
5 Green
6 3 1 Orange
7 4 2 Orange
8 Rotten
9 5 1 Banana