Data Change based on 2 Different Drop Down Lists

[emack1230]

1st thank you in advance, this forum has been a life saver multiple times.

My issue: I am tracking employee turnover with data showing the different positions of employees and how many have terminated. I am wanting our table to auto change the numbers based on 1) Fiscal Year in a drop down list, then 2) what week of that fiscal year. Have played with different countifs and ifs, and just seems very complicated to do it that way and haven't gotten it to work. Thanks gain.

 

[etaf]

depends on how the data is all laid out that you are then counting and if you have the fiscal year - as a date or text and the week as a number


A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[emack1230]

depends on how the data is all laid out that you are then counting and if you have the fiscal year - as a date or text and the week as a number


A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

working on getting it to google docs and will share the link. I made some notes as to where some data comes from since I was unable to pull those sheets. So hopefully with what I have it will give you an idea of what I am trying to do.

 

[etaf]

ok, I'm in UK timezone and now away most of tomorrow - so depends on how complicated a solution may be, BUT a link sample will help other members replying

 

[emack1230]

here is a link to the file. https://docs.google.com/spreadsheets/d/1Ta6js2oHinMZzkFJ5OSFeklWrjofihJR/edit?usp=drivesdk&ouid=111221843069575143633&rtpof=true&sd=true

What I am wanting to have auto change is in the red circle. I have notes below to help explain what is going on because the original file is huge and many tabs. So I tried my best to describe what the cells are and where the info comes from.

 

[etaf]

its not set to public access as its asking for access authorisation

 

[emack1230]

oops sorry about that, should work now.

 

[etaf]

yes, but you are choosing to use fiscal and week
and no data included
How is all the data laid out , maybe difficult if not in a database style format

 

[emack1230]

So we have the date tab like I provided to give us the fiscal year and week dates. As for the data for each employee, we have the different headers that include their identification such as Production, Maintenance, etc. Also on their line is their Hire Date and Term date if they were termed less than 90 days. That is the main number we monitor. I wasn't sure if using a pivot table with slicers would be the way to go. The other info I referenced would be difficult to put somewhere to show for an example. I apologize for that.

 

[etaf]

yes pivot table may work , when i worked in performance improvement & change management - we used those a lot , but going back into 90's - but would be useful to have the data examples - not loads , and without names ,

you can use things like COUNTIFS() SUMIFS() maybe INDIRECT()
But the data would need to be in a format which works ok for those functions