- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Looking for Some Excel Help
Posted on 3/26/18 at 4:23 pm
Posted on 3/26/18 at 4:23 pm
I'll try and succinctly describe what I'm trying to do. I have worksheet that contains four sets of data. I need excel to populate a table based on these data sets, based on a user selected drop down menu.
Here is a picture of the spreadsheet. The spreadsheet user would select their data set of choice (blue box), then excel would populate the table in the middle based on the users selection, from the tables on the far left.
Trying to do this with =lookup...but Im not sure if thats the right path. Please excuse my excel ignorance.
Here is a picture of the spreadsheet. The spreadsheet user would select their data set of choice (blue box), then excel would populate the table in the middle based on the users selection, from the tables on the far left.
Trying to do this with =lookup...but Im not sure if thats the right path. Please excuse my excel ignorance.
3
Posted on 3/26/18 at 4:52 pm to ZoneLiftGMC
First thing that came to my mind (probably not the best way) is to do =IF(EXACT(*output cell*, *blue cell*)
Posted on 3/26/18 at 6:03 pm to ZoneLiftGMC
Name each range in each data set, like Data_Set_1, incorporating the cell range from the top cell in the classification column to the bottem cell in the OT Rate column for each data set.
in the top cell of the ST Rate Column for the group you want to populate add
=VLOOKUP($I3,INDIRECT($R$2),2,FALSE)
Where I3 is the first cell in the classification column adjacent to the ST Rate cell that you just entered the the formula in and $R$2 is the cell with the drop down value.
Copy that cell to the top cell in the OT Rate column then chance the 2 to 3 like so:
=VLOOKUP($I3,INDIRECT($R$2),3,FALSE)
Then copy Down to the bottom and voila.
The range names cannot have spaces which is why I used Data_Set_1 and so on.
You could also enter the actual range as your list so your list cells will contain $A$3:$C$14 or Sheet3!$A$3:$C$14 in the cell depending if it's the same sheet or not.
I can send you an example if you need me too, just post your email if you need it.
in the top cell of the ST Rate Column for the group you want to populate add
=VLOOKUP($I3,INDIRECT($R$2),2,FALSE)
Where I3 is the first cell in the classification column adjacent to the ST Rate cell that you just entered the the formula in and $R$2 is the cell with the drop down value.
Copy that cell to the top cell in the OT Rate column then chance the 2 to 3 like so:
=VLOOKUP($I3,INDIRECT($R$2),3,FALSE)
Then copy Down to the bottom and voila.
The range names cannot have spaces which is why I used Data_Set_1 and so on.
You could also enter the actual range as your list so your list cells will contain $A$3:$C$14 or Sheet3!$A$3:$C$14 in the cell depending if it's the same sheet or not.
I can send you an example if you need me too, just post your email if you need it.
Posted on 3/26/18 at 6:21 pm to ZoneLiftGMC
Upvoted because I believe you are the first person to ever ask for Excel help here who included diagrams and a clear explanation.
Posted on 3/27/18 at 7:43 am to foshizzle
Thanks for the help everyone. I ended up getting rid of all of the table headings and making 1 table, but added a column on the far left identifying which data set that row belonged to.
Once I had 1 large table I used index and match to essentially create a two criteria Vblookup. I'll post my solution later today.
Once I had 1 large table I used index and match to essentially create a two criteria Vblookup. I'll post my solution later today.
Posted on 3/27/18 at 9:27 pm to ZoneLiftGMC
It's possible using/inserting a Pivot Table could give you the filter you seek? You may have to rearrange the 4 ranges into vertical columns.
I actually use a very massive 60K line pivot table at my company for quick analysis of payroll data with many variables
I actually use a very massive 60K line pivot table at my company for quick analysis of payroll data with many variables
Posted on 3/28/18 at 5:02 am to ZoneLiftGMC
As the previous poster said, now that you've added a designator column, a pivot table is your most direct solution.
Posted on 3/28/18 at 7:42 pm to LSUfan20005
I third the pivot.
I didn’t know much about them. Someone suggested I use one for a large amount of data. Gave it a shot and now I use them every chance I have.
I didn’t know much about them. Someone suggested I use one for a large amount of data. Gave it a shot and now I use them every chance I have.
Page 1 of 1
Back to top
Follow TigerDroppings for LSU Football News