How to reference one cell and also reference another cell to mimic the value

ZACowboys

New Member
Joined
Feb 9, 2024
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hello,

My spreadsheet is setup to where cell (B3) is a dropdown list consisted of 3 animals (DOG/CAT/FISH). In Columns F/G/H shows if that specific animal needs that item reflected by an “X”. I'm wanting to come up with a formula to where if you select each name on the dropdown, then Column A will automatically reflect the "X" or a "Blank" if that animal doesn't require it.

For instance, if DOG was selected in B3, then Column A would have the cells with an "X" such as in Column F. Additionally, if you select CAT in the dropdown in B3, the cells in Column A would reflect what has an "X" in Column G. Furthermore, if you select FISH in the dropdown in B3, the cells in Column A would reflect what has an "X" in Column H only.



Thank you in advance for the help!


1707518352292.png
 

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).
See if this does what you want:
Book2
ABCDEFGH
1
2
3TypeFish
4
5
6
7
8
9
10
11
12Animal NeedsDOGCATFISH
13XFoodXXX
14 WaterXX
15 Flea PreventionX
16 GroomingXX
17 LeashX
18 Nail GrinderX
19 Poop BagsX
20XToysXX
21 TrainingX
22 CrateX
Sheet2
Cell Formulas
RangeFormula
A13:A22A13=IF(IF($B$3="Dog",F13,IF($B$3="Cat",G13,IF($B$3="Fish",H13,"")))=0,"",IF($B$3="Dog",F13,IF($B$3="Cat",G13,IF($B$3="Fish",H13,""))))
Cells with Data Validation
CellAllowCriteria
B3ListDog,Cat,Fish
 
Upvote 0
Book1.xlsm
ABCDEFGHI
1Animals
2
3TypeDOG
4
5
6
7
8
9
10
11
12needsDOGCATFISH
13xFoodxxx
14xWaterxx
15 Flea Prevx
16xGroomingxx
17xLeashx
18 Nail Grinderx
19xPoop Bagsx
20xToysxx
21xTrainingx
22xCratex
23
Sheet6
Cell Formulas
RangeFormula
A13:A22A13= IF(XLOOKUP($B$3,$F$12:$H$12,F13:H13,,0)="x","x","")
Cells with Data Validation
CellAllowCriteria
B3List=$F$12:$H$12
 
Upvote 0
A shorter option to my original post, and doesn't have to be completely re-written if you change the list values, just update the ranges:
Book2
ABCDEFGH
1
2
3TypeCat
4
5
6
7
8
9
10
11
12Animal NeedsDOGCATFISH
13XFoodXXX
14XWaterXX
15XFlea PreventionX
16XGroomingXX
17 LeashX
18XNail GrinderX
19 Poop BagsX
20 ToysXX
21 TrainingX
22 CrateX
Sheet2
Cell Formulas
RangeFormula
A13:A22A13=IF(INDEX(F13:H13,,MATCH($B$3,$F$12:$H$12,0))="X",INDEX(F13:H13,,MATCH($B$3,$F$12:$H$12,0)),"")
Cells with Data Validation
CellAllowCriteria
B3ListDog,Cat,Fish
 
Upvote 0
In case Xlookup() doesn't work in 2016, here's an alternative:
Book1
ABCDEFGH
3FISH
4
5
6
7
8
9
10
11
12NEEDSDOGCATFISH
13xFoodxxx
14 Waterxx
15 Fleax
16 Groomxx
17 Leashx
18 Nailx
19 Poopx
20xToysxx
21 Trainingx
22 Cratex
Sheet1
Cell Formulas
RangeFormula
A13:A22A13=IF(HLOOKUP($B$3,$F$12:$H$22,ROW()-11)="x","x","")
Cells with Data Validation
CellAllowCriteria
B3ListDOG,CAT,FISH
 
Upvote 0
My suggested formula in post #5 should have been:
Rich (BB code):
=IF(HLOOKUP($B$3,$F$12:$H$22,ROW()-11,FALSE)="x","x","")
 
Upvote 0
A plain INDEX should work:

Book1
ABCDEFGH
1
2
3TypeCat
4
5
6
7
8
9
10
11
12Animal NeedsDOGCATFISH
13XFoodXXX
14XWaterXX
15XFlea PreventionX
16XGroomingXX
17 LeashX
18XNail GrinderX
19 Poop BagsX
20 ToysXX
21 TrainingX
22 CrateX
23
Sheet5
Cell Formulas
RangeFormula
A13:A22A13=INDEX(F13:H13,MATCH(B$3,F$12:H$12,0))&""


Note the &"" on the end to avoid displaying a "0" in case of an empty cell.
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,826
Members
452,673
Latest member
LaMiaAvy

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