Excel 2007 : Take data from 2 sheets and have it enter automatically

[srgtennis]

I am using this formula and works great:

=IF(ISBLANK('[Apple Worksheet.xlsx]Apple Worksheet'!B3),"",'[Apple Worksheet.xlsx]Apple Worksheet'!B3)

So it is taking info from apple worksheet/ apple worksheet page cell b3 and put it in this cell on a different page.

I need to have it take from apple Worksheet: apple worksheet sheet AND Apple Worksheet: Juice sheet. Can I even have it do that since it automatically takes information when I put it into the other sheets.

What if there is information in apple worksheet sheet cell b3 and also in the Juice sheet cell b3. Could I have it enter it below on the line.

[srgtennis]

Anyone have any idea, Thanks.

[NBVC]

What is it you are inputting on the summary sheet? Give an example of what you would type in and what you expect as a result.

[srgtennis]

3/2/12 104331 Shafer Lake Fruit Farm Red Dels 72 MI

that is the info that it takes from another sheet when it is enter. It transfer what we type in on another sheet to this sheet. I can do it with only one sheet but there is a juice sheet and a processing sheet. So when I say for processing take the info that is in cell b3, c3, etc. and transfer it, I cant have the info on the juice sheet in cell b3, c3, etc. transfer to the same cells. So if info is in the same cells on different sheet (juice and Processing) how can it transfer from both sheet to the same cell on the sheet that I want it to.

[srgtennis]

3/2/12 104331 Shafer Lake Fruit Farm Red Dels 72 MI

that is 6 cells that is transfered to the other sheet

Someone enters in that data and then it is transfer automatically to the other sheet but I want it to do both sheet but I run into the problem when it has info in the same cells.

[NBVC]

So in the sample you attached (and the inputs that are in the Apple and Juice sheets), what do you want to see on the 3rd sheet?

You have to make it easy for us to understand what you want in order to get a quick successful result.

[srgtennis]

sorry

Third sheet
Date Variety
8-Feb jons
16-Feb empire

I want to take the info from the first 2 sheets and move to the 3rd automatically. 2/8 jons and 2/16 empires are in the same cells.

I was using this formula that works good for transfering only 1 page.

=IF(ISBLANK('[Apple Worksheet.xlsx]Apple Worksheet'!B3),"",'[Apple Worksheet.xlsx]Apple Worksheet'!B3)

This formula cant transfer both pages from the same cells. like the 2/8 jons and 2/16 empires can't both get moved to the the third sheet automatically because they would go the the same cell on third page.
When they enter in the data on the first 2 sheets then it goes to the 3rd sheet automatically.
does that help otherwise I can try to load another workbook to explain better maybe. Let me know.
Thanks for helping out.

[srgtennis]

here is another example same thing except added formula in cell.

[NBVC]

The easiest way I can see without resorting to VBA macros, and since you have data in 2 workbooks by the looks of your formula in the 1st post.... is to bring the data into the summary sheet in a separate area with a basic formula.

Example, I put in N2 this formula:

=IF('Apple Worksheet'!A2="","",'Apple Worksheet'!A2)

and copied it down to row 30 (assuming I will never have more than 30 rows in the apple worksheet.

then in Row 31, add formula:

=IF(Juice!A2="","",Juice!A2)

copied down another 30 rows.

The formulas then are copied to next column to get the varieties.

Now that you have data in this worksheet... in A2 use formula:

=IFERROR(SMALL(IF($N$2:$N$60<>"",$N$2:$N$60),ROWS($A$2:$A2)),"")

confirmed with CTRL+SHIFT+ENTER and copied down 60 rows to account for if all 30 rows on each sheet get filled.

Then in B2:

=IFERROR(INDEX($O$2:$O$60,SMALL(IF($N$2:$N$60=A2,ROW($N$2:$N$60)-ROW($N$2)+1),COUNTIF(A$2:A2,A2))),"")


confirmed with CTRL+SHIFT+ENTER and copied down 60 rows also.

You can change number of rows to copy down if you want...

[srgtennis]

Just to understand I am bringing over the information to the 3rd sheet n2 thru n5000 for apple worksheet and then n5001 to n10000 for juice worksheet and then in in the actual box that I want that info in I copy the other formula and it will sort and bring n2 thru n10000 into the cells that I want in order?

Is that correct.

That data(juice and apple worksheet) has to be on the 3rd page for it to work?

thanks, your a genius

[NBVC]

Yes that is correct, you will need to adjust the ranges in the A2 and B2 cells to suit the 10000 row range though... and make sure to confirm each formula with CTRL+SHIFT+ENTER not just ENTER before you copy down... you will see { } brackets come around the formula if successful.

Also note that the more rows you include, and the more rows you copy these types of formulas down, the more slow your computer gets at processing the results...

These are array formulas.

[srgtennis]

I noticed that it is starting to slow down because of the formulas

[NBVC]

Yes, I mentioned that above.. with that many rows and that many formulas copied down and the fact that you are getting data from other workbooks, will all contribute to slow downs. If possible reduce the 5000 rows down.... I can't think of another way that doesn't involve array formulas, unless someone can provide a VBA solution....

[srgtennis]

Sorry, I did the first part to get the info to that page but the formulas are differents that I enter in A2 and b2. I actually have 6 columns that I need to enter in date, receiver, Grower, variety, boxes, state. which formula do I enter in for the other 4 columns? Do I enter in the one that is in a2 or b2?

Nevermind Figured it out

[NBVC]

You would just change the absolute/relative referencing in the B2 formula.

e.g.

=IFERROR(INDEX(O$2:O$60,SMALL(IF($N$2:$N$60=$A2,ROW($N$2:$N$60)-ROW($N$2)+1),COUNTIF($A$2:$A2,$A2))),"")

confirmed with CTRL+SHIFT+ENTER and copied down and across the rest of the columns...

when you copy across the O$2:O$60 changes to P$2:P$60, and so on...

This will cause more slowness.