Referencing Cell Based On User Input That Corresponds To VLookup
Jun 26, 2014
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‎
View 2 Replies
ADVERTISEMENT
Jan 13, 2010
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 Replies
View Related
Apr 17, 2013
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.
View 9 Replies
View Related
Sep 28, 2009
I'm trying to write a vlookup where both the lookup value and the table_array are variables. What I'm trying to do is get a set of dependent drop down boxes to serve as the input for vlookup. The first drop down box contains a list of named ranges and the second contains a list of possible search values. If I manually input the name the function works, but if I refer to it with a cell reference or the indirect funtion it fails. For example:
View 2 Replies
View Related
Jun 15, 2008
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.
View 14 Replies
View Related
Mar 6, 2009
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.
View 7 Replies
View Related
May 2, 2009
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 Related
Feb 10, 2010
I 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).
View 2 Replies
View Related
Jun 14, 2006
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.
View 9 Replies
View Related
Dec 27, 2007
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?
View 9 Replies
View Related
Jan 8, 2009
I'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 Related
Jun 3, 2009
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.
View 3 Replies
View Related
Apr 23, 2014
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 ...
View 2 Replies
View Related
Nov 2, 2009
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.
View 6 Replies
View Related
Aug 6, 2012
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]...
View 1 Replies
View Related
Feb 22, 2013
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
View 3 Replies
View Related
Aug 21, 2008
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
View 9 Replies
View Related
Dec 22, 2009
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 Related
Jan 9, 2009
How 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 Related
Jul 2, 2014
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
Jan 8, 2009
I'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.
View 6 Replies
View Related
Apr 14, 2014
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.
View 11 Replies
View Related
Apr 29, 2014
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‎
View 2 Replies
View Related
May 24, 2012
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
View 9 Replies
View Related
Jun 21, 2012
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.
View 9 Replies
View Related
May 25, 2013
I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.
The code I have for the inserting the rows (from searching through forums) is:
Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9
[code]....
View 1 Replies
View Related
Apr 4, 2008
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.
View 9 Replies
View Related
Apr 30, 2009
I have a list of data that I'm using the index - match function to output data based on user input.
For example, I have:
1 10
2 15
3 20
So when '1' is selected another cell outputs '10'.
However what I want is for example if 1.25 is input, I want it to interpolate to spit out the correct number between 10 and 15.
View 5 Replies
View Related
Mar 26, 2014
Have been going round and round on this! I have a set of data that shows sales dollars by weeks. Currently, that data is in a Pivot table. I'd like to have a macro that selects the weeks (1-52) based on my input. For example. If I want to see weeks 5-52, I want to be able to input "5-52" somewhere and have a macro run that will select weeks 5-52 in the pivot table instead of having to go in and manually select each.
View 1 Replies
View Related
Dec 10, 2012
Date
#
Lname
Fname
[Code].....
With that said, I want to put the row number of a user in (I have a few thousand on this spreadsheet, and all the = data be automatically pulled based on the row number I put in cell B1. So I'm hoping to write in cell B1 the following - "143" (without the quotes), and the remaining cells in column B automatically pull that data based on that, so it would look like the following
Row #
143
Name:
=C143&" , "&D143&" "&E143
[Code]....
How do I write the functions to keep the columns the same, but change the row number based on the number I input?
View 3 Replies
View Related
