Excel 2013 :: Calculating Value Based On Multiple References And Dropdowns
Jul 19, 2014
Using Excel 2013.
I have two sheets in a workbook.
Sheet 1 has the following columns on it, with the following possible values.
People (Dropdown with value between 1 and 8)
Modifier 1 (Dropdown with Text 1, Text 2, Text 3)
Modifier 2 (Dropdown with Text 1, Text 2, Text 3)
Modifier 3 (Dropdown with Text 1, Text 2, Text 3)
Modifier 4 (Dropdown with Text 1, Text 2, Text 3)
Result
Sheet 2
Contains a set of values (Distance) that correspond to the value of the People dropdown (ie. People 1 = Distance 5, People 2 = Distance 6 and a table that looks like the following:
Mod 1
Mod 2
Mod 3
Mod 4
Text 1
0
0
0
0
Text 2
2
0.5
0.25
0.25
Text 3
7
1.75
0.875
0.875
What I want to happen is that the Result field value on table one is the result of:
On sheet on I select People = 2, Modifier 1 = Text 3, Modifier 2 = Text 1, Modifier 3 = Text 1, Modifier 4 = Text 1 which I want to result in the following formula:
6*(7 + 0 + 0 + 0)
This might be exceedingly simple, but I just cannot wrap my head around how to do it.
I am working on a commission report for our veterinarians. I have a tab/sheet for each year, with a column for each month, a row for each of the veterinarian. Each vet is on the same row, year to year. I need to be able to calculate the %, year to date, for each vet, across the past 2 years. I can do the auto sum, but do not know how to do percentage. I have row 3, column b3 thru m3. I want to do 2012 and 2013. Each sheet is name the year.
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max $987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
I need entering multiple arguments in an IF formula in Excel 2013. Here is the formula with just 1 argument.
=IF(SUM($G$4:G15)>3000,G15*0.8,0)
I need to combine it with the following.........
=IF(SUM($G$4:G15)>10000,sum($G$4:G15)-G15,0)
So to say, if the sum totals more than 3000 but less than 10000, then I want the cell value (G15) multiplied by 80% (.80). if it is greater than 10000, i want the sum minus 10000. If both arguments are false (sum totals less than 3000) then the value placed should be 0.
what I need to do to update a folder full of files. There are 120+ .xls files in one folder, the merged cell A30-V38 needs to change its current text to "4th Quarterly Printer Preventative Maintenance".
I have zero prior knowledge of visual basic, and have Excel 2013.
I have an Excel file that's updated monthly. when it does save its around 16mb and can take up to 12 hours to save, and sometimes just doesn't.
I have tried saving as binary, I have made sure exact size of area to be saved is required, I have tried save with no calculations.
Basically the only reason I need to save it is so that another analysis spreadsheet can pull data from it. The file is heavily formatted, charts, vlookup tables etc, none of which is needed when analysis spreadsheet links to it.
With excel 2013 you're now able to open multiple windows or views of the same workbook. However when a workbook is saved with multiple windows open, the next user to open the spreadsheet will also open it with multiple windows. Which can be very annoying when most people work off of one window vs. multiple. Is there a way to disable saving the multiple windows or a macro to force open excel in 1 window?
I am working with DatePicker calendar. I have two userform with date entries. In one userform I will have 2 textbox to enter dates. In the other one I will have 4 dates to enter. I used to have Excel 2007 with Calendar Control and used to work fine but had to make multiple calendar to control individual Textbox in multiple Userforms.
I am looking for a way to, If a userform is selected to trigger only the textbox name that is within that userform. If I add more entries the code adds automatically the date from the previous userform in the next one.
[Code].....
I was thinking that this would work but it still adds the date to my frmdashboard userform if I am in my frmdata userform.
Is there a way to trigger only the userform that I am currently in?
Using Excel 2013.
Note: I also have a "Clear All" VB Code that will delete every entry in my userform on Initiate but I am looking to not go this path.
I have a spreadsheet with a database query attached (refreshing every 60 seconds) showing a stock summary by product.
I need the spreadsheet to play a .WAV file when values change. i.e.;
If cell A1 <6, play sound1.wav If cell A2 <6, play sound1.wav If cell A3 <6, play sound2.wav If cell A4 <6, play sound2.wav
This may need to be repeated several times based on certain criteria. I have a total of about 30 cells which I need to be looked at individually (not a sum of) and to play the relevant sound.
I have seen different people try to tackle this in different ways. The ideal way would be creating alarms in VBA and triggering them using a formula, but I cannot get these to work in Excel 2010 or 2013.
I have the following data set with about 500 rows. It has one large value in column A, followed by a number between 1 and 24 in column B.
I am trying in Excel 2013 to develop a formula that will: divide A by the value in cell BPlace in cell C, D, E, F... the result of this division, where the number of times the value is repeated depends on the value in B. E.g.
Example of source values/data set: A B
18504 18
2011 2
23694 24
(about 500 rows more)
An example of the output result that I am looking for: A B C D E
2011 2 1,005.5 1,005.5
In above example, the value in column B is 2, so the result of the division of value A with value B is repeated twice. In case value B would be 18, the value would be repeated 18 times...
I have been trying to do this in Excel for more than 2 hours, but I did not manage to complete it.
i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.
these workbooks all have the same name with the date at the end
example: workbook 06-01-2011 workbook 06-02-2011 all the way to workbook 12-31-2011
usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant
i have seein INDIRECT of the cell changes but nothing for the workbook name chaning
here is what i am trying to do: ='C:File Path[WorkbookX.xls]Sheet1'!A1 where the X WorkbookX is variable (the date at end of file name)
i have a list of all workbook names or just the dates and would like to do something like:
=D1&$A$1 where D1 is the full path name or ='C:File Path[Workbook"D1".xls]Sheet1'!A1 where D1 is the date name
using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.
my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have numbers that will display in column G. I have payment types entered in column E. So if 'C FUEL','FA','C M/C', is entered in column E I want the number in column G to be red.
What I am trying to do is calculate a particular charge based on two main variables the city and weight chosen (each of which will correspond to a particular value) .I have these cities and weights which correspond to a particular price per 100kg when two are selected
My excel data is set up like this
(A).....................(b)......................(c)..................(d)........................(e).........................(F) .......................Weight..................0-999Kg.............1000-1999kg..........2000-4999kg..........5000-9999kg 1. City 2. Toronto....................................$5.50..................$4.00..................$3.75..................$3.50
(I added a,b,c,d and 1,2,3,4 for reference when explaining the formula)
I have a separate row where I am able to enter a weight in one column (A24) and and choose the city from a list in the second column (B24). I need the next column (C24) to display the dollar charge per 100kg from my data that corresponds to the city I have chosen and the weight entered. I am trying to figure out a formula for C24 that allows for instance $5.50 to be displayed when I type in 800kg for A24 and choose Toronto from a drop down list in B24. This row will look like this for reference:
..................A............................ B..............................C. ..................Weight..................City..................Charge per 100kg 24...............800..................Toronto..................$5.50
I've finally figured out the correct code to allow multiple selections from one of my drop down lists. Now, when I try selecting an item from a separate list (which I only want to be able to choose one thing), it's accepting multiple items. How do I have both working properly?
This is the code I have for my multiple selection list (which is only in column M):
Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count > 1 Then GoTo exitHandler On Error Resume Next
I have several different categories of data, with each list named after the full category title. The data is stored on another worksheet. On the worksheet that will be interacted with, there is a dropdown menu that allows you to choose which category of data you want.
I am trying to get a macro that produces a dropdown menu the next column over of all the items in that category. Here is the code that I have so far.
I am working with data and I am trying to identify people that have moved departments using the following criteria, a person are classed as a mover if their:-
Manager AND Dept ID have changed
OR Job Code has changed
OR Status has changed
I have been trying to come up with a formula but just can't get one to work?
I am looking to create a spreadsheet wherein the user can choose multiple drop downs in sequence and then have it output each choice to a template or empty space within the spreadsheet.
For example, lets say column 1 said what is your favourite colour and had a dropdown with two choices red and green. Column 2 says "favourite place" and had a drop down with two choices "London and USA". Then once the user chooses an answer in each column it would output these choices to notepad or a large space within the spreedsheet.
In this example it would output:
"Red London"
if the first two answers were chosen from each column. Obviously it would be a much larger scale than the simple example above.
I've attached my .xlsx file for easier understanding.
What I want to do is segment out a contact list by "market area" and "department" and pull the corresponding data with those labels from the Contact List tab into the main tab when validated by the dropdowns.
1.) The Market Area dropdown represents different offices. 2.) The Department dropdown represents departments within each office.
What I want the user to be able to do is to select the 2 dropdowns at the top and view an entire listing of the roster of those departments based on how they are labeled on the Contact List tab. I'm still a relative newbie to vlookup/hlookup so I've tried using them and encountered issues with it returning more than one value or being difficult to fill down the next series of values, etc. I simply want it to return the entire set of employees that fit the identifying dropdowns.
I need to establish a method of interleating multiple dropdowns or Form Control boxes. The purpose is to select one item and have a selection of multiple items associated with the selected item. Example:
Computer training dropdown box 1 offers selections of word, excel, powerpoint, ... If selection is Word, then dropdown box#2 offers advanced in room #15, Intermediate in room #16, Beginning in room #17. If Box 1 selection is excel box 2 selections would be different.
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
I have a list of part numbers which repeat when there are multple prices they were sold at. For each unique part number I need one median price. The list if of about 500 parts but with the various different prices the file is 3700 rows. How can i do this quickly? For each unique part number I need one median price.
