Lookup Numbers In Sheet And Assign It's Cell Reference
Apr 5, 2006
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.
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?
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?
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.
I have attached a spreadsheet with a small amount of the data I need to use. This is the distance between customers in kms. I need to be able to have excel look up the cell reference for distance between two customers though they will not always be the same customers every day in the same order.
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?
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.
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:
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!
In Sheet2 I have a list of cell addresses showing values such as D5, D32, D59, D221, D869 stored in cells AB7:AB16. In Sheet1 the "D" column holds dates. I want to return the corresponding date for each D# cell into Sheet2 in column AC7:AC16. I'm unaware of the proper syntax for this. I though it would look something like:
I am running excel 2010 with windows 7. I created a macro in sheet 1 and I wish to activate the macro from sheet 2 using a form button. I have entered the code below. I know how to perform this function on a more simple macro like adding names to cells. This code is a bit more complex I just dont know where to start.
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.
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.
can i take reference from another sheet to formatt the cell? When i tried to format cell taking reference from another sheet, it says that you can not do that. Is it possible or not?
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
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).
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'
We have a sizeable Excel workbook that contains many worksheets for various things, everything works smoothly except for one minor niggle.
I am looking for a formula that references to a cell on a previous worksheet, the worksheet could be named anything (eg A1, B, 2, 3.4, etc). Then to add 1, ideally the first worksheet inserted must equal, say, 0 BUT can reference the sheet previous as this is a constant; so something along the lines of...
Cell A1 =magic formula Cell A2 =A1+1
This is to get a vlookup to work consistently.
OR another option is, for the worksheet to reference an already populated worksheet and read down a table by adding one to reference the cells. Eg first sheet reads ='schedule'!A13, the next sheet added would read A14 then A15 etc...
OR a worksheet (like page numbers) count into a cell onto each worksheet inserted, eg on worksheet 6 in cell A1 the number 6 would be inserted then worksheet 7 would have 7 inserted into A1 etc..
I have several worksheets and I want to summarize all of their data on the main page. The second worksheet is called Grady, the third worksheet is called Stella and the forth worksheet is called Westminster.
On the Main Page cell B1 is, "Grady", C1 is, "Stella" and D1 is, "Westminster". On B5 I need the value of cell C5 on Grady but I don't want to have to type it in as this formula will have to be carried over for several columns.
Currently I have:
Code: =Grady!C5 In cell B5 on the main page but I want:
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.
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.
