Index/Match/Max/Collect

Yehuda

Hi all,

 

I am having trouble putting together a formula and I am hoping someone here can guide me in the right direction.

 

We have a sheet that we enter payments we receive from our customers. That sheet has the customer name, check date, deposit date and amount etc.

 

I am looking to create a sheet that can give me the latest deposit date as well as the amount of the payment. I was able to get the latest deposit date by creating a new sheet with a list of our customers and using the formula MAX(COLLECT({Posting Checks Deposit Date}, {Posting Checks Customer}, [Primary Column]1)) where primary column 1 is the list of customers I put in column A.

 

I am looking for a way to be able to pull the deposit date which is on a different column but the same row.

 

I was also wondering if there is a way to take the data from a drop-down list (the list of customers) and have it auto populate on this sheet with the latest deposit dates and check amounts so we don’t have to add it manually each time we get a new customer.

 

Thank you in advance!

 

Paul Newcome

Are you able to provide some screenshots with sensitive/confidential data removed/blocked/replaced with "dummy data"?

 

From what I can gather, this should be a relatively straightforward solution, but it always helps to be able to visualize exactly what we're working with.

Yehuda

Thanks for the reply!

 

I have attached 2 screenshots one from the source date and one from the sheet I am creating.

 

The source data is where we manually input the payments we receive. The column contract is our customer.

 

The new sheet is what i am trying to create. I am looking to be able to pull the dollar amount from the source data based on the latest deposit date. 

Right now the customer fields in the new sheet is manually entered is there anyway to have it automatically pull the new fields as they are entered in the source data.

 

Please let me know if you need more info.

 

 

Yehuda

Curious if anyone can help me. I am trying to get the information in a different column based on a lookup value and the latest date.

Paul Newcome

My apologies for the delay. I never got a notification for your reply on here.

 

I just want to make sure I am understanding exactly what it is that you're looking for...

 

1. Payments are entered into the sheet in the second screenshot. 

2. The first screenshot is a summary of the most recent transactions.

3. You want the sheet in the first screenshot to automatically update itself with the most recent transaction data for each Contract.

 

Is this correct?

Yehuda

Pretty much.

Yehuda

Bumping this hoping someone can assist me.

Thanks in advance!

Paul Newcome

Still not getting all of the notifications for this thread. I'll have to figure out what's going on there. I have set a reminder to come back and check this thread regularly in the mean time. Anyway...

 

Will there be a possibility of the same contract having more than one transaction per day? If so, are they entered as they are received or can they be entered out of order with manually inputted dates?

Yehuda

Thanks again - its weird I am also not getting notifications.

 

It is possible for one contract to pay twice on the same day. They would typically be entered one after another.

Paul Newcome

Ok. So let me make sure I have this correct. You are actually looking for solutions to a couple of different things.

 

1. Pulling the most recent transaction data from a master sheet based on a customer number.

 

2. A dynamic Dropdown list of your customers so that new customers are automatically added to the summary sheet once their first payment is recorded in the master sheet.

.

I will start with #2. There isn't a way to directly create an automatically updating dropdown list. However, there is a workaround. I do not have it handy (although I really should), so I will revisit this post once I am able to find it along with some additional notes specific to your use case.

.

Pulling the most recent transaction data is actually pretty straightforward, and we have a number of options for a solution.

 

Since there could be multiple entries for a customer within a day, but they should be entered in order of receipt, we can add a helper column (referred to as "Row Number") to your master sheet where the entries are made. In this column we will put in a basic formula of

 

=COUNTIFS(Contract$1:Contract@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))

 

Enter this into row 1 and then dragfill on down. This will give us each row number which we can use a MAX/COLLECT function (same as how you are pulling the date) to pull the most recently entered row number for the specific client.

.

