Hello,
I'm working through this thing, and I've watched too many videos / read too many forum posts and I can't seem to find what I'm looking for.
I'm trying to pull data from one of 6 worksheets (denoted by Range A, Range B, Range C, Range D, Range E, and Range F) I have a drop down that I'm hoping will select the worksheet that this data needs to be pulled from, and then the second drop down would indicate where in that spreadsheet to look.
The goal is for this to be a dashboard that pulls the data from the other worksheets that is based on Range, and the grade.
I have an example of a formula that kind of worked for another workbook, but pulled the whole sheet of information, and I need mine a bit more specific
=if($B$8="Select Geographic Range","",VLOOKUP$F8,CHOOSE(VLOOKUP(CONCATENATE($A$6,$B,$6),GeoCity,2,0),Geo,2,0),SalaryGeoA,SalaryGeoB,SalaryGeoC,SalaryGeoD,SalaryGeoE,SalaryGeoF)2+5,0))
The above formula would pull data into the cells based on what was selected in the drop down. The drop down's were by City and State, which needed the CONCATENATE, but I don't have City / State Data I have Range and Grade data. I would like to keep it referencing the different worksheets, because when the master data gets updated it will be in that particular format.
"Geo" is a name'd reference that looks at
[TABLE="width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
The "Geo City" references =Data!$K:$L where K =Concatenate(I1,J1) and I is the state and J is the City and L is the Geo Range associated with that city. Which is why I don't think I need Concatenate because I'm not looking up particular city/state.
I feel as if I'm trying to reverse engineer this formula to make it apply to what I'm working on and I'm having one heck of a time.
Thanks for anyone's input / help.
I'm working through this thing, and I've watched too many videos / read too many forum posts and I can't seem to find what I'm looking for.
I'm trying to pull data from one of 6 worksheets (denoted by Range A, Range B, Range C, Range D, Range E, and Range F) I have a drop down that I'm hoping will select the worksheet that this data needs to be pulled from, and then the second drop down would indicate where in that spreadsheet to look.
The goal is for this to be a dashboard that pulls the data from the other worksheets that is based on Range, and the grade.
I have an example of a formula that kind of worked for another workbook, but pulled the whole sheet of information, and I need mine a bit more specific
=if($B$8="Select Geographic Range","",VLOOKUP$F8,CHOOSE(VLOOKUP(CONCATENATE($A$6,$B,$6),GeoCity,2,0),Geo,2,0),SalaryGeoA,SalaryGeoB,SalaryGeoC,SalaryGeoD,SalaryGeoE,SalaryGeoF)2+5,0))
The above formula would pull data into the cells based on what was selected in the drop down. The drop down's were by City and State, which needed the CONCATENATE, but I don't have City / State Data I have Range and Grade data. I would like to keep it referencing the different worksheets, because when the master data gets updated it will be in that particular format.
"Geo" is a name'd reference that looks at
[TABLE="width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
The "Geo City" references =Data!$K:$L where K =Concatenate(I1,J1) and I is the state and J is the City and L is the Geo Range associated with that city. Which is why I don't think I need Concatenate because I'm not looking up particular city/state.
I feel as if I'm trying to reverse engineer this formula to make it apply to what I'm working on and I'm having one heck of a time.
Thanks for anyone's input / help.
