How can I add an INDIRECT function to an INDEX/MATCH formula?

ccarmichael

New Member
Joined
May 20, 2015
Messages
9
How can I add an INDIRECT function to an existing INDEX/MATCH formula?

The existing formula is (for one account balance):
=INDEX('FinanceDetail'!B2:B50,MATCH(TRUE,INDEX(('FinanceDetail'!B2:B50<>0),0),0))

The "FinanceDetail" tab/sheet contains dates (column A), and account balances over time in columns B, and C. Each account balance is not always updated on certain dates, so some gaps exist. The above formula works for this condition, except that new dates are added in row 2, which automatically updates the formula above (B2:B50 reference). I understand that the INDIRECT function can solve this issue, but I have ran into various syntax errors. Any help would be greatly appreciated.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
As you know, INDIRECT changes text into ranges. The text part in the INDIRECT must match the actual range address: If you want to refer to 'FinanceDetail'!B2:B50 but want to change the 50 to dynamic range, you'd use something like INDIRECT("'FinanceDetail'!B2:B"&COUNT('FinanceDetail'!B:B)).

And if you want to change the sheet name, you might want to use something like INDIRECT("
'"&A2&"'!B2:B50") where A2 holds the sheet name you want to refer to.

If the first example (= number or rows) is what you're looking for I'd forget the INDIRECT and use either (dynamic) named ranges or Excel tables instead. Both of these make the formulas easier to type / understand and (at least seem to) calculate faster than using the INDIRECT-function.

For more information about dynamic named ranges watch one of the several tutorials found on YouTube.
 
Last edited:
Upvote 0
Thank you. This seems to work well for the first account balance on my summary sheet, but when I add "INDIRECT("'FinanceDetail'!C2:C"&COUNT('FinanceDetail'!C:C))" and INDIRECT("'FinanceDetail'!D2:D"&COUNT('FinanceDetail'!D:D)) for the other two accounts, the result is showing "0". Do you know how I can work around this?
 
Upvote 0
I'm sorry but I should see the worksheet to be able to help you with the work around but from what I've understood from your description it sounds like you should reconsider the whole data layout a bit: If you had your data organized as it should (= a data table layout: Dates in one column, Customers in another and transactions in a third one) you'd get away with a single pivot table.

The only catch with pivot tables is that they don't update automatically. The easiest way to update them all at once is to click the Refresh All -button found from the Data tab.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top