getting data from one work book sheet to another

You don't appear to have responded to the help you received here: https://www.excelforum.com/excel-gen...ate-range.html

Administrative Note:

Please update your forum profile with the version of Office that you are using.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

Nothing here is preventing responses - maybe you need to elaborate on what you require help with.

YOu have not yet updated your profile as requested.

As there is no sample data on the Leads sheet of the Startup Loan Tracker workbook with which to illustrate a proposed solution, I'll provide a link to a tutorial that may help: https://www.ablebits.com/office-addi...heet-workbook/
Scroll down to the section heading: How to reference another workbook in Excel
Scroll a bit further down to the subheading: External reference to a closed workbook
Note that in the tutorial the function being used is SUM, however based on the description in post #1 I would propose using INDEX and AGGREGATE. If you are not familiar with that combination of functions, I suggest adding sample data to the Leads sheet.
Let us know if you have any questions.

To populate the Client's Name and Client's email address columns, select cell A2 and paste the following into the formula bar:

Formula:

Please Login or Register  to view this content.

Drag the fill handle down and across to column B. Of course some of the cells in column AC of the leads sheet (Startup Loan Tracker testdata.xlsx workbook) will need to contain "Loan Submitted for this to work.
To populate the Business Name column use:

Formula:

Please Login or Register  to view this content.

I don't see a corresponding Loan Application Outcome column on the Leads sheet.
Note that the formula in the Loan Outcome Date column will display the current date regardless of the date of the loan outcome.
Let us know if you have any questions.