auto-fill "payment" tab from the "master" tab debt list

[jltjr75]

I have a sheet with two tabs: master and payment
If the "master" tab has a pay date from the 1st - 14th, I want the information auto-filled under the "payment, 1st Payments" row (columns A thru C auto-filled)
If I delete or change the payment amount or pay date it from the "master" tab I would want it to change or delete accordingly from the "payment" tab
If the "master" tab has a pay date from the 15th - 31st, I want the information auto-filled under the "payment, 15th Payments" row (columns A thru C auto-filled)
same here... If I delete or change the payment amount or pay date on the "master" tab I would want it to change or delete accordingly from the "payment" tab
I pasted a few examples on the payment tab to show what I want to accomplish. My "master" tab frequently changes so just copying would not be sufficient.
What would be the best way to accomplish this? I appreciate the support.

 

[moddddmyexcel]

Try this, my works not pretty but it should work.

=FILTER(master!A:C,(IFERROR((VALUE(LEFT((master!A:A),LEN((master!A:A))-2))),0)>=1)*(IFERROR(VALUE(LEFT((master!A:A),LEN((master!A:A))-2)),0)<=14))

Put this in cell A2 on your payment sheet.

=FILTER(master!A:C,(IFERROR((VALUE(LEFT((master!A:A),LEN((master!A:A))-2))),0)>=15)*(IFERROR(VALUE(LEFT((master!A:A),LEN((master!A:A))-2)),0)<=31))

Put this in cell A16 on your payment sheet.

If you get a spill error there is not enough room below for the formula to populate, add empty rows in this case. Or move your 15th payments table to the right of your 1st payments table so they do not overlap.

 

[jltjr75]

Try this, my works not pretty but it should work.

=FILTER(master!A:C,(IFERROR((VALUE(LEFT((master!A:A),LEN((master!A:A))-2))),0)>=1)*(IFERROR(VALUE(LEFT((master!A:A),LEN((master!A:A))-2)),0)<=14))

Put this in cell A2 on your payment sheet.

=FILTER(master!A:C,(IFERROR((VALUE(LEFT((master!A:A),LEN((master!A:A))-2))),0)>=15)*(IFERROR(VALUE(LEFT((master!A:A),LEN((master!A:A))-2)),0)<=31))

Put this in cell A16 on your payment sheet.

If you get a spill error there is not enough room below for the formula to populate, add empty rows in this case. Or move your 15th payments table to the right of your 1st payments table so they do not overlap.

Thank you, I'll try this later today. Right now, It looks like Excel is not accepting the "FILTER" so I will have to figure that out. I have no merged or hidden cells so that's not the issue.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jltjr75]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you, Fluff. I updated it. I am using Microsoft Office Home and Student 2019 version

 

[jltjr75]

Try this, my works not pretty but it should work.

=FILTER(master!A:C,(IFERROR((VALUE(LEFT((master!A:A),LEN((master!A:A))-2))),0)>=1)*(IFERROR(VALUE(LEFT((master!A:A),LEN((master!A:A))-2)),0)<=14))

Put this in cell A2 on your payment sheet.

=FILTER(master!A:C,(IFERROR((VALUE(LEFT((master!A:A),LEN((master!A:A))-2))),0)>=15)*(IFERROR(VALUE(LEFT((master!A:A),LEN((master!A:A))-2)),0)<=31))

Put this in cell A16 on your payment sheet.

If you get a spill error there is not enough room below for the formula to populate, add empty rows in this case. Or move your 15th payments table to the right of your 1st payments table so they do not overlap.

I have Office 2019. The Filter command is not an option.

 

[jltjr75]

@moddddmyexcel , I installed the 2021 version. It works beautifully. Thank you very much!

 

[Peter_SSs]

I installed the 2021 version.

Does that mean you should update your Account details - click your user name at the top right of the forum? (Don’t forget to scroll down & ‘Save’)

 

[moddddmyexcel]

@moddddmyexcel , I installed the 2021 version. It works beautifully. Thank you very much!

Sorry I was unavailable yesterday, glad this worked out!

 

[Peter_SSs]

Does that mean you should update your Account details

Thanks for updating.