I was wondering if there's a way to reference a default sheet in my code without using the SourceData:= '*'! format. I said default sheet because I'm operating on workbooks I've had emailed to me and as such the sheet doesn't have the typicall sheet1 format (that is to say the next sheet I add appears as sheet1).
View 9 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.
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:
='Sheet1'!('Sheet2'!(AB7))
however that doesn't work.
How can this be done?
E.g. say I want to create a list of cells each referencing to the first, second, third sheet, and so on.
Say, on one sheet cell A1 references to the second sheet's A1, cell A2 references to the third sheet's A1, and so on.
Ideally I'd love to be able to write something like
=Worksheet(1)!A1
=Worksheet(2)!A1
=Worksheet(3)!A1
and so on.
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 RelatedSheet 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 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 want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
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?