Copying Data from sheet to sheet base on one cell value.

Hello. Thank you for taking the time to read this message.

I have little experience with excel but have become good at figuring it out. I'm currently at a loss. I'm sure this situation has been covered on the forum before but I'm unable to locate a solution.

The overview of what I'm wanting is a excel file to generate receipts when items are removed from an inventory for each day that they are removed.

In my attached file, I would like to have data from certain cells on sheet 'Collections Spreadsheet' transfered to the sheet 'MTRtemplate'. This data should correspond to the values in the related cells. On the 'MTRtemplate' under my "material description" heading I have columns for Client Code Collection, Box No. Item Code, Media ID/Catalog No.,Format, and Title. I would like these cells to be automatically filled based on the value in column F "Status" on sheet 'Collections Spreadsheet'. If the value in the cell is "Temporary" or "Permanent", then the data from the columns Client Code Collection, Box No. Item Code, Media ID/Catalog No.,Format, and Title (or A, B, C, D, O, and P) should be entered on the sheet 'MTRtemplate' in columns in columns A-F and rows 21 and beyond. When the status is changed I would for the date to be enter into column G "Date Removed. Finally, the last condition of this is the on my "MTRtemplate" I would like to only take the values that meet the "temporary" and "permanent" requirement if the "Date Removed" and "Transaction Date" on the 'MTRtemplate" sheet match.

If possible, there are a couple other things I'd like to do with it additionally. I'd like it to sort based on it's status (i.e. temporary then permanent).

Sorry if this is unclear. I sounds clear in my head when I read it. If you need more clarification, please let me know.

Thank you for any help you can provide!

Is this what you're looking for? I put it on a pivot table, and added a filter. You can select the items on want on the filter and hide the filter row. You hit refresh all each time you have new data and it should populate. Good luck.

I use an auto-ranking sheet for my company's bonus programs that's similar to your needs.

I added:

Column G beginning with G2 and pasted downwards:
=IF(OR(F2="Temporary",F2="Permanent"),DATEVALUE(TEXT(NOW(),"mm/dd/yyyy")),"")

This enters a date when the Status changes, cuts off the timestamp, and reformats to a standard mm/dd/yyyy so it can be compared to the Transaction date.

I used two helper columns which I inserted as H and I.

Column H beginning with H2 and pasted downwards:
=IF(G2=MTRtemplate!$E$10,VLOOKUP(F2,'Authority Lists'!$I$2:$J$4,2,0),"")

This checks if the Date Removed = Transaction Date, and then adds a 2 or 3 based on if Status is Temporary or Permanent. (I defined this as a vlookup for values I stored on your Authority List.)

Column I beginning with I2 and pasted downwards:
=RANK(H2,$H$2:$H$25000,0)+COUNTIF($H$2:H2,H2)-1

This assigns a rank to each Status Value and avoid duplicate ranks such that they increment even if there are multiple Temporary's, etc.


I added a tab into the workbook that counts the total ranking values, and relists only those in their new order.
The MTRtemplate is then fed from this tab, exluding all the garbage.

Columns H and I on the Collections Spreadsheet can be hidden or copied and pasted further down the page if you need.

Feel free to play around with it. Everything on MTRtemplate auto-updates based on Collections Statuses, Date Removed, Transaction Date.

Attachment 251309

here are the temp items only.

I'll try again.

MTRtemplate-daffodil.xlsx

You just have to change the date on the Template tab.