Search for Referenced Data

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
104
Office Version
  1. 365
Platform
  1. Windows
I'm trying to search for data using a referenced cell and on a different worksheet return the value in the row below in the proper column. On the first worksheet the referenced cell's value is 107, I want to look at the second worksheet and find 107, look across the columns to a specific column and then look down at the row below to find the data in that cell. In the example below the answer would be 79,300. [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]107
[/TD]
[TD]BJ
[/TD]
[TD]41,000
[/TD]
[TD]25,000
[/TD]
[TD]8,700
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3,700
[/TD]
[TD]800
[/TD]
[TD]100
[/TD]
[TD]79,300
[/TD]
[/TR]
</tbody>[/TABLE]
 
It works on the first table but when I copy it down it goes wrong, I'll work on it but I'm not seeing where it is wrong it looks at everything in the same manner just one item is different that's the table ID number
 
Upvote 0
[TABLE="width: 1255"]
<tbody>[TR]
[TD]Game Type
[/TD]
[TD]Table ID
[/TD]
[TD]$25,000
[/TD]
[TD]$5,000
[/TD]
[TD]$1,000
[/TD]
[TD]$500
[/TD]
[TD]$100
[/TD]
[TD]$25
[/TD]
[TD]$5
[/TD]
[TD]$1
[/TD]
[TD]$0.50
[/TD]
[TD]$0.25
[/TD]
[TD] Total
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TC
[/TD]
[TD]101
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$41,000.00
[/TD]
[TD="align: right"]$22,500.00
[/TD]
[TD="align: right"]$9,100.00
[/TD]
[TD="align: right"]$3,500.00
[/TD]
[TD="align: right"]$600.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$69,800.00
[/TD]
[TD="align: right"]$76,800.00
[/TD]
[/TR]
[TR]
[TD]UT
[/TD]
[TD]102
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$77,100.00
[/TD]
[TD="align: right"]$1,000.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$71,700.00
[/TD]
[TD="align: right"]$78,200.00
[/TD]
[/TR]
[TR]
[TD]SD
[/TD]
[TD]103
[/TD]
[TD="align: right"]$900.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$600.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$69,200.00
[/TD]
[TD="align: right"]$1,700.00
[/TD]
[/TR]
[TR]
[TD]SR
[/TD]
[TD]104
[/TD]
[TD="align: right"]$777.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$43,000.00
[/TD]
[TD="align: right"]$14,500.00
[/TD]
[TD="align: right"]$10,700.00
[/TD]
[TD="align: right"]$3,600.00
[/TD]
[TD="align: right"]$1,100.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$393,500.00
[/TD]
[TD="align: right"]$73,777.00
[/TD]
[/TR]
[TR]
[TD]RW
[/TD]
[TD]105
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$72,600.00
[/TD]
[TD="align: right"]$300.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$72,200.00
[/TD]
[TD="align: right"]$73,000.00
[/TD]
[/TR]
[TR]
[TD]DD
[/TD]
[TD]106
[/TD]
[TD="align: right"]$700.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$400.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$59,100.00
[/TD]
[TD="align: right"]$1,300.00
[/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD]107
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$46,000.00
[/TD]
[TD="align: right"]$19,000.00
[/TD]
[TD="align: right"]$8,400.00
[/TD]
[TD="align: right"]$3,200.00
[/TD]
[TD="align: right"]$900.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$77,100.00
[/TD]
[TD="align: right"]$77,600.00
[/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD]108
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$77,700.00
[/TD]
[TD="align: right"]$700.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$72,600.00
[/TD]
[TD="align: right"]$78,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$885,200.00
[/TD]
[TD="align: right"]$460,877.00
[/TD]
[/TR]
</tbody>[/TABLE]
Here you can see the data that was pulled into this array using the formula, it lists the tables, their IDs, chip denominations (they should be in proper units shown in the headers) and on the last and next to last are totals. The totals should be exactly equal, the last column is adding up all the denominations to the left and comparing it's total to the cell next to it on the left you see they don't match. The total for all tables don't match either.
 
