Cell A5 has a Yes_No pull-down list for data validation. In cell B5, I want to display the value 0.002 only if A5 is "Yes". If A5 is "No", I want the user to be able to enter a number into the B5 cell without destroying the formula.
View 4 RepliesI'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?
View 2 Replies View RelatedI'm trying to create a formula similar to this:
=Calculations!(Indirect("N"&A2)-Indirect("AB"&A2)+Indirect("AA"&A2)
The idea is that the user will enter a value in cell A2. That number will determine the appropriate rows in the formula above.
I have a cell B10 which contains a formula.
I would like a macro which copies this formula across the same row.
However, the macro should prompt the user to select a range.
Based on the range selected and in particular the columns in this selected range, I would like the macro to copy the formula only for the columns specified in the selected range.
I would like a cell to have user input. If there is no input then the cell should reference a value in another cell. My strategy was to use a formula in another cell to check to see if the user input cell was empty. If it was empty then the formula propagated the cell. If it had user inputted data then it stayed like it was.
Here is an example:
Begin Invent
1000
Gallons
End Invent
User input or
formula here
Gallons
Received
Gallons
Used
Gallons
I want the user to be able to input a value for the End Invent cell right below 1000. If there is no value I want that same cell to mirror the cell above it with the 1000 in it.
I have a table with 4 radio buttons that assume certain data in three cells when they are selected. I have used the IF command to getthe information to appear in these boxes when the cooresponding radio button is selected.
I want the user to be able to either leave the selection as it is presented to them, have the option to change the data if it doesn't fit their scenerio.
Example: button 1 "Electric" makes the data "$0.12", "$per kWh", & "100%" appear in the three cells. Each button produces different data in these boxes, however if when button 1 is selected, the user's electric cost per kWh is $0.10, I want them to be able to type that in without changing my preset default.
Cell A1 is going to be used as my true/false value to trigger the formula. Cell A2 must allow user input to calculate "what if" scenarios however, if cell A1 value = true then cell A2 = 0.
I think I need to place my formula into A3 to allow the user their input to A2 without overwriting my formula. Problem is if I place the = sign in front of IF, the formula does not work the way I need it to. If I don't place the = sign in front of IF excel just treats it as a text entry.
=IF(A1="Actual",A2=0)
IF(A1="Actual",A2=0)
I have inherited this workbook and am just trying to automate the functionality to streamline.
So my issue this time is that I have a bunch of columns that correspond to a month. If the user wants to view data from that month column then they have to link to that cell specifically (the worksheet being used is different than the one in which the month columns are)
My goal was to create a single cell in which the user typed in a month abbreviation such as jan, feb, mar.. so on and so forth. From there they correspond to a vlookup table which tells you which month is in what column (because that part never changes) so jan is in column c and feb is in column d... continuing until december is in column n. So the vlookup corresponds to the table and that works fine, type jan into the cell and the vlookup returns c. So i think this will work.
My problem is that in the cell I am typing this in I want it to be a part of a whole cell reference such as c5 or d7 where the 5 or 7 is a constant (I have that part down) however the reference is to another sheet. So in sheet 2 I want to reference sheet 1 i.e.
Formula: [Code].....
<-- is the desired return.
Right now (in sheet 2) I have
Formula: [Select Code] .....
Which returns the desired d5 but as text and not referencing sheet one. I tried
Formula: [Code]....
But this makes the vlookup malfunction (i think it starts looking in sheet 1 for the month cell but it is not and can not be there.
SO how do i force a vlookup to turn into a cell reference, and how do i make that cell reference come from a different worksheet without causing the vlookup to malfunction.
Attached File : examples.xlsx‎
At the top of my spreadsheet, a day of the week is selected via Dropdown.
Below that in a table, the days of the week are split into hourly slots.
I want to write a macro to jump to the first cell in the bottom table containing the day selected in top cell.
I have created a macro that processes through information for the creation of checks.
I would like to have a way for the user to input a check number, and Excel to take that number, drop it into cell A2, then increment it and drop the next number in A3, and so on until all of the checks have been numbered. The process should stop at such a point as there is no more text in column B.
Here is an example. Let's say I have 35 checks to write. The first check number is 200. I would want a user box to pop up which asks me for the first check number. I would enter 200. Then the process would place the number 200 in cell A2 (A1 is part of the header information for the checks.) In cell A3, the number 201 would be placed. The final check number would drop into cell A36. No check numbers would appear in cells A37 and greater because cell B37 is empty, signifying that there is no further check data.
1) Allow user input of a number between 1 and 999
2) Search column three and delete any rows that don't match this number.
I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.
I have a range of cells that I want to sum based on a range inputed by the user. The range begins with cell c27 and ends with cell au27. I want the user to be asked the beginning cell and ending cell of the range they want to sum and then output the answer.
View 4 Replies View RelatedI have one sheet to act as a splash page for user input. The second sheet stores all relevant data. My goal is for the user to input a numerical value in a cell (or input box) and then click a submit button. The code for the macro should look at a cell on sheet 1 which displays the primary key of the row in the table on sheet
2. Based on that primary key, it should look to sheet 2 and then insert the value into the table in the correct row and column. The column headings are the days of the week, and the specific day the user is concerned with is also displayed on sheet 1.
Sheet 1:
A1 = Day of the week
A2 = Unique primary key
A3 = user input value
Sheet 2:
table
Rows=primary key (001 - 999)
Columns = days of the week (Monday - Friday)
example:
user inputs '5' on sheet 1 with 'Tuesday' and '007' selected in their respective cells. I would then like 5 to be copied to B7 (row 7 for 007 and column B for Tuesday).
I am trying to move (archive) data from one sheet to another sheet based on a date range provided by the user’s input. If the date on sheet “list” in the cells in range R9:R10000 fall within that date range the following takes place. The rows that meet the date range per the user’s input in the range of R9:R1000 are copied and just the values are pasted into the next empty row starting with row 7 on the “report” worksheet. The cells that are copied on the “List” worksheet are NOT deleted but the contents are cleared. The reason for this is because there are formulas/links in those cells.
I have attached a worksheet example and have some VBA code but it does not work.
I have managed to write a macro that almost does what I require, but it inserts the copied row at the end and I want it to insert before the row it's just copied. Any ideas how I can modify the code to do this?
Without using VBA, how can i generate strings say
x1
x2
x3
x4
x5
assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.
I have an excel sheet that I've automated for my shop ... Basically it copies a set of rows from one sheet, asks for which cell you want to start the paste at, then pastes it... Here's what I have so far:
[Code] .....
So I tried to use a variation of strReturn to indicate number of rows wanted but that didn't work ... Basically I'm going to create the "data" to include 50 rows, but have it selectable for the user to say they only want 5, 7 or 29 rows ...
What I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required)
I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.
I need to filter data based on criteria based on user input form that is in another workbook. For example, I have a list of departments, I want to create a userform for the user to enter department number and then have excel filter only the data belonging to that department. The person will then update the department records and save the file.
Here is partial code I have been working on but having difficulties:
Dim FilterCriteria
Dim CurrentFileName As String
'Get the current file's name
CurrentFileName = ActiveWorkbook.Name 'how do I call another workbook?
[Code]...
I am trying to find a value in an array based on partial input from a user.
The user will enter the last four digits of a number.
Then the formula should find the number in a list and return that complete number
I will explain using example.
Here is the list of numbers (array):
3003280197
3003283233
3003625456
User input= 3233
formula output = 3003283233
I am using the following piece of code to delete unwanted rows from a worksheet:
Sub DeleteDates()
FinalRow = Cells(65536, 3).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 3).Value Like "*2007*"
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub
What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted.
However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007.
My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range?
Some important info: All of the dates are in column C, in the format mmm yyyy
I would only need to narrow down to a month - so for example mar 2008 - jun 2008
Excel 2003, I have a list of products with corresponding prices located on a hidden worksheet. I have a spot on another worksheet where users can select a certain product and next to that cell there is a column for price. What I want excel to do is auto-populate the price cell based on what product the user selects. For instance, if the user selects OD2000N, then I want the price to auto-populate with the corresponding price that is found on the hidden sheet. Like I said before, I know this has probably been answered, maybe even mulitple times but I can't seem to find the appropriate thread.
View 9 Replies View RelatedHow would I reorder columns & tabs based on a form where the user can put them into order that they would like the columns? I forsee some type of form where the user can dsignate the order and then start a macro that will sort both the columns and tabs.
View 5 Replies View RelatedTwo formulas which I need in Cells E74 and E75.
Basically the formulas will depend whether the entry in D73 are Yes or No
in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70
in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70
Excel version 2010
I do data entry for a driveway company. I get addresses and phone numbers, and then call the people to offer services. I keep track of my calls on the spreadsheet, color coded for my results, green for leads, yellow for voicemail/no answer, and red for not interested.
I have formulas in cells M2 through M8 to give me my overall stats for the calls I have made, it filters them by color for leads, missed calls, and rejections, and a couple other stats (mostly for my own curiosity)
What I'm trying to add is the cells L10 to M13, I want to be able to input a date, for instance 8/15, and have it run the same stats, just for that day essentially. So, I need it to search the last 2 columns in the table for the text that I input into cell M10, and then run the color based formula on those cells, and total them up in the appropriate cells, M11, M12, and M13.
The 2 Stats tables are the same at the moment in the sample, as I only included one particular street that I have mapped, my actual table is nearly 3000 rows.
I'm trying to come up with a histogram chart that adjusts Bin Size based on user input. I've come up with the formulas for Bins and Frequency, but am struggling when it comes to charting the data to dynamically update when the user changes Bin Size. How to make the x-axis dynamically adjust with the number of Bins.
View 5 Replies View Related I need to copy and paste the specific dates from the specific city that the user will insert in the "dash" sheet.
Basically, I need the macro to read the city and range of dates that the user will type into the "dash" sheet and copy the columns "AA" to "AD" from rows specified by the dates input. Once it has been copied, it has to be special pasted just the value onto the sheet "1" beginning at location B2.
the the data will not be available as it is vlookups to another excel sheet that i have not provided. I have deleted the data, but you can pretty much put 1's everywhere.
I have here an example of how I might extract data from a source workbook and paste it onto a template workbook, using autofilter to find the rows that are needed. It is currently set-up to take rows that match "AREA 1" or "AREA 2" in column A, along with a number of other criteria in other columns.
What I want to do is add into this macro, a way for the user to be able to choose the filter options will be used for column A of the source workbook. So if they want only "AREA 1", they can choose just that, or if they want "AREA 2", or perhaps both "AREA 1" & "AREA 2", or further additional options. They need to be able to specify just one, multiple, or all of the filter options with regards to column A of the source data.
In effect what I need is a user input box or form with a dropdown menu that will give the filter choices based on column A of the source data, and then for the code that copies the data across, to do so based on which choices the user makes in the filter menu of the input box.
I have uploaded examples of the source data workbook and the target template workbook. The macro exists within the template workbook.
[Code] .....
How it may be possible for the user to be able to specify, one, multiple, or all available filter options for column A of the source data, via a user input box or form.
Attached Files :
Sales Analysis Template1.xlsm‎
SOURCEDATAEXAMPLE.xls‎
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6
I've been going round in circles with this for hours and have got no-where. I 'm trying to get Excel to paste in a set pattern of data based on an input cell.
In row 1 I have months starting in cell B1. B1 = April 12, C1 = May 12, D1 = June 12, and so on
The user would enter the data in column A, starting in row 2
If the date in A2 = May 12 then Excel would enter 10 in cell C2, 20 in cell D2 and 30 in cell E2. If the date in A2 is changed to June 12, then the previous data would be cleared and Excel would enter 10 in E2, 20 in F2 and 30 in G2.
Date would be entered in A3, A4, A5 etc and I would want it to post the same fixed data depending on the date the user enters in teh relevant row.
I've tried IF statements and also some code but can't even get near it working.