I have a macro that selects a specific worksheet and checks for a value in a specific cell when they save the workbook. I would like to have it return to whatever worksheet that was active when they clicked on save. Is there a way to do this without knowing which sheet in the spreadsheet they will be on?
View 7 RepliesPossible to look up a value from one excel sheet in another sheet and then return multiple values in the first sheet
For example the following is a sample of sheet 1
NSV Code
Item
12
OLANZAPINE
54
ROPINIROLE
And the following a sample of sheet 2 :
Nsvcode
Item
Division
Speciality
Qty
12
OLANZAPINE
CLINICAL
CS
10
[Code] .....
I want to lookUp the NSVCode from sheet 1 in sheet 2 and return the speciality and qty values in sheet 1 as sheet 2 contains a lot of info I do not require and sheet one also contains various pricing calculations I also require.
how to return data to a second sheet if data selected via a filter.
i have attached spreadsheet i am working on and looking to make this as easy as possible
on sheet "data" cell f2 im looking to return all goals scored by hull
on sheet "data" cell f3 im looking to return all goals conceded by hull
on sheet "data" cell f5 im looking to return all goals scored by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f6 im looking to return all goals conceded by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f8 im looking to return hulls last 6 games as over and under which in this example will return the last 6 rows 27-32 and will read 4 under 2 over
on sheet "data" cell f9 im looking to return hulls last 6 games at home as over and under which in this example will return the last 6 rows 22/23/26/27/29/32 and will read 3 under 3 over
I have in my 'Daily' sheet I9 which needs a formula to look at the date in C9 and the scan range 'Weeks' F6:AN41 and find the cell with the matching date and then whatever text is in Row 2 of the column the date was in will be returned.
View 2 Replies View RelatedI have formula that looks for information from another sheet (sheet name "ValidationLists")in Column A (list "billto") and returns all the occurances in my current worksheet (including the 10 columns next in the array). What it looks for is based on whatever value is currenty in Cell A2.
Example of formula is as follows:
{=IF(ISERROR(INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,ROW(billto)),
ROW(1:1))-1,1)),"",INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,
ROW(billto)),ROW(1:1))-1,1))}
Basically I want to return everything that matches cell A2 of my current sheet from sheet list "billto" in a different sheet but I need the adjacent cells as well. So one cell below this would read the following (Just the Row Changes):
{=IF(ISERROR(INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,
ROW(billto)),ROW(2:2))-1,1)),"",INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,
ROW(billto)),ROW(2:2))-1,1))}
And the column B next to the original formula reads the following (just returning one more column from the array):
{=IF(ISERROR(INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,ROW(billto)),
ROW(1:1))-1,2)),"",INDEX(ValidationLists!$A$2:$J$300,SMALL(IF(billto=$A$2,
ROW(billto)),ROW(1:1))-1,2))}
It is working exactly how I want it to. The problem is it is taking 2-3 minutes. I can just get a list of rows with the value in A2 from List "billto" very quickly but then how do I get the entire row to appear in my current sheet quickly?
Public Sub ListMyFiles()
Dim fso As New FileSystemObject
Dim fso_Folder As Folder
Dim fso_File As File
Dim file_count As Long
Set fso_Folder = fso.GetFolder("S:SHARED All of TransportationTraffic AssessmentMultiway Warrants")
file_count = 1
For Each fso_File In fso_Folder.Files
file_count = file_count + 1
Cells(file_count, 1).Value = fso_File.Name
Next fso_File
Set fso = Nothing
End Sub
This works great, but is there a way for the macro to recognize the file names that have already been added to the spread sheet and only return new ones that have been added to the folder?
I made a new class to make other code less redundant, but it isn't functioning. The class has a "workbook" and "worksheet" member, and these can be accessed through properties. The problem is that the properties don't actually seem to return anything, and no data can be accessed through them.
Private mActiveWorkBook As WorkBook
Private mActiveWorkSheet As Worksheet
Property Get wSheet() As Worksheet
wSheet = mActiveWorkSheet
End Property
Property Get wBook() As WorkBook
wBook = mActiveWorkBook
End Property
'Sets active WorkBook
Sub SetActiveWorkBook(ByVal wBook As String)
Set mActiveWorkBook = Workbooks(wBook)
End Sub
'Sets the activeWorksheet in the workbook.
Sub SetActiveWorkSheet(ByVal wSheet As String)
If mActiveWorkBook Is Nothing Then
MsgBox ("Invalid WorkSheet selection - WorkBook not defined")
Return
End If
Set mActiveWorkSheet = mActiveWorkBook.Worksheets(wSheet)
End Sub
This class is used in the macro as such:
Dim uDate As Updater
Set uDate = New Updater
uDate.SetActiveWorkBook ("Book1.xls")
uDate.SetActiveWorkSheet ("TestTab")
'Below Code is not functioning
uDate.wSheet. Range("A1").Value = "foo"
' Expected result - set Cell A1 in sheet testTab = "foo"
' Actual Result - nothing
Dim st As String
st = uDate.wSheet.Range("B5").Value
MsgBox (st)
' Expected results - bring msgBox of values of cell B5, this cell is not empty
' Actual Result - Empty Message box comes up
So - its fairly obvious to me that something is wrong with the properties. The members themselves are not null, I have verified in the debugger that the class members refer to actual sheets/workbooks, but the properties don't like passing anything out and show as "variable not set" in the debugger. How can I get this to work?? It works fine when I do not use the class, like such:
Dim wB As WorkBook
Set wB = Workbooks("Book1.xls")
Dim wS As Worksheet
Set wS = Worksheets("TestTab")
Dim st As String
st = wS.Range("B5").Value
msgBox(st)
But I would like to get the class and properties to work to save clutter elsewhere.
I have two sheets: sheet1 and sheet2.
In sheet2 I have a column "C" called addresses and in that column I have actual cell addresses such as $J$740, $H$756, etc., all referring to cell locations in Sheet1.
In sheet1 in column "B" are names.
I would like to be able to return the names from sheet1 column "B" to column "G" of sheet2 that belong to the cell address from column "C" in sheet2.
Is there an Excel formula or vb script that will do this?
I tried "=INDIRECT("sheet1!"&C2)" but all that did was return was is written in the cell address (for example $J$740) but what I want is the corresponding name in column B.
I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?
Excel Help.xlsx
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 I can keep a running total of information added to a different worksheetS. They would not always be the same peson ID so I would like to return a match on any unique ID number found on multiple worksheets and add their values on a master front sheet. i.e this becomes a running total of items purchased per ID number.
View 4 Replies View RelatedI am trying to use the following code to compare the password that was entered, to the one that is stored in the worksheet to make the user aware that the password they have entered is incorrect
View 2 Replies View RelatedI have the following table:
A
B
C
D
E
F
1
Most Recent
1st
2nd
3rd
4th
5th
[Code] ......
I am wanting to populate the "Most Recent" column by selecting the last value in each row between B to F so that I get the following result:
A
B
C
D
E
F
1
Most Recent
1st
2nd
3rd
4th
5th
[Code] ........
I am also wanting the result under Column A to indicate by cell colour red if the value is less than the previous value and by cell colour green if the value is equal to or greater than the previous value.
For example; Cell A2 should be highlighted green because the last value which is D2 is greater than the previous value in C2. While Cell A4 should be highlighted red because the last value which is C4 is less than the previous value in B4.
I do have another question actually. In the Total Sales worksheet, I have a a cell in S2 that I wish to show the max value from the "total sales" colum and then show the first and last name related to that particular row where the max value was. so, I only have =MAX(O2:O112), but I want to show the Name associated with the max and not just the $ amount.
So in S2 I want ---> John Doe and not $512.50, but I can only think of =IF(MAX(O2:O112), $B2+$B3, 0) becase I want both the first and last name. I used this formula with only $B2, but it just returned the first name in the B2 field
i have this formula in cell e28 =IF(B28<>"",LOOKUP(B28,STOCK!A1:A500,STOCK!B1:B500),"") and when its empty it returns a blank cell
but in cell e29 i have exactly the same formula =IF(B29<>"",LOOKUP(B29,STOCK!A1:A500,STOCK!B1:B500),"") but i returns #n/a
why is this and how do i stop it i want the cell to be empty unless there is a code entered in b29 in which case it will look up the relevant info for me
I have a sheet with this:
cell a1 = numbers from 1 to 10 or nothing
cell b1 = TO or OFF depending on cell a1
cell c1 = numbers from 1 to 10 or nothing
Now I have a formula in cell b1 that detects if theres text in cell a1.
IF A1 = nothing, then b1 = off, else b1 = to.
What I want to achieve is to do 2 things on the IF.
If a1 = nothing, then c1 = nothing and b1 = off. else, b1 = to.
how can I do this?
I looked everywhere and no one speaks of double answers on IF.
i have a drop down list in cell b16 which then corresponds with what to bring up in the next drop down list in cell d29 this works fine.
View 2 Replies View RelatedI am trying to create a formula that will search a row left to right for the first instance of zero and return the corresponding column label. I had this working for a smaller table using a couple of hidden columns with nested ifs however the new data set is approximately 25 columns wide and it will get very messy using the same approach. See attached sample with desired results.
View 2 Replies View RelatedI have just decided to dable in the 'VLOOKUP' function. I have looked at a few examples on the net and I have tried to replicate them with no luck (I have modified titles and values). I have attached my example to this post.
View 2 Replies View RelatedIf I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?
View 2 Replies View Relatedcode to scan a datasheet for a value and return "MATCH" if it is found and to return "ERROR" written in Red or Yellow if it is not found.
View 4 Replies View Relatedi was trying to see or figure out if there is a way to bring over information on a row from one sheet to another. I have a sheet with dates in column A and information going across to column h.
as an example i have row 1 that has date in A1, text B1, text C1, nothing in D1 but text in E1 all in Sheet 1.
I want to see if there is a formula that will say if it is todays date then grab that row and bring over the row with the cells that have data
I am building a form that uses a combo box to lookup up a row in a table and return row data. To make things more complicated I want to return more than one record. For example: the table has a list of jersey. I search for a style jersey. I want to search the list and return all the colours for the chosen style. Sometimes there is only one colour or there can be up to 10. Have a look a the attached example.
View 2 Replies View RelatedI am trying to use a sub to count files that some one has helpd me out with in the past. It was working but for some reason now it is not. I always have 0 returned as the number of files in the selected location (SourcePath), which is a filthy lie as there are files in there. The idea is that all .xls and all .xml files are deleted, and then if the folder is empty, that is also deleted, but as the Count_Files sub is always returning 0 the code is trying to delete the folder, which causes an error as it has files in it.
View 8 Replies View Related=IF(ISERROR(VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,11,FALSE)),"",VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,10,FALSE))*D14
As long as the lookup value is true the formula works but if the lookup value is false then I get #value! when it tries to multiply by D14. How do i get the formula to return a blank cell or 0 value if the lookup value is false?
I would like to get the last value in column A & B and place them into another sheets cell. The number of rows will always be changing too.. In the attached spreadsheet I want the totals, which is the very last value in the colums.
View 8 Replies View RelatedI am including the entire code. It does a regression analysis on data for a second degree polynomial. In doing so, it creates a worksheet called Regression. So I test first to see if this sheet already exists within the workbook and then ask the user to delete it if it does. I've stepped through the function and the function is always returning False whether Regression exists or not.
View 5 Replies View RelatedI currently have a range of documents which I know the name of but not the file extension. The Macro I have been working with is fantastic for checking the existance of the file but I need it to also return the file name if it finds a match. I am currently using Excel from the 2007 Small Business Suite on a Windows XP PC. The Code I am currently using works as follows:
View 4 Replies View RelatedI want my worksheet to search a column for a specific date (3-Dec-2008, for example) and count cells containing only that specific date. If it finds the date, I want it to count them.
However, if that particular date doesn't appear in the column, it returns 0. I want the cell to return blank. Basically, instead of giving me 0, I want it to return empty. I've tried IF statements, COUNTIF statements, combinations of IF/COUNTIF statements.
=AVERAGEIFS(Activity!K2:K80,Activity!A2:A55,"Complete",Activity!C2:C62,"Oakpointe")
K2:K80 holds dollar amounts. I would like the average of those dollar amounts where Column A=Complete, and Column C=Oakpointe
For some reason it won't return the average. Could it be because Column A can C are dropdowns?