Want to have a formula that can display cell Column and Row number of the following:
If an "Input Grid" cell value matches any of the "Petty cash cost flag values", then the Column and Row of that "Input Grid" is to be displayed in the "Formula Section" grid. Unfortunately, unable to figure out hot to attach the file therefore can see below how the worksheet was set up:
Petty Cash Cost Flags (these values are special cost points that we want to know if met) Address is A2, B2 and C2
$0 $15 $30
-------------------------------------------------------------------------------------------------------------------------------
Input Grid below is a condensed version (note: actual grid is much larger - listed by day)
For this condensed input grid:
The address for the labels below starts at A5, B5 etc through H5
The Column going down the page for the Month starts at A6 thru A17
The address for the raw data starts at B6 etc and ends at H17
Totals row starts at A18 and ends at H18
Category:Supplies Auto AM Food Errands Parking Lunch Misc
Jan $30 $6 $7 $8 $9 $10 $11
Feb $24 $2 $15 $16 $21 $12 $1
Mar $2 $3 $2 $4 $0 $9 $15
Apr $21 $12 $8 $9 $0 $16 $21
May $1 $2 $9 $3 $11 $12 $9
Jun $0 $9 $15 $16 $0 $30 $12
Jul $15 $21 $4 $9 $10 $11 $3
Aug $0 $4 $12 $13 $0 $28 $4
Sep $9 $10 $20 $0 $10 $24 $12
Oct $16 $4 $19 $4 $12 $2 $4
Nov $4 $9 $15 $20 $12 $13 $8
Dec $12 $3 $11 $11 $19 $6 $9
Totals $134 $85 $137 $113 $104 $173 $109
This section is the data entry section.
Only inserting whole dollar amounts in this section
------------------------------------------------------------------------------------------------------------------------------------------------
Formula Section below is what a different table looks like on a different area of the same worksheet (it didn't have to be on the same worksheet though - but just for convenience)
The address for the labels below starts at J5 Q5
The Column going down for the Month starts at J6 thru J17
The address for the raw data starts at K6 etc and ends at Q17
Category:Supplies Auto AM Food Errands Parking Lunch Misc
Jan $30 B1
Feb
Mar
Apr
May
Jun $0 B1
Jul $15 B1
Aug $0 B1
Sep
Oct
Nov
Dec
This section is the Formula Section grid.
Only inserted a formula in the Formula Section "Supplies" column for now due to the formula is working correctly (explained below):
With the given formula written (shown in the Supplies column above) some of the resulting information displayed in the cell is correct as listed below:
Displays only which "Input Grid" cell matches any of the 3 "Petty Cash Cost Flags" values.
Then it displays which "Petty Cash Cost Flags" value was matched.
Then it displays the Column (however, open to any other way to automatically get the Column to display)
What is not correct though is the Row mumber. Example: Supplies column for Jan should display as $30 B6 but it only displays $30 B1
Unable to get any output other than B1 to display - need to know the actual Column and Row of each cell of the Input Grid that match the Petty Cash Cost Flags.
Attempted Formula used: =IFERROR("$0 B"&MATCH($A$2,B6,0),IFERROR("$15 B"&MATCH($B$2,B6,0),IFERROR("$30 B"&MATCH($C$2,B6,0), "")))
But can use any formula that will work for the application - hopefully one that can be dragged down the same column
(or Row if possible) without needing to modify each cell to make the formula work
If an "Input Grid" cell value matches any of the "Petty cash cost flag values", then the Column and Row of that "Input Grid" is to be displayed in the "Formula Section" grid. Unfortunately, unable to figure out hot to attach the file therefore can see below how the worksheet was set up:
Petty Cash Cost Flags (these values are special cost points that we want to know if met) Address is A2, B2 and C2
$0 $15 $30
-------------------------------------------------------------------------------------------------------------------------------
Input Grid below is a condensed version (note: actual grid is much larger - listed by day)
For this condensed input grid:
The address for the labels below starts at A5, B5 etc through H5
The Column going down the page for the Month starts at A6 thru A17
The address for the raw data starts at B6 etc and ends at H17
Totals row starts at A18 and ends at H18
Category:Supplies Auto AM Food Errands Parking Lunch Misc
Jan $30 $6 $7 $8 $9 $10 $11
Feb $24 $2 $15 $16 $21 $12 $1
Mar $2 $3 $2 $4 $0 $9 $15
Apr $21 $12 $8 $9 $0 $16 $21
May $1 $2 $9 $3 $11 $12 $9
Jun $0 $9 $15 $16 $0 $30 $12
Jul $15 $21 $4 $9 $10 $11 $3
Aug $0 $4 $12 $13 $0 $28 $4
Sep $9 $10 $20 $0 $10 $24 $12
Oct $16 $4 $19 $4 $12 $2 $4
Nov $4 $9 $15 $20 $12 $13 $8
Dec $12 $3 $11 $11 $19 $6 $9
Totals $134 $85 $137 $113 $104 $173 $109
This section is the data entry section.
Only inserting whole dollar amounts in this section
------------------------------------------------------------------------------------------------------------------------------------------------
Formula Section below is what a different table looks like on a different area of the same worksheet (it didn't have to be on the same worksheet though - but just for convenience)
The address for the labels below starts at J5 Q5
The Column going down for the Month starts at J6 thru J17
The address for the raw data starts at K6 etc and ends at Q17
Category:Supplies Auto AM Food Errands Parking Lunch Misc
Jan $30 B1
Feb
Mar
Apr
May
Jun $0 B1
Jul $15 B1
Aug $0 B1
Sep
Oct
Nov
Dec
This section is the Formula Section grid.
Only inserted a formula in the Formula Section "Supplies" column for now due to the formula is working correctly (explained below):
With the given formula written (shown in the Supplies column above) some of the resulting information displayed in the cell is correct as listed below:
Displays only which "Input Grid" cell matches any of the 3 "Petty Cash Cost Flags" values.
Then it displays which "Petty Cash Cost Flags" value was matched.
Then it displays the Column (however, open to any other way to automatically get the Column to display)
What is not correct though is the Row mumber. Example: Supplies column for Jan should display as $30 B6 but it only displays $30 B1
Unable to get any output other than B1 to display - need to know the actual Column and Row of each cell of the Input Grid that match the Petty Cash Cost Flags.
Attempted Formula used: =IFERROR("$0 B"&MATCH($A$2,B6,0),IFERROR("$15 B"&MATCH($B$2,B6,0),IFERROR("$30 B"&MATCH($C$2,B6,0), "")))
But can use any formula that will work for the application - hopefully one that can be dragged down the same column
(or Row if possible) without needing to modify each cell to make the formula work
