Automatically input data into a separate sheet when a column is filled out

[MikeSta4ord]

Hi, I'm having trouble finding out the proper function that would input the invoice date whenever the date for a PO is filled out. Since an invoice should automatically be drafted when a PO is received, I'd like a system to automatically document the numerous invoices for a certain PO.

So when the PO date column is filled out under the "PO log" sheet, I'd like for it to document that same date under "Invoice date". The remaining tasks I'm trying to fulfill can be done so using an Index-Match function, and I'm aware how to do that. Though the Invoices $ amount column is calculated by using the "initial payment terms" column and the "Project $ amount" column. So I'm also wondering how to implement that into a Index-match formula. If 60% is the initial payment terms for a $10,000 project than $6,000 should be the initial invoice amount. I'll handle the concluding invoices, I just want to know of the function that will perform this task for me.


I'm sure there is some sort of formula in excel that can do this for me, I just don't know what that would be.

I'll upload a sample file to show what I'm working with...
Thanks so much for all your great help, it's truly appreciated.

[FlameRetired]

With this formula in A2 of Sheet1

Please Login or Register  to view this content.

Adjust ranges to suit.

File is attached.

[skywriter]

Post edited.
FlameRetired responded.

[MikeSta4ord]

Thank-you! That worked out great.

There's one thing I'm stuck on though, for the Initial invoices the system you set up is fine since every Po with have a date filled out in order so all of the Invoices will be displayed. Though, for the secondary invoices...which is similar to how the initial invoices should be set up except instead of PO date having to be filled out the "Date sent" column needs to be filled out. The project might and probably will be shipped out after other POs have came in, therefore the system wouldn't recognize that we added a date to a previous row and wouldn't record the invoice. I was wondering if there's a way to set up the formula to report every date that is recorded in the "Date sent" column even if that date is entered out of order. Thanks again for your help, can't explain how grateful I am for this site and all its great contributors.

[RAF1992]

I have a project I am working on which needs some date logic. I have a column of dates formatted: 1/1/15.
I want to insert a function to the right which basically says: if DATE is before or equal to February 28th, return the date. If FALSE, then return 1/1 of the next year. Please help. Apologies if I haven't followed proper forum etiquette, I'm new.

[FDibbins]

RAF1992, please dont post duplicate posts, and dont post on other member's threads

[FlameRetired]

There's one thing I'm stuck on though...........

Though, for the secondary invoices............... the "Date sent" column needs to be filled out.

The project might and probably will be shipped out after other POs have came in, therefore........

.......I was wondering if there's a way to set up the formula to report every date that is recorded

in the "Date sent" column even if that date is entered out of order.............

In my formula(s) I guessed which columns to INDEX.

The additional questions leave us with too many things to guess.

We might make shorter work of this if you would upload another workbook that includes all of the following:

1.) Fieldnames / headers in Sheet1 that exactly match the target headers in Sheet2.
2.) In Sheet2 type in about 10 or so rows of data that are representative of the challenges you anticipate.
3.) In Sheet1 hand type in the desired results ... completely.

IE give us the clearest before and after presentation that you can. Along with that please explain in the workbook item by item .... not in general ...... but by specific item entries ... how these results qualify. Please be specific leaving nothing to speculation.

The bulk of this work is going to fall on you. If you will supply the above it will help us to help you in a timely manner.

[MikeSta4ord]

Attached is the sample file with clear headings and notes which explain my issue.

Thanks again for your help.

[FlameRetired]

Beautiful job on the reworked headers and explanation!

I did make one small change on a Shipment Invoices header. Date Shipped (Secondary Invoice) header does not exist in PO log. So I changed that to Date Shipped. Excel is particular about these details.

This is an array formula entered into A2 of Shipment Invoices and filled across and down to E20. That is already done for you in the attached. If you edit and recommit you will likely have to reformat dates/$ amounts and percentages.

If you are not familiar with array formulas they must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Please let us know if we're making progress.

[MikeSta4ord]

Thank-you! That's exactly what I'm looking for...

Once again, I'm extremely grateful for such great contributors on this site.

[FlameRetired]

You're welcome. Glad it does the job and thank you for the feedback!