On the summary sheet is where we run into a few different options on how to pull the data for display. I will go with the most straightforward way of automating. I see that you are familiar with cross sheet references, so I will not go into the details of creating them. Any cross sheet references will be labeled as {Sheet Name Column Name}, so for example {Master Sheet Amount} would be the column named "Amount" on your Master Sheet.

 

We will be using a series of INDEX functions to pull the data based on a new text/number column we will call "Max Row" that can be hidden from view once everything is built to help keep the sheet looking cleaner.

.

Here is the syntax for INDEX:

 

=INDEX(range_to_pull_data_from, row_number, [column_number])

range_to_pull_data_from: Where are we pulling the date from?

row_number: What row within the above range are we pulling the data from?

[column_number]: Optional. If the above range includes multiple columns, what column are we pulling the data from?

.

For the [Max Row] column, we will simply use your MAX/COLLECT of 

 

=MAX(COLLECT({Master Sheet [Row Number]}, {Master Sheet Contract}, Contract@row))

.

This will pull the highest row number for each customer from the master sheet which will be the row number for the most recent entry.

.

NOTE: This solution is based off of the assumption that new entries are added at the bottom of the sheet.

.

This will automatically update with the highest row number for that particular customer. Since we have now established the highest row number for the customer, we can now use that within our INDEX functions to specify which row to pull data from.

.

So we don't need a column number, and we have our row number. All that's left is the range to pull the data from, and our INDEX functions will be completed.

Here's how simple this part is...

I will start in the [Last Check Date] column of your summary sheet.

.

Type in

=INDEX(

create your cross sheet reference by selecting the entire column from your master sheet that you are wanting to pull from

=INDEX({Master Sheet [Check Date]}, 

tell the INDEX function which row to pull from which we have automated in our [Max row] column and then close it out since we aren't needing a column number.

=INDEX({Master Sheet [Check Date]}, [Max Row]@row)

.

All you need to do now is take that same INDEX formula and change the cross sheet reference to pull from the appropriate column, and your solution is built!

.

Let me know how all of this works out for you, and feel free to ask any questions, raise any concerns, or ask for clarification of anything at all.

.

I will also get back to you when I can in reference to the Dynamic Dropdown. It isn't so much a dropdown as a cell link to a listing that we can automate.

 

Rebecca Larkin-Martinez

@Paul Newcome you never came back with

"2. A dynamic Dropdown list of your customers so that new customers are automatically added to the summary sheet once their first payment is recorded in the master sheet. There isn't a way to directly create an automatically updating dropdown list. However, there is a workaround. I do not have it handy (although I really should), so I will revisit this post once I am able to find it along with some additional notes specific to your use case."

Paul Newcome

@Rebecca Larkin-Martinez

My apologies. There are actually a couple of different ways to do that. Can you provide some detail into your processes and setup and exactly what you want the dynamic list for as well as how you want to be able to use it?

Jack Rafalski

Hello Paul,

Would love to hear about the other solutions regarding a dynamic dropdown list if you have a moment. Would it involve two sheets and using a lookup where the range is much larger than the total entries? A brief idea of the solutions would be enough to give a hint on where to start drilling down.

Your solution above seems sound, in order to utilize it with a sheet ordered by new entries at the top I'd simply use a min function instead, correct?

@Paul Newcome

Thank you,

Jack

@Paul Newcome

Paul Newcome

@Jack Rafalski It has been a while, so my memory on the topic is a little hazy. It does look like you should be able to use the MIN function instead though.

As for the Dynamic dropdown...

@Andrée Starå developed a solution using cell linking instead of an actual dropdown (and will hopefully correct me if I get it wrong).

Basically you enter "Double Click Here To Select" or something along those lines on row 1 and then create your dynamic list starting on row 2. Then on the working sheet you would cell link to the cell in row 1 so that the visible text is the "Click Here" text. You would "pre-fill" multiple rows with this link to the "Click Here" cell.

Users can then double click on the linked cell to edit the cell link and they will be shown the listing on the dynamic sheet.