Move a row of data based on a word

Hello,

I am new to the forum and I would be grateful for any kind of help as this is driving me crazy!!

I have attached my blank document for reference.

My mum currently buys, restores, and resells furniture for a profit and at the moment she doesn't have anything electronic so I thought excel would be great.

I have 3 tabs. The first one is 'Current Stock' which will outline what stock she has and then on the side I have made a formula to calculate how much the stock is in the value of money.

The second one is 'Sold Stock'. This is where sold items will go. I have made another formula to the side to add up all of the profits made and also minus off expenses which is tab 3.


I have been trying for ages to find a code to allow the data for an item to move from 'Current Stock' to 'Sold Stock' when the word 'Sold' is input into the 'Confirm' column. Can anyone help as I have tried many VBA codes and nothing has worked!!
I am more than happy to answer any questions for clarification

Personally, I think the Sold Stock sheet is redundant. You might as well leave it where it is. It's either sold or it's not, or maybe it could be reserved. I would allocate a stock code/ID for each piece of furniture and use that to record expenses. Then you can use SUMIF/SUMIFS to calculate the total cost of refurbishment for an item.

You can use AutoFilter to show which items are still in stock, or those that have been sold.

If you don't know VBA, I’d suggest you avoid it.

Thank you for your response.
I'm not to sure how to use SumIf but i will certainly look into it!

Please see the updated sample workbook. I have used two sheets: Current Stock and Expenses. Each contains one Structured Table. If you type a new entry immediately below the table, the table will be extended to include it. Formulae and formatting will automatically be copied into the new row.

Structured Tables allow you to create "more readable" formula as they reference the column headings. For example:

Formula:

Please Login or Register  to view this content.

I have added a unique ID so that you can differentiate between the different furniture items ... as time goes on, you will have more than one desk, chair or mirror. This allows you to link specific expenses with specific furniture items. I guess you might want to apportion the cost of bulk items like sandpaper, paint, varnish, screws, etc.

I have also adjusted the formatting. I think you are mixing up presentation with content ... in my opinion, less is more. It will make it easier should you wish to print the sheets.

You'll see that the two sheets are linked together. The expenses for an item are a total of the relevant amounts on the Expenses sheet.

Formula:

Please Login or Register  to view this content.

And the Description on the Expenses sheet uses a VLOOKUP:

Formula:

Please Login or Register  to view this content.

Anyway, some ideas to be going on with. I'm sure it will evolve over time.

Nicely done.

Nicely done.

Why, thank you

I can't thank you enough, this is fantastic!!

Thank you so much. It's really neat and works perfectly. I love the table automatically generating

Many thanks,
Luke!

You're welcome.


Note that you can Sort and/or Filter items in a Structured Table. That would allow you to compare similar items.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.