Looking Up Two Values From Two Sheets And Entering Third Value In Column?
Jul 22, 2014
I have two tabs BIN QTY and ORDERS. On the ORDERS tab Column A is the order number and is duplicated several times depending how many line items are on the order. The second key is Column H which is the part number which is unique per sales order. I'm trying to use these two values to look up or find the matches on the Bin Qty tab.
-The data in Column A (order tab) gets compared to Column B (BIN QTY tab)
-The second match is the Item no. Data Column H (order tab) to Column C in (BIN QTY tab).
-Once both values match it needs to pull the value from column G (BIN QTY tab) and enter it in column N (ORDERS tab).
My example is highlighted in blue with the result in green. I've tried several different index and match formulas with no luck. my Last was
=IFERROR(INDEX('BIN QTY'!$G$1:$G$7957,MATCH(ORDERS!$A1,'BIN QTY'!$B$1:$B$7957,0),
MATCH($H2,'BIN QTY'!$C$1:$C$7957,0)),0)
MY ORIGINAL SHEET IS MUCH LONGER
View 3 Replies
ADVERTISEMENT
Feb 24, 2009
My database has what I call the 'cover page'. On the sheet is a command button that will take the user to the next page. Basically what I want to do is upon opening the workbook, the user is taken to the cover page, and all the rest of the sheets remain hidden until they click a command button to 'enter' the workbook. P.S. I have some sheets that need to remain hidden though...
Of course the other way is just to have a userform that opens when the book is opened to do the same thing. Where would I put the code for the userform, in ThisWorkbook or a separate module?
View 2 Replies
View Related
Mar 18, 2014
I want to hide selected sheets and when i want to unhide them, ask for password to unhide the sheets.
View 10 Replies
View Related
Jun 16, 2014
I've already entered a variation of the formula below into 180 different columns. The only variation is where worksheet 322 is referenced. Each column references a different worksheet.
Formula:
I have at least four other tables to build of the same size, and they're each going to use this same formula with an additional IF formula housed around it. Ideally I would be able to copy the table, then run find/replace, where I could substitute = with =IF(new formula, and then run find/replace a second time and sub ))) with ))),more new formula). The problem of course is in between those steps lies a formula error preventing me from running the second step. The only alternative I can think of is to build the new formula, copy it into a word document, and run find/replace 180 times to tailor the formula for each column. That's what I did to build the first table.
View 8 Replies
View Related
Jan 8, 2014
I work in a fruit and veg business and they are using parchment and quill still, well it certainly feels that way. I have been asked by customers to provide an online order form which i will email to them and they can fill in and email back, i scanned an invoice and used OCR to upload it to excel and it has come out quite well.
Basically there are three columns of product lines and then directly to the right of each product is a column i have entitled Box & KG respectively, if the customer types a number into the box column i want it to automatically add 'Bx' to the end so this if they typed 5 in a cell in the box column it would look like this '5 Bx' (obviously without the quotes), and the same for the KG column but it would denote 5 KG instead, also i would want it to add in the space as well after the value.
View 3 Replies
View Related
Apr 26, 2006
a colleague recently used my pc and "messed it up". when i enter a value in any cells, it is divided by 100, eg, when i enter 1, it shows as 0.01.
View 2 Replies
View Related
May 19, 2013
I have a VBA module in Access that extracts data and copies to an Excel spreadsheet. Every time I run the code additional data is extracted and displayed on a chart together with previous data. When I enter values to the series collection the first axis be it X or Y is entered correctly but the second axis produces error 1004 'unable to set XValues property of the series class' If I swap the lines of code around the error switches to 'Values' (y axis)
wchart.SeriesCollection(NoCurves).Values = wsheet.Cells(9, 3 + (3 * (NoCurves - 1)))
wchart.SeriesCollection(NoCurves).XValues = wsheet.Cells(8, 3 + (3 * (NoCurves - 1)))wsheet.Cells(a, b))
wchart.SeriesCollection(NoCurves).Name = wsheet.Range(Cells(6, 2 + (3 * (NoCurves - 1))), Cells(7, 2 + (3 * (NoCurves - 1))))
View 2 Replies
View Related
Dec 18, 2008
I tried to make use of the indirect function but don't think this is the right type of formula to be used.
Essentially in column A..i have linked cell references
e.g = D2767
However, for the whole of the column I'd like to change the refs to =$D$2767, ALL in column A in one go.
I know that if you place the cursor in one cell immediately after the = sign and press F4 it automatically adds $ SIGNS TO THE CELL, which I find useful for V-lookups, but I obviously cannot do it (fill it) all the way down the column i'm using excel v7.
In addition or related how would I write an IF formula to read eg.
= IF (cell ref begins with C, then link and add PRECEDING AND FOLLOWING $ signs to all refs)?
View 9 Replies
View Related
Jan 31, 2013
I am trying to pull peoples names from 3 diferent tabs into one column in a separate tab. But if a name is shown more than once only display it once. How can I do this?
View 1 Replies
View Related
Jul 3, 2014
I have a home finance spreadsheet that I've been using for a few years and in the last couple of days it's ground to a halt. Whenever I enter a value in a cell and hit [ENTER] there is a long delay with the spinning wait icon (Windows 7) and the Excel window reports "Not responding". Eventually (3-4 minutes) the update occurs and everything is fine.
The workbook has 9 sheets on it but only one summary sheet references the others. The sheet I'm updating has about 2200 rows, 50 columns and the formulas are simple A+B-C types. Performance DOES NOT improve if I change recalculation to manual. The only VBA is some macros to perform tasks and they don't execute dynamically. Adding comments and changing formats responds immediately, it is only updating values that exhibits the problem.
I've tried replacing all formulas with their values (paste special/values) across the sheet but again this didn't work.
View 4 Replies
View Related
May 13, 2014
Dbod_BUDGET_TEMPLATE_NEW_test popup.xlsmHi,
I have created a budget template. In some instances, a discount is offered when more that x amount of products are ordered ( it varies,so I'll have to change the macro for each instance). I'd like to have a pop up box appear ( and dissapear again when pressing 'enter') that gives a notification like: a discount is applicable, select'
my cell with entered amount is "I54", and want a message if entered above 5
I tried the below and swapped places between sentence 1 and two as it doesn't seem to recognize subname, or sub start, or it just dissapears from my macro list ( although still a module): ( in the below order, it stated it expects the sub to end after sentence 1, if i remove sub popup () ( the title) ut just deletes the thing from my macro lsit,w hat is goign on!, and it just wont work)
'Sub popup()
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
[Code]....
View 1 Replies
View Related
Feb 7, 2007
I’ve done a search of the forum for a macro and found some post that seems to be about what I want to do, but unfortunately my minimal vb experience prevents me from adapting them to my requirements.
I‘ve got a workbook with three sheets; say Sheet A, Sheet B and Sheet C. I want the info in Sheet C copied to either Sheet A or B depending on the info in cells in Column A of Sheet C.
Sheet C contains customer info, there are about 9 column headings and up to 30 000 rows (Individual customers).
The cells in Column A will contain a number between 0 - 23. What I want the macro to do is, if a cell in Column A contains any of these numbers, 0, 1, 2, 6,7,8,9,10,17,19,20,21,22,23, I want that whole row copied to Sheet A and if it contains 3,4,5,11,12,13,14,15,16,18, I want it copied to Sheet B. The cells will only contain one number, never a combination.
View 3 Replies
View Related
Apr 14, 2008
I've come across the post by Dangelor but can't reply to it directly so have started a new thread quoting the code. I'm trying to select entire rows of data based on specific values in a column and then paste those rows to a new worksheet.
This code loops 10 times and creates 10 new sheets. Any chance someone could explain some of the code to me and adapt it to suit my situation?: Data will be in sheet 1 ("Data List"). I want search down the rows and if the value in column 2 is "1" copy that row to the sheet named "Heat 1", if the value is "2" then copy that row intt the sheet "Heat 2", etc.
Sub FindandCopyRows()
Dim Data As Variant
Dim DataFound() As Variant
Dim iValue As Integer
Dim j As Long
Dim i As Integer
Application. ScreenUpdating = False
For iValue = 1 To 10
With Worksheets("Main") 'change name as needed
.Select
Data = .UsedRange.Value
End With
Redim DataFound(1 To UBound(Data))
For j = 1 To UBound(Data, 1)
On Error Resume Next......................
View 2 Replies
View Related
Nov 19, 2007
the code of how do stop saving a report if the spefic cells are not filled.
For ex: In a excel sheet in we have A1,A2,A3 there should be some values else its should not save and should show an alert stating that this cell is empty....
View 9 Replies
View Related
Sep 24, 2007
I have the bellow code, I'd like enter the input on the next available row on column "A" instead than on "A2"
Dim strResponse1 As String
strResponse1 = InputBox("First Name", "Hey you !")
If strResponse1 = "" Then
MsgBox "You have chosen not to participate!", vbInformation, "What happened?"
Exit Sub
End If
Range("A2").Value = strResponse1
View 9 Replies
View Related
Oct 16, 2013
I would like to be able to output the rows in the attached spreadsheet to separate sheets on the basis of whether they have a Y or an N in the four rightmost columns - i.e. I want to make SOLO, DUO, TRIO and FULL BAND sheets.
I would ideally like these sheets to update automatically when I change the data in the main spreadsheet.
View 3 Replies
View Related
Jul 8, 2014
I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
View 3 Replies
View Related
Aug 3, 2014
I have a spread sheet that is populated via a Userform. I have arrived at a problem where I don't know how to write the Code so as to Skip Column B i.e.
Column A Column B Column C Column D Column E Column F
Serial
ID
List of Activities
Owner
Environment
Planned
Start
Planned End
1
HQ DLC0001
Campaign Plan
HQ DLC
28-Feb-14
18-Jul-14
From the text Box I fill Serial, List of Activity, Owner Environment, Planned Start, Planned End.
Column B - ID - Is a unique ID that is created via a formula.
How can I add a line to the code below so that the User form skips Column B and only places the in putted data into Columns A, C, D, E and F.
VB:
Option Explicit
Dim id As Integer, i As Integer, j As Integer, flag As Boolean
Sub GetData()
[Code] ......
View 1 Replies
View Related
Feb 5, 2009
I have a concatenated value in a column D (from three colums A, B and C). Whenever a user enters data in colums A, B and C, I would like to get a sorted list of values from column D to be populated in column E. I use the sorted values in Column E for a dropdown list in another worksheet. Could somebody help me with the macro code?
View 9 Replies
View Related
Jan 24, 2014
I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?
In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.
View 14 Replies
View Related
Jan 6, 2013
I have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:
VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range
[Code]......
View 2 Replies
View Related
Feb 26, 2012
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
View 2 Replies
View Related
Jun 5, 2014
I have set of user-form contains with Combox & 2 textbox and to generate report one cmd button
I have 3 different sheet contains report of daily activities ( Dispatch,Closed,Cancel)
If Dispatchcalls Select In Combobox1 ,Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from dispatchcalls Then Save Data Into Excel File As "Dispatchcalls".
If Closedcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Closedcalls Save Data Into Excel File As "Closedcalls".
If Cancelcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Cancelcalls Save Data Into Excel File As "Cancelcalls".
"C:UsersmaniDesktopNew folderLenvo_ReportsONSITE CasesVlokupuf" This is path i stored existing 3 file dispath,closed,cancel
View 3 Replies
View Related
Aug 22, 2013
[URL]
I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.
View 2 Replies
View Related
Oct 1, 2013
There should be a formula to extract the largest values and its corresponding values. For example, if column
Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100
The desired outcome
Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100
The second goal is extracting the top 3 largest values using a formula.
Sample file is attached for your convenience. Desired outcome is in GREEN.
View 6 Replies
View Related
Dec 5, 2013
Below the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
View 2 Replies
View Related
Dec 24, 2013
I have two columns in excel, col A and col B.What I would want to achieve is following:
Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3
Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).
[URL]
View 5 Replies
View Related
Jun 11, 2014
Here is a table
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail
[Code] .....
Here is some context
Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).
In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4
[Code] ....
What I need is a formula that will produce this result.
View 5 Replies
View Related
Jan 31, 2008
I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
View 9 Replies
View Related
Feb 21, 2013
I am trying to sum certain values in one column based on the values of another column. I made a few tables, generated some random numbers, and then I started writing up an if-then statement to see if I could get what I am trying to do to work. However, I was having difficulty getting the program to run, because I was declaring more than one variable (I think).
So for instance, I want cells in column G to sum forces in column C when a joint in column E matches a joint in column A. Similarly, I am trying to do the same thing for columns F and B.
I have attached a picture, which shows what I would like for the program to do automatically (rather than me doing it all manually)! I didn't include the code I wrote, because it was so minimal
Attachment 51660
View 3 Replies
View Related