Upvote 0
[TABLE="class: grid, width: 1108"]
<tbody>[TR]
[TD]C
[/TD]
[TD]
[/TD]
[TD]CP
[/TD]
[TD]
[/TD]
[TD]_5
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]PAGE 1
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]FS
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]CH
[/TD]
[TD]
[/TD]
[TD]CH
[/TD]
[TD]
[/TD]
[TD]SD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--
[/TD]
[TD]-------
[/TD]
[TD]--
[/TD]
[TD]---------
[/TD]
[TD]---------------------------
[/TD]
[TD]---------------------------------
[/TD]
[TD]-------------
[/TD]
[TD]-------------------------------------
[/TD]
[TD]-----------------
[/TD]
[TD]--------------------
[/TD]
[TD]-------------------------------
[/TD]
[/TR]
[TR]
[TD]GM
[/TD]
[TD]E/TBL/S
[/TD]
[TD]GM
[/TD]
[TD]T
[/TD]
[TD="align: right"]$25,000.00
[/TD]
[TD="align: right"]$5,000.00
[/TD]
[TD="align: right"]$1,000.00
[/TD]
[TD="align: right"]$500.00
[/TD]
[TD="align: right"]$200.00
[/TD]
[TD="align: right"]$100.00
[/TD]
[TD="align: right"]$25.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$5.00
[/TD]
[TD="align: right"]$1.00
[/TD]
[TD="align: right"]$0.50
[/TD]
[TD="align: right"]$0.25
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--
[/TD]
[TD]-------
[/TD]
[TD]--
[/TD]
[TD]---------
[/TD]
[TD]---------------------------
[/TD]
[TD]---------------------------------
[/TD]
[TD]-------------
[/TD]
[TD]-------------------------------------
[/TD]
[TD]-----------------
[/TD]
[TD]--------------------
[/TD]
[TD]-------------------------------
[/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]107
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]41,000.00
[/TD]
[TD="align: right"]22,500.00
[/TD]
[TD][/TD]
[TD="align: right"]9,100.00
[/TD]
[TD="align: right"]3,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]77,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]900
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]108
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD="align: right"]777
[/TD]
[TD][/TD]
[TD="align: right"]43,000.00
[/TD]
[TD="align: right"]14,500.00
[/TD]
[TD][/TD]
[TD="align: right"]10,700.00
[/TD]
[TD="align: right"]3,600.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]72,600.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]700
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]301
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46,000.00
[/TD]
[TD="align: right"]19,000.00
[/TD]
[TD][/TD]
[TD="align: right"]8,400.00
[/TD]
[TD="align: right"]3,200.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]77,700.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,000.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]302
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30,000.00
[/TD]
[TD="align: right"]22,000.00
[/TD]
[TD][/TD]
[TD="align: right"]8,100.00
[/TD]
[TD="align: right"]4,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]65,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,200.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]303
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]44,000.00
[/TD]
[TD="align: right"]20,500.00
[/TD]
[TD][/TD]
[TD="align: right"]8,700.00
[/TD]
[TD="align: right"]4,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78,400.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,000.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]304
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]42,000.00
[/TD]
[TD="align: right"]16,500.00
[/TD]
[TD][/TD]
[TD="align: right"]8,900.00
[/TD]
[TD="align: right"]3,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]71,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]308
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]46,000.00
[/TD]
[TD="align: right"]22,500.00
[/TD]
[TD][/TD]
[TD="align: right"]9,700.00
[/TD]
[TD="align: right"]2,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]81,800.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,000.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]309
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]43,000.00
[/TD]
[TD="align: right"]18,000.00
[/TD]
[TD][/TD]
[TD="align: right"]10,400.00
[/TD]
[TD="align: right"]6,300.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]78,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]700
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]407
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]39,000.00
[/TD]
[TD="align: right"]30,500.00
[/TD]
[TD][/TD]
[TD="align: right"]9,000.00
[/TD]
[TD="align: right"]3,925.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]83,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,060.00
[/TD]
[TD="align: right"]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]501
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18,500.00
[/TD]
[TD][/TD]
[TD="align: right"]6,100.00
[/TD]
[TD="align: right"]2,650.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28,800.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,475.00
[/TD]
[TD="align: right"]75
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]502
[/TD]
[TD]BJ
[/TD]
[TD="align: right"]11/15/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19,000.00
[/TD]
[TD][/TD]
[TD="align: right"]10,400.00
[/TD]
[TD="align: right"]3,900.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]34,500.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,110.00
[/TD]
[TD="align: right"]90
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]503
[/TD]
[TD]BJ
[/TD]
[TD="align: right"]11/15/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21,000.00
[/TD]
[TD][/TD]
[TD="align: right"]9,500.00
[/TD]
[TD="align: right"]4,525.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,040.00
[/TD]
[TD="align: right"]35
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]504
[/TD]
[TD]BJ
[/TD]
[TD="align: right"]10/31/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21,500.00
[/TD]
[TD][/TD]
[TD="align: right"]7,700.00
[/TD]
[TD="align: right"]3,600.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]34,100.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,230.00
[/TD]
[TD="align: right"]70
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BJ
[/TD]
[TD="align: right"]511
[/TD]
[TD]BJ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14,500.00
[/TD]
[TD][/TD]
[TD="align: right"]9,400.00
[/TD]
[TD="align: right"]2,800.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28,000.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1,200.00
[/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
The data for the smaller denominations is in the third row rather than the second row:


Excel 2010
MNOPQRSTUVWXYZAA
1Game TypeTable ID$25,000$5,000$1,000$500$200$100$25$5$1$0.50$0.25TotalCheck
2TC107004100022500091003500900100007710077100
Sheet1
Cell Formulas
RangeFormula
O2=INDEX(E$9:E$50,MATCH($N2,$B$9:$B$50,FALSE))
P2=INDEX(F$9:F$50,MATCH($N2,$B$9:$B$50,FALSE))
Q2=INDEX(G$9:G$50,MATCH($N2,$B$9:$B$50,FALSE))
R2=INDEX(H$9:H$50,MATCH($N2,$B$9:$B$50,FALSE))
S2=INDEX(I$9:I$50,MATCH($N2,$B$9:$B$50,FALSE))
T2=INDEX(J$9:J$50,MATCH($N2,$B$9:$B$50,FALSE))
U2=INDEX(K$9:K$50,MATCH($N2,$B$9:$B$50,FALSE))
V2=INDEX(E$9:E$50,MATCH($N2,$B$9:$B$50,FALSE)+2)
W2=INDEX(F$9:F$50,MATCH($N2,$B$9:$B$50,FALSE)+2)
X2=INDEX(G$9:G$50,MATCH($N2,$B$9:$B$50,FALSE)+2)
Y2=INDEX(H$9:H$50,MATCH($N2,$B$9:$B$50,FALSE)+2)
Z2=SUM(O2:Y2)
AA2=INDEX(K$9:K$50,MATCH($N2,$B$9:$B$50,FALSE)+1)
 
Upvote 0
Solution
I've tried this formula and it returns #N/A Did you notice that in the raw data the tables are listed by game type in that grouping and I'm regrouping according to location on the casino floor and this will cause some data to change location on the spreadsheet from time to time.
 
Upvote 0
Which formula? I copied the data you posted and pasted it into A1. You can see that the formulas work from the table I posted.
 
Upvote 0

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