Free Microsoft Excel 2013 Quick Reference

Random option selection based on variable input

I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.

It is probably easier to look at the attached sheet and the previous discussion to see what I mean.

Previous discussion:

I would like to create a spread sheet for forecast sales income based on variable inputs of the sales amount.
I am calculating a ship mo - Order mo + 62 days
I am calculating a Paid mo - ship mo + 62 days
Terms - 40% order mo
- 50% ship mo
- 10% 60 days
The inputs will be down the left side by customer order
The months of the year will be accross the top.

Input Calc
Cust - $ - Order | Ship - Paid - Jan - Feb -Mar Apr- May
Name- 100- Jan-08 | Mar-08- May-08- 40 - 0 - 50 0 10

What I am try to calculate is
If the calender month on the top row of this column matches the order mo then 40% * sales price, else if calender mo equals ship mo then 50% * sales price, if calender mo equals Paid mo then Sales * 10%, else nothing. So in effect the sales price would appear according to the terms accross the spread sheet.

I have not performed a match using a "month/year" before and wonder if there is a special way to do it?

I am looking for a way to create a random selection based on odds. It would be similar to the RANDBETWEEN function, but in this case I specifically want one of the three values to be selected randomly in cell C2 based on the odds given.


I have some data in sheet1 with 10 columns and 5000 rows.
I want to filter the data with 2 criterios.

When I go to 4th column and Click custom filter, I will give one criteria and select "or" and give another criteria. SO I will get the result in sheet1, I need to copy the data and paste the same in sheet 2 with the header.

The problem is, I need to filter more than 20 times giving the criteria and copy the result and paste in sheet 2 one after the other.

So i need a macro to solve this time consuming work.

I just paste the two criteria either in a text box or some cells and run the macro. the macro has to filter the data in sheet1 based on my input.( that is criteria1 or criteria 2) and the result should be pasted in sheet2 with the headers.
Again I delete the values in my input cell, and paste the new values, and run the macro, that result should be pasted after the first result, with the header. (would be great if that is pasted leaving one row above, that is if the first result is pasted in sheet 2 till 10th Row, then the send result should be pasted in 12th row and so on..

the similar kind of question with some changes, I posted in the below link with

I guess bit complecated, I would appreciate If I get helps


ok...first things first..i'm new to all of this...
i need help!...looking for some code that would export certain info to a new
worksheet, based on USER INPUT...

my db is setup as so - i have a SOURCE WORKBOOK and a SUMMARY WORKBOOK...

week / sales pro / revenue req'd / revenue achieved / account name /
product ....
1 j. doe 1000.00 2000.00 abc
company widgets
2 j. doe 2000.00 1000.00 xyz
company hammers
to 13

i've NAMED each ROW to reflect the week (ie...Week1, Week2, Week3, Week4,
Week5, Week6, Week13)

