Dynamic table arrays

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets

As stated

I need a formula that results in =MATCH(1,Tabulator!AR:AR,0)
When = a specified "Value" in a cell like A1 & = A2, which determines the start and finish of a Dynamic array row range.

Or a formula that allows me to search for the second/third occurrence of (1) in a column.

If this is not possible that it fine. It just means that the Excel program language is too simple for basic programing. With the raw amount of effort it takes to apply a simple loop in Excel it would not surprise me if that is the case.

I can not publish our INV and what I am requesting should be fairly clear. I can not be the first person to ask how to define an array range with a variable value. There are an infinite number of applications where such a basic formula would be needed. There has to be a simple way to accomplish this in Excel. I've tried offsets, indexing, concatenate, indirect and many more. I need to match (1) in AR on another sheet that start from the row indicated by the value in A1, not A1 itself.

This is the best I have been able to come up with and it fails even though the formals look like they should work.
=MATCH(1,INDIRECT("'"&"Tabulator!"&CONCATENATE("AR",A1,":AR",A2)),0)

Again and please, any help is appreciated.

Try:

Please Login or Register  to view this content.

maybe...hard to test without a sample sheet.


Also, if not.....

Please Login or Register  to view this content.

should at least tell you where it is trying to look. Once you get that looking right, insert it into your INDIRECT.