Issue with functions now reading cell address

[mjincks]

I was hoping to get some help with a complex excel equation series I'm trying to create. In summary, I'm trying to create equations where I type in a specific start & stop numbers in two cells, and then three separate cells reference a table of lookup values based on the start & stop numbers, and then returns data using the "sum", "max", and "count" functions.

I'm using the equation "=CELL("address", INDEX(A#:A#, MATCH(A#, A#:A#, #)#))" to pull the cell address of my start & stop numbers from the table of lookup values. Then I'm trying to use the "sum", "max", and "count" functions to read those address values and return details on other columns in the same table. The issue I'm having is the "sum", "max", and "count" functions are just reading the cell the address outputs are located in as text but not utilizing the actual address being pulled from the above equation for the start & stop numbers.

Another questions I had, is there a way to get the above equation to function across excel sheets in the same workbook? Currently I have the equation address equations on the same sheet as the reference table and it's working correctly, but when I try and move the address equations to a different sheet in the same workbook it just returns a "#N/A" error as the output and not the actual referenced cell address.

Now for the visuals:
I've pasted a table of values in the "Staking Table" sheet.

The "OPGW REELS" sheet is the main page for this worksheet.

My goal is to be able to type in a start and stop structure number in "B" and "C" columns of the "OPGW REELS" sheet and then have the "AE" (SUM function), "AF" (MAX function), and "AG" (COUNT) columns spit out their respective data pulled from the table in the "STAKING TABLE" sheet. I'm currently using the equation "=CELL("address", INDEX($B$3:$H$1500, MATCH(K1505, $B$3:$B$1500, 0),7))" located in the "STAKING TABLE" sheet to return the cell addresses for the start and end structures, and just a simple "=" in the "AB" and "AC" columns to pull the cell address to the "OPGW REELS" sheet.

The two issues I'm having:
1) I would like to move the above equation to the "AB", and "AC" columns in the "OPGW REELS" sheet, but when I move the equation (and adjust the sheet references) it is returning a "#N/A" error
"=CELL("address", INDEX('STAKING TABLE'!$B$7:$H$1500, MATCH('OPGW REELS'!B4, 'STAKING TABLE'!$B$7:$H$1500, 0),7))"

2) The "AE" (SUM function), "AF" (MAX function), and "AG" (COUNT) functions are not reading the cell addresses listed in the "AB" and "AC" columns as cell references, they are just reading them as text and returning a "0" output. How do I modify the cells or equations to read the cell address as an actual cell address instead of just text?
AE - "=SUM('STAKING TABLE'!AB4:AC4)"
AF - "=MAX('STAKING TABLE'!AB4:AC4)"
AG - "=COUNT('STAKING TABLE'!AB4:AC4)"

The cell references in the above equations are the locations in the "OPGW REELS" sheet.

Thanks,
Matthew

 

[severynm]

Among other things, the Indirect function can be used to return an actual range reference from text/number inputs. To use a simple example from your second screenshot,
=INDIRECT("STAKING TABLE!C"&B4+6) would return 747.651. That should help with your second issue.

For the first issue, note that Cell will return [WorkbookName]Sheetname!AbsoluteAddress if the reference is on a different sheet, so if you're not accounting for that, that's likely the cause of your #N/A error. If all you want is the cell reference as text, =Address might serve you better.

 

[RoryA]

IMO, you would be better off using just the MATCH part to find the relevant row numbers, then use INDEX functions inside your SUM and other functions.