Change Drop downs to populate multiple values into other cells or return a value of 0

Hi,

It sounds like you have a lot going. My first suggestion would be to provide a sanitized "working" example of your file. Please don't provide one that just has parts, only to find out later that something doesn't work because the whole picture wasn't provided. "No!!!" don't provide a picture. LOL.

I'll be happy to look at what you provide and hopefully help you find a working solution.

Cheers

It's uploaded. I think I have some of it fixed, but not sure. It still reports N/A instead of (0). I've hightlighted the cells to look at in Yellow on the FTE sheet and the data sheet.

Thanks

I am not sure what you did wrong, but the file isn't uploaded.

Forgot to Save after uploading. Here it is.

Hi,

What is the criteria you are using to the values in DATA Sheet columns R:CI?
From the looks of it they are just transposed numbers from FTE Calculator column O.
Then at cell Q5 (50/50 Water/Electric) you divide by 2.

What is the reasoning behind coping the data over to another sheet, dividing by 2 and then bringing it back?

We program some of the peoples salaries 50/50 between PQ34 and SQ34 as well as Water and Electricity, that is why I divided by 2. So I need to be able to populate each column (PQ34 & SQ34) with half of their salary if I select "50/50 PQ34-SQ34" in the drop down.

Yes I transposed the numbers so I could use VLookup. Is there an easier way?

Can't you do the math on the FTE Calculator page? For example cell S4 = O4/2

The Chief wants to be able to play around with positions in the Drop down (Column N) as well as the Pay Grades (Column G). So when he makes a change in either or both of these dropdowns I need them to populated the appropriate Column Q thruogh W. Then I need all the columns to autosum in Row 2.

What is the relationship between Column N and Q through W?

If N4 is changed to FMSS what are the expected results in Q through W?
What is expected when a cell in Column N is changed to:

FO1Z
FM1Z
Water 0150
Electricity 0153
50/50 Water/Electric
Housing PQ34
Housing SQ34
50/50 PQ34-SQ34
Cyclic

Give me some examples so I can better understand what you need.

Cheers

OK. With regard to excel you have to be exact with relationships.

Trying to follow your logic I created an array on the DATA Sheet. It is highlighted in blue.

Hopefully this will give you an idea of how to use something like this in your calculator.

The formulas in columns Q:W multiply the Column O value with the 1 or 0.5 in the Data Sheet blue array.

See the attached example:

Hope this makes sense and is helpful.

Cheers

Perfect. Thanks for all you hard work on this. I don't understand it, but it works just like I wanted.

Thanks again.

Glad I could be of some assistance.

Cheers

Hi,

Yes, changing names and adding new columns without updating the formulas will cause errors.

To reduce confusion I cleaned up the data area on the DATA Sheet.

If you add rows and columns to the DATA Sheet range, the formulas in the FTE Calculator will need to be updated.

See attached file with the corrections.

Cheers

Hi Again,

I need just a few things fixed.

1. I need columns X & Y populated with the code from columns Q-W.
2. I need all rows down to Row 66 populated with the code for columns Q-Y.

I tried to copy them over but that didn't work.

Thanks again

Acutually could you add down to row 85 for columns Q-Y. I will fix all the new positions, just need the code for now.

Thanks,

Hi,

Here is the formula:

Please Login or Register  to view this content.

Just enter it in Q4, copy across and down.

Cheers