Accessing a cell in a named column

JenniferMurphy

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
In the following table, I need the formulas in columns H & I to refer to the data in column G but at one row down. Column E is assigned the name PP_A, column F the name PP_B, and column G the name Iterations.

I would like to replace the references to column G in the formulas with the name of that column, but if I do, I get the cell in G on the same row as the calling cell.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]PGS_A[/TD]
[TD="align: center"]PGS_B[/TD]
[TD="align: center"]PP_A[/TD]
[TD="align: center"]PP_B[/TD]
[TD="align: center"]Iterations[/TD]
[TD="align: center"]Wins_A[/TD]
[TD="align: center"]Wins_B[/TD]
[TD="align: center"]Formulas[/TD]
[TD="align: center"]Formulas[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]52.00%[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]51.35%[/TD]
[TD="align: right"]48.65%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]61,620[/TD]
[TD="align: right"]58,380[/TD]
[TD]H6: =G7*PP_A[/TD]
[TD]I6: =G7*PP_B[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]120,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]62.00%[/TD]
[TD="align: right"]60.00%[/TD]
[TD="align: right"]51.59%[/TD]
[TD="align: right"]48.41%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]185,724[/TD]
[TD="align: right"]174,276[/TD]
[TD]H8: =G9*PP_A[/TD]
[TD]I8: =G9*PP_B[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]360,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]72.00%[/TD]
[TD="align: right"]70.00%[/TD]
[TD="align: right"]52.02%[/TD]
[TD="align: right"]47.98%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]249,696[/TD]
[TD="align: right"]230,304[/TD]
[TD]H10: =G11*PP_A[/TD]
[TD]I10: =G11*PP_B[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]480,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have tried using Cell, Address, Offset, Row, Column and other functions, but have not been able to get anything to work.

Does anyone have any magic I can use?

Thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
if I understood well maybe:
=IF($G6="","",$G6*E5)
and
=IF($G6="","",$G6*F5)
 
Last edited:
Upvote 0
sandy666 said:
if I understood well maybe:
=IF($G6="","",$G6*E5)
and
=IF($G6="","",$G6*F5)
Click to expand...

I don't understand these expressions, but I am trying to eliminate literal cell references (G6, $G6, G$6, or $G$6). I want to replace the "G7" in the expression in H6 with some form of the named range (column) "Iterations". I want the result in H6 to be the product of G7 & E6 but using named ranges, not literal cell addresses.
 
