This formula A16=IFERROR(INDEX(INDEX($A$4:$H$17,,MATCH(A$29,$A$3:$H$3,0)),SMALL(IF(MONTH($A$4:$A$17)=MONTH($D$25),IF($C$4:$C$17<>"",ROW(A$4:A$17)-ROW(A$4)+1)),ROWS($A$4:A4))),"")
suddenly stopped working in the whole worksheet. I tried everything including turning on auto calculate but nothing changed.
Please I'm asking for help, I'm very stressed.
Regards,
Sunboy
This is because when you put this formula in A16, you get a circular reference, as your Index is pointing to A17..H17
Cheers
Erwin
I started learning VBA because I was lazy...
Still developing.... being more lazy...
Please find the attached sample and see why is used A16. A16 is a drop down list for months.
Regards,
Sunboy
Well to be frank, I don't see any dropdown list, but that doesn't matter.
I assume you want to apply this formula in the two tables below the ledger.
I don't see the need for the double index, and the cells that are referred to, are empty (like D25). Above all, I hope you do know that this is an arrayfunction to be entered als Control-Shift-Enter?
Here's the function again, a little simplified, for the VAT-number column:
where D$29 is the header-name as in your table, mind you, they must be the same!
Please Login or Register to view this content.
So you can copy this formula to the other columns as long as the headers are the same.
Cheers
Erwin
Last edited by Eastw00d; 07-29-2020 at 02:03 AM. Reason: Adjusted Formula
Still struggling to make work. Please the attached sample.
Regards,
Sunboy
Hi find the attached sheet,
I can see only that in the sheet Sales Journal the formula's in columns I and J are not correct. It must be:
CheersPlease Login or Register to view this content.
Erwin
It does not report row 6 of 'sales journal' because the Vat number is empty.
Note: having noticed you posted 2 different workbooks with potential different questions in I doubt my input is relevant.
Last edited by Andy Pope; 07-29-2020 at 09:10 AM.
It is VAR report so it only report those that have VAT number.
Okay, and the VAT statement workbook appears to be working, so what does 'suddenly stopped working in the whole worksheet' mean exactly?
I can give only one "improvement", in order that you don't have to input as CSE (Array-formula), but as a normal formula:
CheersPlease Login or Register to view this content.
Erwin
HI Erwin
I'm trying your formula but confused by Aggregate 15,6. Which is this range?
Thank you.
it is not a range: AGGREGATE is a multifunctional function, where 15 is the function number, stands for SMALL and 6 omits all the errors within the results of the function.
Cheers
Erwin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks