Getting Additional Data From A Reference
Jun 21, 2009
I am building a database of music files and using Excel(2007) to catalog them. As an extra bonus I'm setting up a sheet of statistics about the database with suchs things like the # of tracks and longest song, etc.
Most of it has been easy to figure out but there's one formula I'm not quite sure how to do, or the way I think it is to be done is incorrect or contains some error on my part. I already have a cell that gives me the length of the longest song in the database. What I want it to do is give me the name of the longest song. This is what I tried to do, without success, giving me an popup error telling me that the formula has an error.
View 3 Replies
ADVERTISEMENT
Jan 29, 2014
I am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.
al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.
View 1 Replies
View Related
Aug 13, 2013
Trying to perform a match of 2 sets of data.
Data set 1 is 2 columns: A = ID number, B = Text entry
Data set 2 is 1 column: C = Text entry
All of the cells in column C can be found in column B so I'm trying to match that data but I also want to create a new column that lists the ID number for the matched cell.
For Example:
Column A
Column B
Column C
Desired Result
1
Sock
Underwear
9
[code].....
I've tried to do this MATCH and VLOOKUP. Kept getting error messages. I threw a stapler.
View 5 Replies
View Related
Jun 18, 2008
The code is as follows:
Sub addtaskpc()
ActiveSheet.unprotect
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.Insert Shift:=xlDown
End With
Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
ActiveSheet.protect
End Sub
View 9 Replies
View Related
Feb 18, 2014
I want to use excel to create quotations for customers. (currently doing it in word)
Tab 1 is the quote, has 4 columns, part number, description, cost, yearly maintenance
Tab 2 has all the things we sell, part number, description, cost, yearly maintenance. 150 lines.
So, Tab 1, description column is a drop down box from Tab 2. Working fine.
Question. When an item is selected from the drop down, how do I get it to then bring from tab 2 the part number, cost and yearly maintenance.
View 8 Replies
View Related
Mar 17, 2014
Sheet 2 in the attached sample workbook (without all the pre-existing formula's from the original) contains data that I paste in (Cols A to D).
Sheet 1 then searches that data (minus Col D) from Sheet 2 and returns "Yes" to Col I if matching data is found.
What I would like to do now is if Sheet 1 returns "Yes" then the corresponding value from Sheet 2 would be returned to Sheet 1 Col J.
So in this example the value "5" would be returned to Sheet 1 Cell J1
I already have all the formulas in place to find the matches and return "Yes", this is just to return the additional information from Col D Sheet 2 to Sheet 1 Col J..
View 3 Replies
View Related
Feb 20, 2010
I would like to set a data validation list with
- the list of values (A1:A10)
- at the top of the list the value "all"
- as default (when opening workbook or when changing depenend validation list) = "all"
View 9 Replies
View Related
May 8, 2006
I've set a range as DD14 to G014, then using a For Next Loop asked the program to cycle through each cell in the range. Upon findng a populated cell, it stores several bits of data from that cell (already done) and then move up one cell to record the cell value (time) and again move one cell up to record the date.
Now I did get this to work using a clunky bit of code that used activecell.offset to move the focus up the two columns and a While .... Wend loop - but using a set range and cycling through each cell it does not appear to be possible - or am I wrong?
Set MyRange(1) = Range("D14:ER14")
Set MyRange(2) = Range("D23:GN23")
For MyCounter = 1 To 2
For Each Cell In MyRange(MyCounter)
If Cell.Value = "" Then Goto Skip_Cell
MyCell = Cell.Address
MyPosition = InStr(1, Cell.Value, ",")
If MyPosition = 0 And Len(Cell.Value) >= 3 Then
Tool_Number = Left(Cell.Value, 3).....................
View 2 Replies
View Related
Jun 9, 2014
I have 400 source files containing (among others) 8 sheets with daily results: "Fri 23", "Mon 26", "Tue 27", "Wed 28", "Thu 29", "Fri 30", "Sat 31 (if applicable)", "Mon 2".
Each sheet contains also:
State - D1
Role - D2
Staff ID - D3
Date - D4
Activity group name in column A (starting from row 8)
Activity type in column B (merged with C and D) (starting from row 8)
Activity time in columns E:GV (starting from row 8). Usually, there is none or only one value in whole range (e.g. E8:GV8). But sometimes there are two values.
Customer ID in row 6 (value appears only if time was reported in E:GV range)
CC Number in row 7 (value appears only if time was reported in E:GV range)
It's all about transferring values from all daily sheets in all files (.xls) sitting in folder C:WADFinal to one simple table (WAD_Consolidation_file.xls, sheet "Consolidated") consisted of 9 columns: Staff ID, Role, State, Date, Activity Group, Activity Type, Minutes, Customer ID, CC Number.
Additional note if two values exist in the same row they should be copied as two separate entries to consolidation file.
View 9 Replies
View Related
Feb 18, 2009
I'm working on a spreadhseet which has several macros that can take up to 5 minutes to run. When the macro(s) complete, I pop up a userform to provide instructions and collect some additional data from the user.
The "problem" is that when the user moves to another application to do other work, the user form remains hidden behind the active app. I've added some text to the status bar to let the users know the processing is done, but they would like something more obvious - so, is there any way I can force focus back to Excel?
View 2 Replies
View Related
Aug 21, 2009
I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?
View 9 Replies
View Related
Jan 16, 2014
I am working on a project that requires Other or Additional info to be available if I click on a specific item. For example, if I click on a person's name a message or pop up show appear with the person's address or any other information I might want to add. Is this possible in Excel or should I be using a different program?
View 1 Replies
View Related
Aug 2, 2012
I have a macro attached to a command button to clear entries in my workbook for me however, I left out one tab that needs to be cleared.
I need to add the following tab and cells.
Add Tab "Time Off Glance", Cells (B1:P35) to the same command button.
The code I have now is:
Private Sub CommandButton2_Click()
Dim rng As Range
Dim x As Integer
Dim y As String
For x = 1 To 27
y = x
Union(Sheets(y).Range("B9:O15"), Sheets(y).Range("Q9:T15")).ClearContents
Next x
End Sub
View 5 Replies
View Related
Mar 16, 2007
I am trying to find some better looking command buttons for userforms in excel VB.
Maybe like the ones you can use in Access from the command button wizard. inoculars for find, door for exit etc. Does anyone know how to get these in excel VB editor?
View 2 Replies
View Related
Mar 28, 2014
I have a code that searches in a column for a certain value, if it finds it, it copies this value to a range adjacent to it.
[Code] ..........
However, if the cell where the code is supposed to find a new value is blank it makes the range blank. But in that case, I wan't it to leave the range cells with their original value.
View 11 Replies
View Related
May 5, 2007
I have one sheet that receive data automaticly.
I have colorfunction in the end to count the red,yellow and green points.
My problem is sometimes there are points that have more than one
coordinate (X,Y,Z) as you can see on line 6,7,8 and they should count only as one.
The criteria for that is if any of them is yellow it should count as yellow,or if any is red it should count as red, always the worst result.
View 9 Replies
View Related
Jan 6, 2009
So we created a workbook that has lots of worksheets inside it. The problem is due to the high number of worksheets there are alot of tabs along the bottom. Many of the tabs are hidden behind the scroll bar. Is it possible to have 2 rows of tabs at the bottom of the screen?
View 2 Replies
View Related
Apr 1, 2013
I'm working on a spreadsheet that needs a lot of check-boxes and every time I add one I get the text "Check box" right next to it. I can't seem to get rid of it.
Format control - Alt Text didn't work.
View 2 Replies
View Related
Mar 10, 2014
I have problem with additional "hidden"(duplicated?) sheets (not by atribute)
This is my excel, created from new fresh file : excel tables.bmp
And this is what i see when i get all sheetnames (with C#, word and some other programs) word-tables.JPG
where A1$ have same value as A1$_4349_inkjet_WZ
I have this problem with some other files but in this one i have 100% confidence that none of sheets is hidden nor very hidden.
I found that 3 proper tables are "System table" and 2 additional are "tables".
[URL] .....
View 1 Replies
View Related
Aug 18, 2014
I have an existing macro that convert active sheet to pdf then email it as an attachment thru outlook.
now, what i need is to attach another pdf file in it, so it will now be 2 attachment. here is my codes:
var all_spans = document.getElementsByTagName( 'SPAN' );
for( var i =0,skip =0 ; i [code]......
View 6 Replies
View Related
Feb 3, 2009
I have seen this on many spreadsheet but never figured out how to do this - click on a cell in a spreadsheet to open up a new spreadsheet?
View 4 Replies
View Related
Dec 29, 2011
I need to add a condition to a statement that adds a "If TRUE" reference to another cell.
The other cell can be either a TEXT value "RM1" OR "TE2". If neither condition is true than the value is blank or zero.
So right now I have in the destination cell =IF(AB7="CONT",V7,"") That works lovely, but really isn't countable unless cell "Z7" ALSO has Either "RM1" OR "TE2".
If cell "Z7" has the text "DEA" or "SP" then the value of "V7" will be placed in a different column. I will use a different destination column (for this modified "DEA" or "SP" condition)
View 7 Replies
View Related
Aug 3, 2012
I have a spreadsheet that records data on various sites with data entered on a userform.
Every so often a site may need to be added or deleted. Is there anyway to use VBA to add an additional field to a Userform, and then also delete the field if necessary?
View 2 Replies
View Related
Jan 17, 2008
The codes below allows me to change the color of the cells in Col F & G dependence on the value of Col G. I like to add another condition and that is if Col K the value is cancelled or closed the whole row will be CellColor = 48: FontColor = 2
Sub Risk_Color()
Dim c As Range, myFontCol As Integer, myCol As Integer
For Each c In ActiveSheet.Range("f7:g20000")
myFontCol = xlAutomatic
myCol = xlNone
Select Case c.Value
Case Is = 1, 2, 3
myCol = 34....................................
View 9 Replies
View Related
Sep 18, 2008
when the user enters the letter Y in a cell within a set column a macro kicks in and enters a VLOOKUP formula in a cell 1 row above and 4 columns to the left by using ActiveCell.Offset (-1,-4).
This seems to work fine however when I try and get the coding to copy and paste special as values it just seems to ignore it?
View 9 Replies
View Related
Sep 20, 2008
i have a macro that copies info from cell in row A and uses that info to name the whole row, now if the name has a space between the words it uses _ (underscore) substition so i don't get an error (when row name is defined - you can't use any special characters or spaces) i was wondering if anyone can help me to add "-" besides the space
here is the macro
Sub Macro1()
Dim a As Long
For a = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(a, 100) = Application.WorksheetFunction.Substitute(Cells(a, 1), " ", "_")
ActiveWorkbook.Names.Add Name:=Cells(a, 100), RefersToR1C1:="=Sheet1!R" & a
Next a
End Sub
View 9 Replies
View Related
Oct 8, 2008
I've managed to create a drop-down list with the following possibilities:
Yes
No
Others
When the option Others is selected, I would like the user to be able to input additional information in a new line with wrap text formt (this new line should be hidden when either Yes or No is selected).
View 9 Replies
View Related
Apr 11, 2008
As a follow-up to Cannot Access Additional Controls From Toolbox.
I have been having the same problems adding additional controls to the toolbox in Excel VBA on Windows Vista running Office 2007 Pro.
I ensured I had a full install of Office and I was logged in with admin rights.
Two work-arounds:Create a form in Excel on a machine that has the control you need to use, then export the form. Copy it to the PC that doesn't have the additional controls functioning properlyOn my machine, creating a new profile and running VBA enabled the additional controls *only for this profile*, not the existing profile.
While these work-arounds don't solve the problem, they point to it being a profile issue, not an Office installation issue.
Hope this saves you the hours of Googling that I have spent trying to find a solution.
View 3 Replies
View Related
Sep 29, 2009
I have two worksheets, 'monthly budget' and 'summary sheet'.
Summary sheet has been set up so that any additional records added to 'Monthly Budget' will be copied using the formula below (kindly provided by Cheeky Charlie yesterday):-
=IF(ISBLANK(INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A))),"",INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A)))
I have set up a unique entry count on 'summary sheet' which is currently showing 5. If I add a new name 'Jim' to the 'Monthly budget' sheet, this is copied on the 'summary sheet' however the unique entry count remains at 5.
View 5 Replies
View Related
Mar 14, 2014
I'm attempting to create a worksheet to consolidate his financial data.
How to add the last loop or where to place it.
I can copy the values from the P&L sheet to the DATA sheet but I cannot discover a way to also pull the values from the BalSht sheet to the Data sheet.
I think the BalSht loop will be almost identical to the "P&L sheet loop, and be placed just below it - but I'm not successful in multiple attempts and days of trying.
How can I accomplish this? Here's the VBA code:
[Code] .....
View 3 Replies
View Related