Last edited:
Upvote 0
without any references but with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]PGS_A[/td][td=bgcolor:#5B9BD5]PGS_B[/td][td=bgcolor:#5B9BD5]PP_A[/td][td=bgcolor:#5B9BD5]PP_B[/td][td=bgcolor:#5B9BD5]Iterations[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
52.00%​
[/td][td=bgcolor:#DDEBF7]
50.00%​
[/td][td=bgcolor:#DDEBF7]
51.35%​
[/td][td=bgcolor:#DDEBF7]
48.65%​
[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
120,000​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
62.00%​
[/td][td=bgcolor:#DDEBF7]
60.00%​
[/td][td=bgcolor:#DDEBF7]
51.59%​
[/td][td=bgcolor:#DDEBF7]
48.41%​
[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
360,000​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
72.00%​
[/td][td=bgcolor:#DDEBF7]
70.00%​
[/td][td=bgcolor:#DDEBF7]
52.02%​
[/td][td=bgcolor:#DDEBF7]
47.98%​
[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
480,000​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]PGS_A[/td][td=bgcolor:#70AD47]PGS_B[/td][td=bgcolor:#70AD47]PP_A[/td][td=bgcolor:#70AD47]PP_B[/td][td=bgcolor:#70AD47]Iterations[/td][td=bgcolor:#70AD47]Wins_A[/td][td=bgcolor:#70AD47]Wins_B[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
52.00%​
[/td][td=bgcolor:#E2EFDA]
50.00%​
[/td][td=bgcolor:#E2EFDA]
51.35%​
[/td][td=bgcolor:#E2EFDA]
48.65%​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
61620​
[/td][td=bgcolor:#E2EFDA]
58380​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
120000​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
62.00%​
[/td][td=bgcolor:#E2EFDA]
60.00%​
[/td][td=bgcolor:#E2EFDA]
51.59%​
[/td][td=bgcolor:#E2EFDA]
48.41%​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
185724​
[/td][td=bgcolor:#E2EFDA]
174276​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
360000​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
72.00%​
[/td][td=bgcolor:#E2EFDA]
70.00%​
[/td][td=bgcolor:#E2EFDA]
52.02%​
[/td][td=bgcolor:#E2EFDA]
47.98%​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
249696​
[/td][td=bgcolor:#E2EFDA]
230304​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
480000​
[/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicate = Table.DuplicateColumn(Source, "Iterations", "Iterations - Copy"),
    Fill = Table.FillUp(Duplicate,{"Iterations - Copy"}),
    WinsA = Table.AddColumn(Fill, "Wins_A", each [#"Iterations - Copy"]*[PP_A]),
    WinsB = Table.AddColumn(WinsA, "Wins_B", each [#"Iterations - Copy"]*[PP_B]),
    RC = Table.RemoveColumns(WinsB,{"Iterations - Copy"}),
    Type = Table.TransformColumnTypes(RC,{{"PGS_A", Percentage.Type}, {"PGS_B", Percentage.Type}, {"PP_A", Percentage.Type}, {"PP_B", Percentage.Type}, {"Wins_A", type number}, {"Wins_B", type number}})
in
    Type[/SIZE]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]PGS_A[/td][td=bgcolor:#5B9BD5]PGS_B[/td][td=bgcolor:#5B9BD5]PP_A[/td][td=bgcolor:#5B9BD5]PP_B[/td][td=bgcolor:#5B9BD5]Iterations[/td][td=bgcolor:#70AD47]Wins_A[/td][td=bgcolor:#70AD47]Wins_B[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
52.00%​
[/td][td=bgcolor:#DDEBF7]
50.00%​
[/td][td=bgcolor:#DDEBF7]
51.35%​
[/td][td=bgcolor:#DDEBF7]
48.65%​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#E2EFDA]
61620​
[/td][td=bgcolor:#E2EFDA]
58380​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
120,000​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
62.00%​
[/td][td=bgcolor:#DDEBF7]
60.00%​
[/td][td=bgcolor:#DDEBF7]
51.59%​
[/td][td=bgcolor:#DDEBF7]
48.41%​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#E2EFDA]
185724​
[/td][td=bgcolor:#E2EFDA]
174276​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
360,000​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
72.00%​
[/td][td=bgcolor:#DDEBF7]
70.00%​
[/td][td=bgcolor:#DDEBF7]
52.02%​
[/td][td=bgcolor:#DDEBF7]
47.98%​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#E2EFDA]
249696​
[/td][td=bgcolor:#E2EFDA]
230304​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]
480,000​
[/td][td][/td][td][/td][/tr]
[/table]



Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Duplicate = Table.DuplicateColumn(Source, "Iterations", "Iterations - Copy"),
    Fill = Table.FillUp(Duplicate,{"Iterations - Copy"}),
    WinsA = Table.AddColumn(Fill, "Wins_A", each [#"Iterations - Copy"]*[PP_A]),
    WinsB = Table.AddColumn(WinsA, "Wins_B", each [#"Iterations - Copy"]*[PP_B]),
    Type = Table.TransformColumnTypes(WinsB,{{"PGS_A", Percentage.Type}, {"PGS_B", Percentage.Type}, {"PP_A", Percentage.Type}, {"PP_B", Percentage.Type}, {"Wins_A", type number}, {"Wins_B", type number}}),
    ROC = Table.SelectColumns(Type,{"Wins_A", "Wins_B"})
in
    ROC[/SIZE]
 
Last edited:
Upvote 0
sandy666 said:
without any references but with PowerQuery (Get&Transform)
Click to expand...

Wow!!! I'm afraid that is way over my head.

I was able to find a solution along the lines that I can comprehend. This expression eliminates literal cell references (except for the calling cell, which is OK, and uses the named column "Iterations".

Code: 
=INDIRECT(ADDRESS(ROW(H6)+1,COLUMN(Iterations)))*PP_A

I was hoping for something simpler, but this will do.

Excel really needs a simpler, more compact cell addressing syntax.
 
Upvote 0
JenniferMurphy said:
I was able to find a solution along the lines that I can comprehend. This expression eliminates literal cell references (except for the calling cell, which is OK, and uses the named column "Iterations".

Code: 
=INDIRECT(ADDRESS(ROW(H6)+1,COLUMN(Iterations)))*PP_A

I was hoping for something simpler, but this will do.
Click to expand...
This formula will produce the same result as the one you posted above...

=(7:7 Iterations)*(6:6 PP_A)

Just so you know, the space character as I used it above (with a row reference on one side a column reference on the other side) is the Intersect operator (it retrieves the value at the intersection of that row and column).
 
Last edited:
Upvote 0
Rick Rothstein said:
This formula will produce the same result as the one you posted above...

=(7:7 Iterations)*(6:6 PP_A)

Just so you know, the space character as I used it above (with a row reference on one side a column reference on the other side) is the Intersect operator (it retrieves the value at the intersection of that row and column).
Click to expand...

Perfect. I fiddled around with the Intersect operator, but couldn't get it to work. Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Formula in the helper column. need duplicate to be count as One
Replies
7
Views
463
KRice
K
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
571
Greenbehindthecells
G
M
  • Question Question
Match combobox name on userform with shee name and fill empty cell
Replies
0
Views
247
Maklil
M
A
  • Question Question
create report for 40000 rows to calculation stock across sheet based on sheet name in cell value
Replies
1
Views
349
Amer Omar
A
K
  • Solved
formula for the average of cells across columns with multiple conditions
Replies
4
Views
662
kckliodna
K

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

Share this page

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