Lookup Numbers In Sheet And Assign It's Cell Reference
I have a worksheet(Shed) and the range where numbers will be entered are B3:E46. In another sheet(Location) I have a in column A "Shift #", B "Shift Bus #" C "Location". what I am attempting to do is when a bus number is entered into Shed! ie.. b3=900, d15=350 etc.... Location! would lookup ie.900 in the Shed! and match it to Location! (column B) and from there provide the cell ref in column C. So I could then print Location! that would give me in Shift # order where each Bus # the shift is assigned to.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Assign External Cell Reference To Variable In VBA?
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
View Replies!
View Related
Can't Assign Shortcut To Macro: 'Options...' Button Disabled
I'm trying to assign a shortcut to a macro I wrote in VB. However, when I go to Tools->Macros->Macros, none of my macros (whether coded in VB or recorded) have the "Options..." button enabled, so I can't assign the macro. This happens whether I use Excel 2003 or Excel 2007. The file is not readonly and I have tried the various levels of macro security. I have VBA installed.
View Replies!
View Related
Assign Multiple Cell's Values To A Variable
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
View Replies!
View Related
Assign Password To A Hidden Sheet, Only 1 Cell Populated
This is my input for password prompt: Sub CommandButton1_Click() Dim wSheet As Worksheet On Error GoTo ErrHandler Currently, my excel is protected with a password "PSWD" when users open the file. In my vba script, I have a function that can unprotect my excel without any password input box to the users. This method is to unprotect the file and allow users to do any formating columns and rows tasks, such as add or delete cells. After users finish their job, the file will be protected back. The function is such as below: Dim wSheet As Worksheet For Each wSheet In Worksheets Application.ScreenUpdating = False If wSheet.ProtectContents = True Then wSheet.Unprotect Password:="PSWD" Else My questions are: 1. How I can create a hidden sheet that has one cell populated only? 2. How can I assign the password that the user enters at the "TextBox1" (first code) to a cell of of the hidden sheet? Do I have to modify the first code? 3. For the second code, how am I going to assign the value to at the wSheet.Unprotect/Protect Password:="..." with whatever the user has previously chosen as a password that refers at the hidden sheet?
View Replies!
View Related
Vlookup:keep Cell Reference As Lookup Criteria
See attached a sample from a larger workbook I am working on. What i would like to do is in the Rec tab column G, keep the references from columns L & M as the Table Array and Column Index Number. I have =VLOOKUP(F:F,L:L,M:M,0), I would like to have =VLOOKUP(F:F,whatever tab reference is in column L as table array,whatever number is in column M as index number,0). I have included what I would like the data to look like in coulmn H.
View Replies!
View Related
Return Cell Reference From Multi-Column Lookup
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached) it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet! i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all). any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help! should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
View Replies!
View Related
Pull A Reference Number And Assign It To All Records In A Particular Section
I need to pull a reference number and assign it to all records in a particular section. The code I recorded keeps going back to the first section. Also would like it to repeat until all records have the reference number attached. 06010-100Beginning Balance07/09/2004AD06011-10007/23/2004AD06011-10008/06/2004AD06011-100 06011-100Beginning Balance07/09/2004AD07/23/2004AD08/06/2004AD Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/14/2008 by acantu ' ' Selection.Copy Selection.End(xlDown).Select Selection.End(xlDown).Select Range("C7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B7").Select Selection.End(xlDown).Select Range("C63").Select Range(Selection, Selection.End(xlUp)).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.End(xlToLeft).Select Selection.End(xlDown).Select Selection.End(xlDown).Select Selection.End(xlDown).Select End Sub
View Replies!
View Related
Reference Cell In Macro Using Row & Column Numbers From Cell
I'm trying to reference a location on a spreadsheet in a macro by using the values in two cells (the idea is to then paste to this location): D2 is 17 (the row) B4 is 2 (the column) therefore the location is B17 (R17C2), but can I get this to work...? The values in these cells can change depending on selections made which is why I want to use the cell references rather than the absolute!
View Replies!
View Related
Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters. Samples of what I've tried: =IF(LEFT(L23,1)=P) =IF(MID(L23,1,1)=P) etc. I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?
View Replies!
View Related
How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E. What I am looking for 1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that. 2) I would like this validation/formula to be applied to all the cells in a particular column. 3) Nice-to-have. If the user enters a lower case letter, then I would like to convert to upper case.
View Replies!
View Related
Assign Numbers To Text Words
I would like to know if I can do a VBA code that takes one formule that is showed as text in one cell (eg '=wood+plastic) to the next cell to really use it... I mean, that one time we define de "wood" valeu and plastic valeu will realize the calcul.
View Replies!
View Related
How To Lookup Row # Based On Content Of Another Column's Cell
I have a 3 column table with 20 rows. The first two columns contain travel expense data. The cells in the third column are blank exept for whichever cell I enter an "x" in. Please let me what formula or worksheet function do I use to calculate the travel expenses based on the data in the first two columns that correspond to the row of the cell with an "x" in the third column?
View Replies!
View Related
Reference A Sheet Name In A Cell
Is it possible to have a sheetname reference in a cell? A1 = "Exhibit A" and have a formula as such? =IF($A4"",(IF(ISNUMBER(MATCH($D$2,' Exhibit A'!38:38,0)),"x","")),"") where the sheetname in bold is pulled in by a reference to cell A1?
View Replies!
View Related
Sheet And Cell Reference
I have an IF statment that looks at a sheet but I would like it to look at certain sheets I.E. sheet 2,4,5,6. Sub SelectPrintArea2() For Each ws In Worksheets If Range("Sheet4Q5").Value > 0 Then Range("A1:AA47").Select ws.PageSetup.PrintArea = "$A$1:$AA$47" ElseIf Range("C5").Value > 0 Then Range("A1:M47").Select ws.PageSetup.PrintArea = "$A$1:$M$47" End If Next ws End Sub But it will only look at the sheet it is in and not any of the other sheets. I've tried to tell it which sheets but I just get errors.
View Replies!
View Related
Use A Cell/variable As A Sheet Reference?
I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells. For example... instead of LEMONS!a1 and LIMES!B2 I would like to use REF1!a1 and REF2!B2 where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes' How can I do this?
View Replies!
View Related
Cell As Reference Anchor To Another Sheet
I have a problem how to do the next: A1 cell contains title of some test The next cells on bottom line (B2:B10) contains the results of the this test. On another Sheet i want to analyze those results, thus I need the results from Sheet1 be copeid to sheet2. I want just to indicate the test name (A1 location) and the rest of the cells will be copeid automatically(relative reference, relatively to A1).
View Replies!
View Related
Cell Reference Or Defined Name For A Sheet
I'd like to be able to refer to a cell or local scope defined name within my sheet that references another sheet, or accomplish the same functionality in some way. For example, if I have a sheet MyInformation and then another sheet MyPrintedInformation, where the printed information sheet shows MyInformation in a more printer-friendly format, I might have this in MyPrintedInformation: a cell or defined name that says (this doesn't work, but just as an example): let's call the named variable TheSheetToPrint ='MyInformation'. Then from within MyPrintedInformation all of my references look like this Some cell : =TheSheetToPrint!A2
View Replies!
View Related
Formula Using Sheet Reference In Cell
After having searched for a while I can't seem to find any formula that will look at a cell for reference as to which sheet to go to and return a certain cell's contents. For example, in column A, I have the text "Sheet1" and in column B, I need the formula to return the contents of cell B15 from Sheet1, based upon column A.
View Replies!
View Related
Formula Reference To Cell On Another Sheet
I have Excel 2007, here's what i want to do. I've got a workbook created with several worksheets in it. I want to input the name of a project in say, cell A1 on sheet1. Then on sheet2 cell C4, I want the text I input into the afore mentioned cell to show. I know how do do this with numbers (using the autosum button), but I can't figure out how to do it with text, though it's probably very simple & I'll be embarassed when I get an answer. Following is EXACTLY what I'm trying to do if the above example is confusing. I have a Project Cost sheet and on it I input (text) a description of a building to be constructed. The cells next to it will be the associated costs of that particular building. There will be several buildings listed on the sheet. On a Sales Figures Sheet in the same workbook, I want to list those same buildings and then their corresponding sales prices. I want these descriptions to be automatically pulled from the costs sheet, so that I only have to input the sales figures.
View Replies!
View Related
LOOKUP With AND Requirements (lookup A Particular Student's Grade In A Particular Assignment)
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value". My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.
View Replies!
View Related
Sheet Cell Reference And INDIRECT Formula
I have this formula in excel 2007: =COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H"). Blad is Dutch for sheet, by the way. In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2. W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9. I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.
View Replies!
View Related
Look Through A Sheet, Find A Text Reference, Then Bring Up Something In A Cell
I'm in the leadup to my A2 ICT controlled conditions piece where i have to make an Excel system. My chosen subject for this is to use the school house points system. I'm going to make a system where the results of house matches are inputted and points are awarded accordingly. I just have a couple of queries after looking at my initial designs. 1. Is it possible to get excel to look through a sheet, find a text reference, then bring up something in a cell located in the same row? For example, for it to locate one of the House team names, then go along the row and find the ammount of house points they have in order to make a total for them. Is this also possible over a number of sheets? (In order to keep a running total on a separate sheet). E.G. House, Games Played, Wins, Losses, Draws, House Points 2. For the house cross country event they use a different system which differs from everything i have designed. What i basically need to do is be able to use a drop down against each finishing position where you can select one of the four house teams, then it takes the points won for that finishing position and puts them into a table.
View Replies!
View Related
Reference Cell On Non Activate Sheet Macro Code
I have a UDF in Cell Sheet1!A1 that inputs some ranges from Sheet1 and calculates a number. Then I have a sub that calculates the value of this function with Evaluate(Sheet1!A1.formula). However, this only works when I am in Sheet1. Otherwise, I get the wrong calculation. How can I avoid this? I will post the code in the open source forum as it otherwise is quite good and automatically resizes array functions to their proper size.
View Replies!
View Related
Copy Cell Reference From Seperate Sheet To Increment Correctly
I have two sheets in my workbook, and I am trying to reference one chart's value into the other. (attached) I have referenced successfully by manually typing in the reference on row 2 for all values on Sheet 1: Sheet 1 - C2 needs to reference Sheet 2 - B2. (Sheet2!B2) - no problem all the way across to F2. All those values are correct b/c I manually typed in the reference. The problem comes when I just want to drag over that same formula to increment for the other cells. It's not incrementing the formula like I need it to. So, Sheet 1, Row 2 shows how the correct values I need to pull over but was done in a manual way. Sheet 1, Row 5 shows how it increments incorrectly when I try to just drag/copy the formula over. What I need is for Sheet 1 - D5 to reference Sheet 2 - C3, but instead if I try and drag to copy the formula instead of manually typing it in, it increments when I drag reference D2 instead of C3.
View Replies!
View Related
Lookup With 'sheetname' In Formula
I am collecting a lot of prices from over 60 countries. Each country has a seperate sheet in a workbook, named "Country" Spec (eg Australia Spec, China Spec etc.). These sheets are all setup exactly the same. I have the following sheet wherin I want to show the data coming from all the different sheets: | a | b ----------------------------------- 1 | Australia Spec | "Formula" ----------------------------------- 2 | Bangladesh Spec | "Formula" ----------------------------------- 3 | China Spec | "Formula" In B1, 2 3 etc I want to do a lookup of a fixed cell on the correct country sheet. Therefore I want to include the sheetname in the formula. Thus I would like in B1 the content of cell q13 of sheet Australia spec. In B2 the content of cell q13 of sheet Bangladesh Spec. My current formula is: =INDIRECT(a1&"!$Q$13") But it doesn't work
View Replies!
View Related
Using A Loop To Add '0's In Between Two Other Numbers To Reach 12 Digits Total
I have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC. I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT. What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.
View Replies!
View Related
Lookup Correspoding Data On Another Sheet Of Cell Value
i have 2 sheets in my excel workbook. in my second workbook i have the values as follows A B ss 10 yy 20 zz 30 so in my first sheet values: X Y (List) ss yy zz in my first sheet X range i have list which consists ss,yy,zz so i f choose ss from the list it has to check the valu for ss in sheet2 and has to fetch 10 and display in Y range my excepcted output: X Y (List) ss 10
View Replies!
View Related
Referencing Active Cell's Value From Another Sheet
I'd like for users to click in a cell in col A in a sheet named "Period7", have the cell value placed in cell C11 in a sheet named "Per7Report," and change the focus or active sheet to sheet Per7Report. Can this be done simply by clicking in a cell, or must a command button be inserted into each cell (note - odd rows only) in col A in Period7. on the code to do this? Note that there is a formula in col A in sheet Period7
View Replies!
View Related
Add Button To Sheet & Assign Macro Code
how I can write code to create a button on a worksheets and then link to another subprocedure. I tried recording a macro and this is what excel so eloquently gave me: ActiveSheet.Buttons.Add(384.75, 60.75, 79.5, 39.75).Select Selection.OnAction = "CreateImport" ActiveSheet.Shapes("Button 1").Select Selection.Characters.Text = "Parse Deposits for Import" With Selection.Characters(Start:=1, Length:=25).Font . Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With This works fine, my only problem is if the button that is created is actually named Button2 or 3, etc. Is there a way to assign the new button to a variable, or would it be an object? this way it wouldn't matter what it was named.
View Replies!
View Related
'3-way' Table Lookup
I have a large set of frequency data obtained from experimental testing which is dependant on three variables - a span length (from 0-20 metres), vehicle type (divided into motorbikes, cars & trucks - small, medium & large for each) & speed (20-80 km/hr). Given a span length, speed & particular vehicle (eg large car), I need a program/formula that calculates the equivalent vehicle type (eg in 'motorbikes' - it would correspond to 'small') based on the frequency data. The equivalent vehicle frequency would be +/- say 0.5 Hz (since one type of vehicle would not have the exact same frequency as another). I have tried experimenting with 'vlookup' functions but it doesn't seem to yield a solution to this problem.
View Replies!
View Related
If Statement: Return 'V,' If Cell='&'
When I tried using if & or statements I got an error - so I tried this: =IF(K7="&","V,",""),IF(K7="1 Space + &"," V,","") I want to return 'V,' if cell='&' or if cell='(space)&' I want to return '(space)V,' What is wrong with this statement..?
View Replies!
View Related
Run-time Error '1004' :: Method 'Range' Of Object'_Global' Failed
I am trying to create a macro in my personal macro book such that whenever any workbook is opened the calculation settings (tools, options, calculation tab) are set to semiautomatic and do not calculate before save. The macro works when I am opening Excel itself (Book1) but when I open an already saved file it gives me Run-time error '1004' Method 'Range' of object'_Global' failed From there I choose Debug, the VBE window comes up, and I hit F5 to continue the code without doing any actual debugging. Here is the code that I am using. This is in the Personal Macro book on the "This Workbook" section....
View Replies!
View Related
|
TRACKER
