#### mjincks

##### New Member
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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### severynm

##### Board Regular
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

##### MrExcel MVP, Moderator
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.

Replies
19
Views
481
Replies
2
Views
86
Replies
1
Views
131
Replies
3
Views
110
Replies
1
Views
518

1,141,768
Messages
5,708,416
Members
421,567
Latest member
vicpinto1970

### 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.

### Which adblocker are you using?

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

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