Return amount from cell above the next date

**mia12** · 10-31-2019, 12:38 PM

I have bank info from pdf that when pasted to excel only pastes on a single column.
I can get the date and memo easily by filter but the amount for that particular transaction is in different cell, which is one cell above the next date/transaction.

How to easily do that? thank you

**protonLeah** · 10-31-2019, 02:11 PM

Were the $ signs in the pdf or did you add/custom format them for the sample file?

**mia12** · 10-31-2019, 02:16 PM

There were $ in the pdf but after pasting (either as text or values) in the excel, they are formatted as currencies.

**JeteMc** · 11-02-2019, 01:28 PM

Perhaps the following will help.
1. B2:B35 are populated using: =IFERROR(IF(AND(VALUE(LEFT(A2,8))>43500,VALUE(LEFT(A2,8))<10^7),A2,""),"")
2. C2:C35 are populated using: =IF(B2="","",IF(ISTEXT(B2),INDEX(A$2:A$35,AGGREGATE(15,6,(ROW(A$2:A$35)-ROW(A$1))/(ISNUMBER(A$2:A$35))/(A$2:A$35<10^8),SUMPRODUCT(--(B$2:B2<>""))))))
3. E2:F35 are populated using: =IFERROR(INDEX(B$2:B$35,AGGREGATE(15,6,(ROW($B$2:$B$35)-ROW($B$1))/($B$2:$B$35<>""),ROWS($A$1:$A1))),"")
Let us know if you have any questions.

**mia12** · 11-05-2019, 02:05 PM

Hi! Thank you for taking time to answer. I'm just wondering how the formula works in some rows but not in others after clicking enable editing. The formula is still there and no editing done yet but the other info needed just don't appear. Please see attached screenshot.

**mia12** · 11-05-2019, 02:48 PM

It's no problem really, I'll just filter the date in column A, use cell reference in column B, then the formula in column C for the amounts which I really needed works perfectly.

**JeteMc** · 11-05-2019, 03:47 PM

You might try using this modified formula in column B: =IFERROR(IF(AND(VALUE(TRIM(LEFT(A2,8)))>43500,VALUE(TRIM(LEFT(A2,8)))<10^7),A2,""),"")
Only other suggestion that I have would be to upload a copy of the spreadsheet on the right of the screenshot.
Let us know if you have any questions.

**mia12** · 11-12-2019, 03:46 PM

The modified formula for column B actually works. No more #N/a in my column C. Thanks

**JeteMc** · 11-12-2019, 04:06 PM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Return amount from cell above the next date

LinkBack

Thread Tools

Rate This Thread

Display

Return amount from cell above the next date

Re: Return amount from cell above the next date

Re: Return amount from cell above the next date

Re: Return amount from cell above the next date

Re: Return amount from cell above the next date

Re: Return amount from cell above the next date

Re: Return amount from cell above the next date

Re: Return amount from cell above the next date

Re: Return amount from cell above the next date

Thread Information

Users Browsing this Thread

Similar Threads

Return date against validating if the bill amount equals to payment and return a date

[SOLVED] Return amount values based on date and name criteria

[SOLVED] Index Match-return an amount if a date falls between two dates

[SOLVED] Loop through tabs and return date and amount when amount is present

[SOLVED] Count amount of desired numbers in a single cell and return that value in another cell

trying to return a year to date amount

index row, return dollar amount adding amount together

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions