Auto insert rows into worksheet from table in another worksheet

[excelnewbie2332]

Hello,

I have the attached spreadsheet. The Log worksheet is our 'working' worksheet and it pulls data by selecting the (+) button then (in) or (out) arrow - folder is selected and any files or emails within the folder selected, will auto insert into the LOG worksheet.

I would like specific data to pull from the LOG worksheet and auto-insert into the 'Transmittal Letter' worksheet. The column headers with the data I am looking to derive, are Document Number, File Name, Rev, Document Type, Status and Date In/Out

I have tried pivot tables, which are great for summaries but with my lack of pivot knowledge it fell flat, I tried a simple formula (=+) <-- but that does not work since it is a table the data is coming from, and auto shifts down the formulas and then the links break.

I tried making the Transmittal letter into a table as well but that did not work. So I have tapped my well of knowledge and am kindly asking for any and all help if possible.

Ok... I cannot attach any files apparently. Every file type I try to attach I'm being told is not supported - if you can also tell me how to fix this...

Thank you in advance!

[#2]

@excelnewbie2332 

If you cannot attach files here, upload them to a cloud service such as OneDrive (comes with a Microsoft account), Google Drive (comes with a Google/Gmail account) or Dropbox.

Obtain a link to the uploaded file and paste it into a reply.

[#3]

@Hans Vogelaar 

Here is the link -  thanks so much

https://1drv.ms/x/s!Att_U8QEycN7gzFYNDKLYbDmAvTr?e=BWMNk7 

[#4]

@excelnewbie2332 

Thanks. Can you tell us how you want to select a record (row) from the LOG sheet from which you want to retrieve data to the Transmittal Letter sheet?

[#5]

Hi Hans,

I would like the data to insert automatically, no trigger other than the rows being created in the LOG worksheet. Worst case, if that cannot happen, can we have a button that 'loads' the data?

[#6]

@excelnewbie2332 

> I would like the data to insert automatically

Do you mean that you want one transmittal letter for each row on the LOG sheet?

[#7]

I apologize. No, I would like all the lines that are created on the LOG, to automatically be on one transmittal letter.

[#8]

@excelnewbie2332 

It's not clear to me what a transmittal letter with more than one record from LOG should look like. Could you provide an example?

[#9]

Yes, please see the following link: https://1drv.ms/x/s!Att_U8QEycN7gzJevyq7vpzMwcTk?e=Qo853f

Also, I should clarify - we use this LOG as a tool, it is not meant to be for continuous record keeping and is not saved after each use, it's just the 'passthrough' for pasting date from and into our CRM and to create the transmittal letter.

[#10 · Accepted solution]

@excelnewbie2332 

Thanks. If you have Excel 2021 or 365, enter the following formula in C13:

=INDEX(Trans,SEQUENCE(ROWS(Trans)),{3,4,6,7,8,9})

Excel will do the rest for you.

If you have an older version, select C13:H13, enter the following formula and confirm it with Ctrl+Shift+Enter, then fill down as far as you want:

=IFERROR(INDEX(Trans,ROWS($C$13:$C13),{3,4,6,7,8,9}),"")

I have included both in the attached version, in two different sheets.

[#11]

Thank you thank you thank you!! You have no idea how thankful I am for you, your help and your solution.
That worked PERFECTLY.

You are the best, Hans