Summary sheet formula help needed for newb

[Davo888]

Good Morning,
Probably a very basic question, but I am very unskilled at excel, and am not familiar with the correct terminology, so cannot find my solution with the search posts.

we have a basic quote sheet with columns, quantity, product code, product description and price.
what we require is to select the quantity in a cell, then copy this row to a new sheet in the same workbook.
This will be a summary of the rows selected in the previous sheet.

could someone steer me in the right direction about the type of formula that would allow this function.

Thank you in advance

cheers enjoy your day.

 

[Johnny Thunder]

It sounds more like a VBA type of question that your asking than a formula.

You would definitely need to elaborate further on this to get a better response.

 

[Davo888]

Thank you Johnny,

Sorry probbaly why i am struggling to get an answer.

for example
row 1 has the column headings A - quantity B- Product C- Product Code D Price
there is a list of product populated through this sheet.

what i would like to do is if i populate the quantity cell A2 for example with the number required ie 2, then in a new sheet it adds this row complete Columns A through D.
So the new sheet would still have the same column headings, but populate them as a summary of those rows selected from the first sheet.

i only want to bring across the rows that have a value added in them.

thank you for your reply.

cheers

 

[MartyS]

For clarity, I'm going to call the first sheet Source_Sheet and the second sheet Target_Sheet.

If someone enters a value into a cell in Column A: [Quantity] in the Source_Sheet, you want that specific row of data in the Source_Sheet appended to the end of the Target_Sheet.


Q1: Once that has happened.. what do you want to happen to the row in the Source_Sheet?

Does this row remain in the Source_Sheet?

Is the [Quantity] to be cleared?

Is the [Quantity] to remain as entered?


Q2: If you want [Quantity] in the Source_Sheet to remain as entered, is that value ever altered at a later date?

Q3: If so, what is supposed to happen then?

 

[Davo888]

Thanks Marty for your questions and assistance.

Q1 - The Data in the Source sheet can remain as is, with the value still showing. yes this Row does remain in the source sheet, a copy of would be sent to the target sheet
Quantity does not have to be cleared, it can remain as entered
Q2 quantity can be altered at a later date, change would be reflected in the Target Sheet when altered
Q3 Target Sheet would be updated.


essentially the target sheet would be a summary of those Rows selected from the source sheet. source sheet might have 100 rows of data, and only 20 would have values added in the quantity column, these 20 rows would be what i need visible on the target sheet

Thank you for your help

cheers

 

[MartyS]

Thank you for the additional information.

Before going to formulae or vba for a solution - can I ask what the purpose of this second sheet is?

The reason I ask is, Excel has some very simple built-in functionality that will allow you to use the one sheet.. and it can function as both.

To explain:

If you add FILTERS to the data, you can then FILTER the data in the Source_Sheet, by column A (Quantity) and set the filter to "values that are greater than 1" (or NOT BLANKS)..

That will show the list, but with all the zero/blank entries hidden (until you go back to FILTER=ALL)...

This way, all of your editing AND "summary" data is the same data, and you don't need any formulae, vba, etc, to do that task.


In case you're not familiar with filters:

Highlight all the headings.
Go to the DATA menu
Click on FILTER.

Arrows will appear in the cells of the headings.

Click on the first "down" arrow

You'll see a list of ALL (unique) values. Scroll down that list to the last item where it will most likely say BLANKS.

UNCHECK Blanks.


Alternatively, if you have no blanks, and instead have zero values.. UNCHECK the zero value.



To get back to the full list - click on the down arrow and choose the menu option that says "Clear filter from....(heading name)"

 

[Davo888]

Thanks Marty,

Second sheet is just going to be a summary of those rows selected that i can add into another document as a table or a simple copy paste option.
in effect if i put a 1 in the quantity column in source sheet it would instantly appear in target sheet.. so it takes less clicks to achieve the desired result. if that makes sense.

i just want to maintain the original Source sheet and have the Target as the summary... that way i can easily modify the source at later stage which will update the target as well.
i don't think it is that complex, but i lack the right excel terminology to search the easiest way to achieve what i need.

thanks again for your Help Davo