Bring referenced Cell Values to Top of Worksheet

[PatCell]

ATTACHMENT: Order Form.xlsx

Okay, this will definitely be a question for the best amongst you. Whoever can figure this out, I will be forever in your debt.

The situation is this. I am creating an Order form for my job in Excel. On this form, it displays the Top 100 items for sale. All the basic functionality of the form is working fine, my real issue is with the order summary tab.

On this tab, I would like for any Items with a Quantity on the first Sheet to transfer to a summary on the Second sheet.

So far, I am able to get the data onto the second spreadsheet. My final question is, can someone help me make the items on the summary sheet go to the top available row? Basically if I have items in row 3, row 7 and row 9 - I would like for the items to automatically fill in rows 3,4,5 respectively. (on the second worksheet)

I have seen people make this happen (on this board even) but for some reason, when I try using those formulas I do not get the results I need. I have attached the file to this post.

On the Tab entitled "Adv Piano" this the product list. On that tab, customers can put a Quantity in column G for the items they want to order and that item's details, price, and quantity will automatically appear on the 2nd tab, "Order Details" in columns I - L in the corresponding row (this is already done)

I need the information in the columns I - L on the "Order Details" sheet to appear in columns C - G on the same sheet, in the same order but to fill in towards the top with the corresponding quantities, item details and price remaining in the same row. Can anyone help with this?

If you have any questions, let me know and I will get back with you promptly. Thanks!!Order Form.xlsx

[Tony Valko]

I didn't download your file but it sounds like you want something like this:

http://www.excelforum.com/tips-and-t...ml#post3376007

[RudiS]

Hi Pat,

Try this solution.
I removed the formulas in the Order Details sheet and set up a macro to populate the sheet instead.

[PatCell]

Hi Pat,

Try this solution.
I removed the formulas in the Order Details sheet and set up a macro to populate the sheet instead.

Rudi,

You are truly a Guru. You got so much closer to solving this then I ever could have. The only problem I am having is that when I enter a Quantity, everything is appearing as it should on the Order Form (thanks again!) however, when I remove or change the quantities, the items on the order form are not updating. So if I put a quantity in and remove it, that item is staying on the order form. Is this an easy fix to have the Quantities update when changed?

Even so, like I said Rudi you are awesome. Thanks for your help so far.

[RudiS]

Thanks for the accolades LOL

I'll have to look into that and resolve that for you.
I never even focused on the issue of updating an existing quantity, or deleting the quantity. I think I had too much on my plate at the time.

[RudiS]

Hi Pat,

Here is the reworked file.
Caveat: Please note that if you delete a quantity, then it must be one at a time. If you select a range of quantities to delete, ONLY the first one will delete.

[ChemistB]

Here is a formula solution.
It uses Arrayed Formulas which must be entered with CNTRL SHFT ENTER instead of ENTER.
In I4 copied down

Please Login or Register  to view this content.

The others are similar.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
This can be done without the array but would require a key column. If you'd prefer that, let us know.

[PatCell]

Here is a formula solution.
It uses Arrayed Formulas which must be entered with CNTRL SHFT ENTER instead of ENTER.
In I4 copied down

Please Login or Register  to view this content.

The others are similar.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
This can be done without the array but would require a key column. If you'd prefer that, let us know.

Order Form Conflict.xlsx

Chemist,

Wow, this is basically what I needed. Here is one final caviat to the original request: if I wanted to add another Worksheet with different items but have all the items with quanitites appear in the same order form- how would I adjust the formula to continue searching for quantities across other worksheets? I tried to use the IFERROR function and repeat the formula changing the Workbook names but for some reason, if I put a Qty on the other workbook but in the same Row, only the first value will show up in the order form (not subsequent worksheets with a Qty) Once again I cannot thank you enough, that formula is 99% of what I needed. I have attached another WorkBook to illustrate what I was talking about with the multiple worksheets. I edited the Formula for the first 2 rows of the
Order Details worksheet. Both the Adv Piano and Concert Band tabs have values in the first two rows, yet on the Order Detail sheet, only the values from the Adv Piano worksheet are showing with my Formula edits. Order Form Conflict.xlsx let me know if you need any other details. Thanks!

[ChemistB]

It's easy with 2 or 3 worksheets. How many are we talking about?

[PatCell]

There is a total of 16.. Does that make this painful for you?

[ChemistB]

You could conceivably do 15 nested IFERRORs as you started to do but it's probably easier (and less memory restrictive) to create a combined sheet (I called it "Complete") and just reference your formula to that. That sheet is just pulling data from the other 16 sheets so unless you modify your range on those sheets, it will stay constant (i.e. rows 2 to 101 reference Adv Piano, 102 to 201 reference Concert Bands, etc). So once you create it, you can hide it and forget about it (unless you change your ranges in the other sheets).

VBA is also very good with handling multiple sheets so, if this isn't what you want, maybe a variation on Rudi's solution.