Sheet 2 contains my checking ledger and sheet 1 is my summary page that I would like to have show current balance from the ledger. Is there a way to reference a value from sheet 2 on sheet 1?
View 3 RepliesIf 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 have a spreadsheet that shows a large number of folks we had working in a particular division from Jan. 1st until now, 1800 +. Some of the folks are on the sheet twice due to having more than one role. The sheet lists last name, first name, skill description, pay rate, company they worked at...etc. Most of the folks on the list are not currently working but some are. I have another spread sheet that lists the folks that ARE currently working. I'm trying to see if there is a way to compare the two sheets, via a formula, that will be able to identify when the first name, last name, company name, and skill description are the same and then have those identified deleted from the first sheet.
View 14 Replies View RelatedTo get the content of cells in other sheets of the workbook you refer to the 'Name' of the sheet and write e.g.:
[Code] ....
However, I need to refer to the '(Name)' of the sheet rather than the 'Name', i.e. 'Sheet1' rather than 'Fruit', and I would like to do it in a formula not using VBA.
I have a spreadsheet, that I have been using for years to estimate jobs. I started learning excel, by building this sheet. It is still a work in progress, as am I. I am always trying to figure out ways to make my life simpler by making the sheet more intelligent. I would like to be able to go from one sheet, to another and click on a part name, which would return a value associated with that part name, to an additive formula on the first sheet, to build and assembly. And, eventualy build a parts list from it as well. For ordering parts.
View 7 Replies View RelatedI am using a sumif function that will sum a reference off another sheet.
I want to use a cell in the current sheet(where the formula exist) to reference the target sheet.
Is this possible.
For instance:
I believe you have to use indirect for to have formula reference a sheet name
I have two sheets, "Win" & "Lose"
On "Win" sheet I have this function =sumif(indirect(J180)G1:G20,"room",E1:E20)
On cell J180 of "Win" tab will be "Lose". I am trying to have the formula reference J180 as part of the function and have it sum anything in the "Lose" tab that has the word "room" in it.
I'm getting a #Value Error when i enter the following formula
=VLOOKUP(BF$17,"'"&B19&"'!"&"A10:N200",4,FALSE)
BF$17 is a number that stems from a date variable i'm looking for. B19 is a text field with the worksheet name in it.
How do you reference a table on another sheet so that when you sort that original table it doesn't change the reference you made on that other sheet.
i know how to do this when i am referencing a cell. for instance if i am referencing a cell on sheet 5 and that comes out to something like =Sheet5!B2, i know that when i sort the data differently in sheet 5, that reference will know to change to =Sheet5!B18 or where ever that original reference ends up in this new sorting. but if I do =Table5[2] or something, and i sort that table differently, the reference will change to the new data in that cell and will not follow old reference through the new sort.
I have a the following function: =INDIRECT(ADDRESS(SUMPRODUCT((F35:G45=K40)*ROW(F35:G45))-21,SUMPRODUCT((F35:G45=K40)*COLUMN(F35:G45))))
I'm trying to put the get the value of it from a different Sheet.
say this function is in Sheet1 and I wish to place the equation in Sheet3 - what should I change? I tried INDIRECT(ADDRESS(SUMPRODUCT((Sheet1!F35:G45=K40)...
nothing works.
what the function does? there are 2 tables. table 1 has values and table 2 has values as well. K40 is the user input --it reflects the number in table2 and the return value is the 'reference' in table1. for example, if table2 in F41 has the number 54 and the reference for it in table1 is $344, when the user types 54 in K40 the returned result in the function will be $344
I have attached my workbook. What I would like to do is to have an input box to where you input the Material, Gauge, and Size and then have it return the rest of the values on Sheet 1. The material is listed as the worksheet name and the other information is inside that sheet. For ex... I would enter in Stainless, 7, 48x120 and it would give me the rest on sheet 1. I am totally lost, I have tried several different things but am coming up with nothing.
I've found on this newsgroup a function to get the previous sheet
name
Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function
when i put =PrevSheet() i get the previous sheets name
What i want to be able to do is use this in place of directly
referencing the sheet name in my code as follows...
=IF(G4-J4<0,G4-J4,G4-J4)+'Week 1'!H4
So i want to be able to replace Week 1 with PrevSheet but i've tried
and falied on this. eg...
=IF(G4-J4<0,G4-J4,G4-J4)+PrevSheet()!H4
Could someone show me the correct syntax for this.
I'm working on a formula to get the the path of a picture on a different sheet in a workbook. I am trying to get use the hyperlink function to reference that cell on the the other sheet. It looks right but I get an error saying that it cannot open the specified file.
This is what my formula looks like:
=HYPERLINK(ADDRESS(4,12,2,,"Sheet 1"),2)
I need it to actually get the actual text value of the cell I am trying to reference. How would I do that?
I need a function where the sheet-reference can be linked to a cell.
By other words can the content of cell A1 be sheet1, sheet2, sheet3 etc. The cell in B2 shall always point to cell b3 in the choosen sheet, but the sheetreference is dependent on the sheet refererred to in A1
In the following example the costs for each FGI SKU are on detail cost build sheets each named simply 600001, 600002, etc.
To add a summary I simply list all the SKU's on a summary worksheet and reference each detail sheet. The problem is with large numbers of SKU's it gets clumsy having to create a reference for each sheet.
Is there a way to have the formula in the cost column reference the detail sheet based on the value in the FGI SKU column?
FGI SKUCost60000111.34 60000211.34 60000311.34 60000411.34 60000511.34
So to reference the cost for the 600002 SKU instead of this...
=+'600002'!$E$37
I would like this...
=+'value in cell with 600002 in it'!$E$7
How Could I change the below line to reference the sheet "NO" not just a range.
Set Source = Range("NO!A1:BK10000").SpecialCells(xlCellTypeVisible)
I need a formula that would look for and match a reference in docsheet tab - column I - (to match against the same reference in PRIMARY tab - column C) I need it to bring back the time from docsheet that is in column B and 1 row down. I want the formula in column H in the PRIMARY sheet. The data in docsheet changes daily and can e several hundred rows but PRIMARY ref is always the same.
docsheet
A............ B ........... C .........D E....... F...... G H.............. I
- - - ....................................................................... BCD243 ............22:01
PRIMARY
A............ B ............ ........C ..................D..E.F..G..................H
..................................BCD243 .....................................time formula
I'm trying to write some code that loops to take cells from Sheet 1 and puts them in Sheet 2, in a different order. I want to start working on row 11 of Sheet 2 and row 23 of Sheet 1. I have mRow = 11 but I can't figure out how to make nRow = 23 on Sheet 1.
View 9 Replies View Relatedi have a code to save file referencing to a certain cell on a sheet, but how do i reference to 2 of them, since one is for a month and the other one is for year:
ChDir "X:SSS2014" ActiveWorkbook.SaveAs Filename:="X:SSS2014SSS Report* " & Format(Sheets("CP").Range("D3").Value, "mmmm yyyy") & ".xlsm", FileFormat:=".xlsm
so i have D3 and F3 to reference to together?
I have this formula (which will soon be much more complex...):
=COUNTIF('dt-a'!C2:C194,'what they want for each teacher'!D1)
I would like to be able to replace the sheet names with a reference to a cell which contains the sheet names.
This is so that a user can enter the sheet name in a cell without having to modify the formulae, break the formulae, and then come running to me ranting about things not working and how computers never work properly.
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?
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.
From a formula, how can I reference other sheets in my workbook via a relative reference as opposed to needing to know the sheet name? I know I can write a macro, but looking for a non-VBA solution.
View 9 Replies View RelatedI am not very experienced with excel VBA and I am trying to populate a blank sheet in a file called Agent.xls by referencing a file called Agent_Export_03.xls I tried playing around with a few things and I don't know why it isn't working, I guess I don't know how to reference to a separate file. I need the macro to look at cells in C1 an depending on if they are different or not, populate my new excel file accordingly, but my reference doesn't work.
Sub Agent()
x = 2
counting = 2
countitem = 2
Do While [Agent_Export_03.xls]Sheet1!RxC1.Value <> ""
b = x - 1
If [Agent_Export_03.xls]Sheet1!RxC1.Value <> [Agent_Export_03.xls]Sheet1!RbC1.Value Then..............
On sheet1 I have a list with some employee numbers and other training data. On sheet2 I have a complete list of employee numbers along with the address details.
On sheet1 I have a cell using the MATCH formula to pick up the column reference for the address from Sheet2. What I would like to do is use a macro that looks at the column reference in sheet1 and then copies the details from that reference in sheet2 and pastes back to sheet1. The following is what I have tried, but it just doesn't want to work:
Sub selectrange()
Set ws = Worksheets("Sheet1")
Set wl = Worksheets("ListEmployeeContact")
ws.Select
ws.Cells(2, 1).Select
wl.Select
wl.Range(ws.Cells(4, 11), Cells(5, 26)).Copy
ws.Select
ws.Cells(2, 1).Select
ActiveCell.Offset(2, 12).PasteSpecial
End Sub
I've got the following code:
VB:
Application.DisplayAlerts = False
Worksheets("WildCard").Delete
Application.DisplayAlerts = True
I'd like the "WildCard" to pull from a value in a cell whereby I can run a button macro that, when pushed, deletes the tab/worksheet named "WildCard". For instance, if I wanted to delete "Sheet3" I would type "Sheet3" into, say, cell C3 and then press the button to run the macro and "Sheet3" would be deleted. How to I refer the macro to cell C3? I currently have a macro that inserts/names a sheet based upon a cell reference and macro button. I'd like one that can delete in the same way.
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
Some cell :
=TheSheetToPrint!A2
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).
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?
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..