i have some code (that i'm going to use from a previous project i worked on)
that enables the user, at the click of a button to EXPORT the data range
that "I" defined into a new workbook...

now, however, i want the user to be able to export info for the WEEK
SPECIFIED BY THEM...for example export info for Week1 only onto new

not quite sure how to go about this...

i've already NAMED the ranges so i was thinking perhaps of adding a "prompt"
that would ask the user "Which Week Do You Want to Export?"...then perhaps
have a dropdown list (containing the names of my predefined weekly ranges
ie..Week1, Week2, etc..)

then i'd have to write code to say something like: if answer is Week1 then
Range(Week1).Select, if Week2 then Range(Week2).Select

i THINK i'm on the right track?

appreciate your assistance!

Hi Guys,

I have an excel spreadsheet where i have two comboboxes. However based
on the input from 1 combobox i wish to have different options available
in the second combobox. Is this at all possible in excel??

Any help would be greatly appreciated,


afmullane's Profile:
View this thread:

Hi Guys,

I have an excel spreadsheet where i have two comboboxes. However based on the input from 1 combobox i wish to have different options available in the second combobox. Is this at all possible in excel??

Any help would be greatly appreciated,


Hi, I have a macro that selects an entire column based on an input box.
Macro2 Macro
    Dim UserReply1 As String
    UserReply1 = InputBox("Enter column letter to hard code")

    Columns(UserReply1 & ":" & UserReply1).Select
I would like to change it so that it selects the entire column based on the value of 1 cell. ie ( Set up tab, cell A1). And then select that column for every worksheet in my workbook ( there's like 20 ). I appreciate any help. thanks.

I would like to be able to change what is selected in my slicer based on a particular cell value.

For example, if my slicer contains the names of hundreds of sales reps, I would like to be able to type the name "John Brown" into cell A3 and have this automatically select "John Brown" in the slicer.

Thank you very much!


Hi friends,

I have a spreadsheet in which I enter values date wise every day. But I need to view values in other dates. Now what I want it go to a particular column whose header is the date, based on an input in some standard cell, say A1.

For example,

I wish to select the column whose header is March 9, 2011. I will enter this data in some cell and then this entire column (carrying its hearder as March 9, 2011) should get automatically selected or atleast the header should automatically become active.

Can anybody help?

I’m hoping someone could help.

I have data on airline routes – origin, destination and destination region.
For example, origin might be London, one of the destinations is Mumbai and
destination region is Asia. I have created unique origin that user can
select. I would like to create a dynamic report that list the destination and
its region based on user input.

Region Asia Asia EMEA US
Destination Mumbai Malaysia Spain Texas
Data(miles) 10000 15000 2000 20000

There will be instances where a region will not have any destination. The
order would be Asia, EMEA and US for region. Origin, destination and
destination region are on a same data sheet.

I very much prefer to use to use logic/formula instead of VBA as I’m really
bad at it. Any suggestions?

Psig Inlet Temperature
90 95 100 105 110 115 120

60 1.156 1.289 1.451 1.643 1.915 2.296 2.793
70 1.022 1.140 1.283 1.453 1.694 2.030 2.470
80 0.929 1.039 1.166 1.320 1.539 1.844 2.244
90 0.855 0.954 1.074 1.216 1.417 1.699 2.067
100 0.797 0.888 1.000 1.132 1.320 1.582 1.925
110 0.742 0.828 0.932 1.055 1.230 1.474 1.793
120 0.700 0.775 0.872 0.987 1.151 1.379 1.678
130 0.700 0.737 0.829 0.939 1.095 1.312 1.596
140 0.700 0.707 0.796 1.901 1.047 1.255 1.527
150 0.700 0.700 0.768 0.870 1.012 1.213 1.476
160 0.700 0.700 0.742 0.841 0.979 1.174 1.428
170 0.700 0.700 0.717 0.812 0.947 1.135 1.380
180 0.700 0.700 0.700 0.784 0.913 1.095 1.332
190 0.700 0.700 0.700 0.755 0.880 1.055 1.283
200 0.700 0.700 0.700 0.728 0.849 1.018 1.238
210 0.700 0.700 0.700 0.704 0.820 0.983 1.196
220 0.700 0.700 0.700 0.700 0.796 0.955 1.161
230 0.700 0.700 0.700 0.700 0.772 0.925 1.126
240 0.700 0.700 0.700 0.700 0.750 0.899 1.094
250 0.700 0.700 0.700 0.700 0.728 0.873 1.062
260 0.700 0.700 0.700 0.700 0.709 0.849 1.033
270 0.700 0.700 0.700 0.700 0.700 0.825 1.004
>280 0.700 0.700 0.700 0.700 0.700 0.802 0.976

I need to be able to retrieve the corresponding data based on two input
values, the results and input values are in one worksheet (1) and the data
(as above) in another(2). For example : i need to find the corresponding
value for a pressure of 70psig at a temperature of 100oF. The result should
be 1.283.
This result should be based on inputs in two cells(C8(pressure) &
C9(temperature) in worksheet 1)

I need to have users input a starting week number in cell A1 and an ending
week number in cell B1. In cell C1, I need to compute the average volume from
the first week, based on the variable the user input, through the last week,
also based on user input.

If the user values "2" in A1, and "4" in B1, via VLOOKUP(A1,D1:E9,2), 30 is
returned, which resides in cell E3. Via VLOOKUP(B1,D1:E9,2), 50 is returned,
which resides in cell E5.

Column D Column E
Row 1 Week Volume
Row 2 1 20
Row 3 2 30
Row 4 3 40
Row 5 4 50
Row 6 5 60
Row 7 6 70
Row 8 7 80
Row 9 8 90

Need: To average the values within the range E3:E5

I tried the following: AVERAGE(VLOOKUP(A1,D1:E9,2),VLOOKUP(B1,D1:E9,2)), but
it correctly averages the two returned numbers, but I need it to average the
identified range of numbers.

Any and all help would be appreciated. thanks in advance!!!!

I am working with Excel 2007. I have created code that will get the users input. The input is for three characteristics (risk, cost, and performance. Based on the input, titles for a chart will be created. For example if the user enters five for the number of risk characteristics, the word risk would be entered starting in cell B8. I need help coding a way to merge and center this cell with the next five cells (i.e. cells B8 through F8). The user then enters the number of cost characteristics. The header would be created in the cell after the risk header (i.e. in the example cell G8). How do I go about coding this?

Sub Data_Entry()
'Declare Variables
Dim risk As String
Dim cost As String
Dim performance As String

'Ask User for Number of Attributes
risk = InputBox("Enter the number of risk attributes:")
cost = InputBox("Enter the number of cost attributes:")
performance = InputBox("Enter the number of performance attributes:")

'Create Chart
Worksheets("Data Entry").Range("B8") = "Risk"

'Prompt User to Enter the Data
MsgBox "Please enter the appropriate data values"

'Enter Weight Factors

End Sub

I have multiple inputs and multiple outputs with a combined output and based on the input value's sigfigs I need to make the output values have the same sigfigs all the while not rounding before computing the cumulative output totals.

In the file the Data page is for the inputs and the Results page for outputs. On the Results page all the grey squares are input's loads along with row 19 and column D for distances. On the Results page are the stress outputs after each number gets computed for each load value (none of the values on the Results page are supposed to be manually changed except the drop-down depth cell).

Each grey square on the Results page needs to be formatted to the correct number of significant figures based on the grey square on the Results page with the lowest number of sigfigs. In the case of random numbers I put into the sheet the lowest number of sigfigs is 1 because of the 200 in Q24 on the Data page so I need all the grey squares on the Results page to be formatted to 1 significant figure visually.

BUT in cell W17 on the Results page I need as many sigfigs as excel can compute for a more accurate cumulative answer. So I either need to find a way that the Results grey squares can hold their calculated values for computation but show the sigfig value to the user or have the grey squares rounded to the correct sigfigs and have to run a very, very long calculation in cell W17 instead of merely summing the grey squares.

I had thought that conditional formatting might do it but that seemed limited to merely coloring. VBA solutions would need to be automatic if someone decides to try that route.

Hi, I have simple question...I'm trying create little script where I will be able filter based on user input. For example I have column L (Order Date), when I run macro input box will show up so user can add date. When user type in date, all data that are equal or less then user input will be deleted.

So if, Filter all date that are less or equal ##/##/##, I got following code but when I run it it does not filter my data. Can anybody tell me what I'm doing wrong in here or can somebody tell how would I do this?

HTML Code: 
'Get the filter's criteria from the user
        sDate = InputBox("Enter the Condition to Filter ##/##/##")
'Filter the data based on the user's input
        Selection.AutoFilter field:=12, Criteria1:="<=" & CDate(sDate), Operator:=xlAnd
Thanks a lot

Hi all,

I've to sheets [Sheet1 & Sheet3] in the first sheet I used a macro to copy rows to sheet3 based on cell input from dropdown list. The code works, so that whenever they select [High] rating, that whole row will be copied to the other sheer.

The only problem I have is that whenever I go back and change rating from High to Low, Medium or delete the rating, that change doesn’t reflect on Sheet3. I need whenever the change the rating the macro should go back and delete the equivalent row from sheet3 that was copied there previously.

This is the macro I used:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("D:D")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)
If LCase(Target.Value) = "high" Then
With Target.EntireRow
.Copy Sheets("Sheet3").Cells(Rows.Count, "D").End(xlUp).Offset(1, -3)
End With
End If
End Sub I hope I made it clear. attached is an example of the workbook.

I'm trying to prepare a macro that will run the sum formula for a column based on variable range. The sum formulas need to repeat in Column K everytime the 2011 is shown. See data below:

Column K

This is what I have come up with, which works but gives me an error because the object is variable. Can anyone tell me how to fix the following macro please?

'Sum amount of each transaction
    For a = 1 To RC
        If ActiveCell([K2]).Select Then
        Selection.Find(what:="2011", after:=ActiveCell, LookIn:=xlFormulas, lookat:=xlPart, 
        searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
        Selection.Formula = "=-sum(K2:" & Format(ActiveCell.Row - 1, "K#") & ")"
        End If
    Next a

Hi, I'm an amateur programmer and a noob to Excel, so I'm hoping you can help me out.

I need set some cells in my sheet based on user input in a cell (before program runs). Basically, if user chooses 1, B1:B5 needs to match data from H6:H10. If the user chooses 2, the B1:B5 needs to use data from I6:1:10, etc. How can I change which column the data is being pulled from?

Thanks in advance,


Hi All
Getting into this VBA but am stuck using msg box to create a new column based on user input
I have a table with column D & E containing variable data, these will be numeric values,
I am trying to create a msg box that will ask" Do you need to change any VLAN numbers" if the answer is yes, it will ask for the number then look in column D and where it finds that number it will put the new user input number into a new column at the end of the table in the same row. If the user input is no then it will juts put the existing numbers in column D into the last column with a header of NEW VLAN and then loop to a new msg box this time asking "Do you want to change any Voice VLAN numbers" and repeat the above against column E putting the new info into the last column in the same row number with the column header changed to New Voice VLAN.
The last thing that would be useful is if the changes could have their cells coloured red.

Thanks for your excellent help in advanced


My attempted calculation is this:

If I input any FX pair (Column B) without ???JPY in it, then the formula in Column K produces the correct result. Formula is :

eg: If Column B = EURUSD pairs and the like:


This formula will produce a +ve or -ve result in points (PIPS) based on the values of E3 and F3.
(eg. E3 = 1.2800 & F3 = 1.2750. If C3 = L then the result is -50. Conversely if C3 = S then the result is 50.)

When I input the JPY pairs in Column B, I need the formula to calculate the same way but multiply by 100 (not 10,000).
This is because all non JPY cross pairs have 4 decimal places and JPY cross pairs have 2 decimal places. (eg. 1 PIP in EURUSD = .0001, 1 PIP in USDJPY = .01).....

So if Column B = USDJPY the formula would need to be :


Have tried many combination's of the IF function but am now starting to think this is not quite the right formula for this situation.

Attached is a sample of what I am trying to achieve. The formula would need to incorporate any/all variations in currency pairs as listed in the attached sample.

Any help would be greatly appreciated.


Ps: I have attached versions from Excel 2003 & 2007 (same example)

Ladies and Gents,

I need some help figuring out how I can sum a range of cells closed workbooks based on information inputted in an open book.

Essentially I am looking up data in tables in the closed workbooks, the data can be index/matched fairly easily however I am needing to sum 50 or more cells based on what the user inputs.

The inputs are time (weeks - going across) and SKU - (going down) the actual data is sales information. What the end result becomes is for product X during weeks 1-20 it sold Y amount of product

Currently I am using this formula but I found that when the workbook is closed it no longer works

=IFERROR(SUM(OFFSET(INDEX('[Estimate IBP 2011 Plan RTEC.xls]->Ship $'!$B$4:$BC$173,MATCH($E13,'[Estimate IBP 2011 Plan RTEC.xls]->Ship $'!$A$4:$A$173,0),MATCH($B$10,'[Estimate IBP 2011 Plan RTEC.xls]->Ship $'!$B$3:$BC$3,0)),0,0,1,$B$13)),0)

I guess Offsets don't work in closed books. I know there is an answer to this but I can't seem to figure it out...I appreciate your help

Hi Folks

Please help me to update the time sheet data based on userform input? I have attached a file of what I'm trying to accomplish.

Thanks in advance for your expertise!


I am creating a financial projection that adds new customers over time with an average purchase for each new customer. What I am trying to do is add a ramp up rate for each new client that can be adjusted by changing a cell on my input sheet. The model is broken out so that there is a different month in each column with new customers being added each month.

This is an example of what I am trying to achieve at a 5 month ramp up rate:

Jan 09 -> 2 New Customers -> each with an average purchase of $500 when they are using the service fully

Jan09 would show 20%(Ramp rate)*$500(total purchase)*2(New Customers)=$200

Feb09 ->1 New Customer ->$500 Average purchase when service is being fully utilized

Feb09 would show -> 40%*500*2 (Jan09 new cust) + 20%*500*1 = $500

The problem I am having with this is that I need the ramp up time to be adjustable based on an input on the input tab. Meaning that if I switch the ramp up time period to 4 months I need the sales to grow by 25% each month for 4 months for each new customer.

I also need to cap the sales at $500 for each new customer after they have reached the full ramp up time period. I cannot find a way to make this work using what I know about excel.

I was wondering if there was a formula to achieve this or if there is an easy way to create a separate sheet and pull the data from it onto the summary tab.

Thank you in advance for any help you can provide.

Hey all,

So I want to display only a certain number of rows in a table based upon how many the user needs (the user will be inputting information into this table, but that's not really relevant to the problem). I have a cell designated for them to input their value, and then I want the table to automatically adjust based on this input.

I've tried feverishly to do this with lists, custom filters, and advanced filters but have had no luck. For some reason you can't define criteria for an advanced filter using a cell directly, or at least I haven't figured it out. Any help would be greatly appreciated!

PS - I've been exposed to VBA, but haven't had much experience with it. I'm actually hoping that there is an easy way to do this in VBA that my lack of experience has prevented me from figuring out. That is, if it can't be done without messing with code.