Issue with functions now reading cell address

mjincks

New Member
Joined
May 19, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
1621428342784.png


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


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
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
37,365
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
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.
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
Top