Concat Two Cells In The Source Worksheet And Vlookup Into The Defs Sheet
Aug 26, 2009
I've used vlookup before but his #REF problem has me stumped.
I want to concat two cells in the Source worksheet and vlookup into the Defs sheet, but I keep getting #REF(G4:G6) when I want to return the 2nd column in the Defs sheet. What's curious is when I modify the vlookup to return the 1st column I correctly get the lookup cells(G6:G9).
What's wrong with the formula in G4.
My requirement is I can't change the format of the Source worksheet because it contains macros that work with ERwin. And it is MUCH bigger than the attached sample so I will copy/paste the formula down 6000+ cells.
View 2 Replies
ADVERTISEMENT
Mar 16, 2007
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.
View 2 Replies
View Related
May 6, 2014
I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.
I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.
The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.
how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?
View 3 Replies
View Related
Dec 10, 2007
I found a small routine on msn that combines two olumns in a third. It works well.
Here it is.
Sub ConcatColumns()
Do While ActiveCell "" 'Loops until the active cell is blank.
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Here is my question. I need to alter this routine so that the macro will start at A1 and move to right until it finds its first empty cell and the combine all the strings of the cells it moved past.
View 9 Replies
View Related
Mar 27, 2009
I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.
I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.
View 5 Replies
View Related
Sep 17, 2013
I current have a Vlookup looking at the source data in another workbook stored in a drive so that only limited people have access. Because this information is linked to a drive I thought that it wouldn't return a value if you didn't have access to the drive but I was wrong. Is there a way to remove the Vlookup information if you don't have access to the drive?
View 4 Replies
View Related
Oct 29, 2008
how can merge 2 column together:
column time1 with text as 00:41
column time2 with text as 27
how can i merge it into a single cell in hh:mm:ss format , 00:41:27 ?
View 2 Replies
View Related
Feb 12, 2010
I am having trouble with a number of workbooks when using vlookup (using the wizard rather than entering the formula directly)
First I select the cell with lookup value, then try to select the table array on the source sheet but the cursor changes to the hand symbol and I can't click on any cells. I have had this problem with many different workbooks. The only solution I have found is to copy the whole sheet, paste-special-values into a new sheet and use that as source.
View 11 Replies
View Related
Feb 23, 2012
I'm trying to create a macro that will open automatically generated report outputs and manipulate the data.
The macro will run from it's own workbook (Report Macro.xls), sheets / cells within this workbook are available as 'helpers'.
So far what I have working ok is;
Via a browse dialogs in a userform in Report Macro.xls I select 5 different report outputs. I'm storing the full filename & path in Sheet Data, Cells B1:B6 (B1 being the filename & path to the macro file). In column C I have just the filename
The first file is opened and manipulated as required. The next thing I need to do with it is to open the second file (filename / path in B3) and put a Vlookup in cell 02 of the first file which will look for data in the second (the second file will only ever have a single sheet).
As a formula I'd be using
Code:
=VLOOKUP(A2,'Email 22FEB12.XLS'!$B$7:$C$13120,2,FALSE)
In VBA if I use;
Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],'Email 22FEB12.XLS'!R7C2:R13120C3,2,FALSE)"
It works great. However, the second filename will change and I need to take it from the macro file, sheet 'Data', cell 'C3'.
How can I use the contents of sheet Data, cell C3 in the VBA code above instead of the filename being hard-coded?
View 1 Replies
View Related
Jul 18, 2007
I would like a macro to invoke after selecting multiple rows that would be concatenated into a single row containing no dupes within each column of selected cells.
Details:
I would like to select '2 to n' rows. I would like the macro to concatenate the cells of the columns only within the selected range. However, if any of those cells are duplicates or blanks, I don't want them included in the concatenated cell. After the concatenation of all selected rows is complete, I'd like the macro to delete all the selected rows, except for the final concatenated row.
[NOTE: Rows 3 and 4 (below) would have been deleted by the macro.
Row 8 would become the final product in row 2's place.]
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1Sample*Data:*****2aaaxxxflintstone,*fredXYthese*are*notes3aaaxxyrubble,*barney*N*4aaaxyz*X*add*notes5******6******7Desired*Output:*****8aaaxxx
xxy
xyzflintstone,*fred
rubble,*barneyXY
Nthese*are*notes
add*notesSheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Nov 20, 2009
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
View 4 Replies
View Related
Dec 17, 2013
I have 2 sheets, lets say SheetA1 and SheetA2. I am using Vlookup to serial number & getting data from SheetA1 to SheetA2. But I want to modifty the data. If i modify the data in SheetA2 that modification should relect in SheetA1. Is it possible...
View 5 Replies
View Related
Jun 18, 2009
I need to concatenate and de-dupe. I want to find dupes, concatenate a column, and the delete the duplicate columns--all while maintaining data from other populated columns.
Here is the some example data:
#| Type | Name
1 | A | Bob
1 | B | Bob
1 | C | Bob
2 | B | Sally
2 | E | Sally
3 | A | Bill
3 | C | Bill
4 | E | Nancy
The result should look like:
# | Type | Name
1 | A, B, C | Bob
2 | B, E | Sally
3 | A, C | Bill
4 | E | Nancy
I am using Excel 2003.
View 5 Replies
View Related
Jun 6, 2013
I have an excel file - central database - that holds a lot of raw data organized in tables. There are more than 15 files (dependent files) that access this data using VLOOKUP formulas.
I now need to add a few columns to the central database and these columns can't be after the columns already existing, i.e. they have to be on the left of some of the existing columns. Inserting those columns is going to mess up all the vlookup formulas in the dependent files.
Is there a way change the central database file without affecting the vlookup formulas in the dependent files?
View 2 Replies
View Related
Nov 27, 2013
I need to do a vlookup of the values of Sheet 1 Column A (Ref) in Sheet 2 Column A (Ref).
The results will be the concatenated values of Sheet 2 Column B + C + D + E to be displayed in Sheet 1 Column B (Address).
I'm not sure how I can do this using formula.
I've attached a example.
View 6 Replies
View Related
Mar 28, 2013
I've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.
I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.
I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.
Right now, I have, on Source col L:
Code:
=MID(C2,1,9)
and in source M:
Code:
=IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2)
This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M
Then, in Log col R, I have an array formula:
Code:
=VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)
This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)
Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.
View 2 Replies
View Related
May 12, 2014
I have been working on a workbook that uses as a source information on Sheet 1.
Sheet 1
Each item reports a number of different details from column A to column BY.
Each row is for a different item.
25 rows of information.
Sheet 2
This is my master formula sheet, the one I've been using to create the page that reports and formulates information on a single item. The source for this sheet is taken from the cells in Sheet1!Row2.
Now that I have the sheet pretty much finished, I want to paste the same report on each succeeding page for a total of 25 sheets. I can copy Sheet2 and paste into each successive sheet, but then I need to change every single formula so that where it said AT2 it changes to AT3, where it was H2, H3, etc., from A to BY.
Problem: Can I use something that will automatically take the information for succeeding sheets and take as its source the succeeding row from the source page?
I don't know that I'm describing that sufficiently, so:
Sheet 3 would take as its source Sheet1, row 3.
Sheet 4 would take as its source Sheet1, row 4.
Sheet 5 would take as its source Sheet1, row 5.
Sheet 6 would take as its source Sheet1, row 6.
Etc., etc.
In the past I used the find/replace function, but this sheet seems way too complicated for that. As an example, one cell alone on my master sheet is
=IF(ISBLANK(ConnectMLS_export!S2),(IF(ISERROR(LEFT(C15,FIND("X",C15,1)-1)*RIGHT(C15,LEN(C15)-FIND("X",C15))),"",LEFT(C15,FIND("X",C15,1)-1)*RIGHT(C15,LEN(C15)-FIND("X",C15,1)))/43560),(ConnectMLS_export!S2))
so I would need to change S2 to S3 everywhere it appears, but there are probably more than a hundred places on the sheet that I'd have to manually change 2s to 3s, etc., for 25 sheets, which is why I'm wondering if there isn't a better way than going cell by cell, sheet by sheet.
View 1 Replies
View Related
May 13, 2009
When creating a drop-down list using Validation, is there any way to make the source a different worksheet in the workbook? Right now when I click on Source and select my list, it will not allow me to go to another worksheet.
If I manually enter a reference to cells in another worksheet, such as 'PCP'!$A$2:$A$250 it only shows that cited reference, not the actual list.
View 3 Replies
View Related
Sep 23, 2007
I am try to copy a worksheet "exactly" from one workbook to another, i.e. all formulas and range names reference the new workbook and not link back to to the workbook they were copied from. The code below brings the formulas and named ranges over still referencing the source workbook: SourceWB.Sheets(sht).Copy after:=TargetWB.Sheets(sht)
View 3 Replies
View Related
Jun 22, 2014
I have created a powerpoint that has linked charts from excel. Stupidly, I have saved both of these files (powerpoint and excel) on my desktop.
I need to save them on another drive (so they can be accessed by others) but i am worried that if i move the original excel file, it will break the links for the charts in the powerpoint file....
I have over 150 charts so I don't fancy going through and re-pasting each chart.
View 1 Replies
View Related
Sep 2, 2006
I have exported some raw data into excel. On another sheet in the same workbook I have a formatted sheet which will be my final report. I would like to know how I can copy data from one sheet to another via VBA code using the auto_open() function.
View 2 Replies
View Related
Jun 15, 2009
I want to design a spreadsheet report, but instead of importing the data from an external source, i would like the data to come from another sheet within the same workbook. The goal is that the data sheet will contain only raw data with no formatting, and the report sheet will contain all the formatting.
View 9 Replies
View Related
Jun 9, 2009
I have a master sheet named "Summary" and a number of following identically formatted sheets with lots of different names. I intend to pick out information from cell F7 of each of the following sheets and make the information show on the summary page. On the summary sheet in column A is the name of all the following sheets (spelt correctly), and in columns B, C and D are the spaces for the information I need on the summary sheet.
I need to set up a formula across columns B, C and D that I can copy down, which picks out the name of the sheet from column A and the cell reference within that sheet. So far I have experimented with formulae similar to the below which all keep returning with #REF! I hasten to add that all the Sheets are named and spelt correctly: =INDIRECT("'"&$A1"'!",$F$7)
The F7 would be changed for other cells in columns C and D, and so when I copied this down through all the following sheets on the summary page it should give me hte right results...
View 5 Replies
View Related
Sep 13, 2013
I am making a spreadsheet for use by my customers. Is there a way to leave cells that have formulas' in blank until the cells that make up the formula have entries in?
View 5 Replies
View Related
Feb 23, 2009
I have one excel file with several columns and rows of data.
Then I have a template excel with titles and some tables (no formulas, no macros, etc).
I need to generate a new workbook (based on the template) for each row of data from the source excel.
View 9 Replies
View Related
Feb 9, 2007
I have a sheet ( Graphs A) with 50+ graphs on all linked to a datasheet (Data A).
I now want to copy these graphs to another sheet (Graphs B) which links to a different data sheet (Data B). The data is in the same layout format etc.
The problem is that the find-replace function will not replace the sheet name in the charts series formula from Data A to Data B
Is there an easy way to change the source sheet for the new graphs?
View 3 Replies
View Related
Jan 17, 2012
I am looking for macro where it can generate multiple workbooks with a single worksheet data source. I have this worksheet with data which is look like this:
[IMG][/IMG]
This data will be places in 1 workbook for each of line with given file name as page number above those workbooks generated will have same information for each line but at different cell position such as: take this example at line 3
and
Microsoft Excel 2007
Window 7Pro 64bit
This line could be up to 50 lines of data. I try google search but found most about consolidate multiple workbooks to single worksheet. I do have VBA reference that I refer to from [URL] ......
How to put the information from the worksheet into specific position in the workbooks.
View 1 Replies
View Related
Aug 17, 2007
I'm trying to do is create a macro that will autopopulate information from a source sheet. What I have is a different route sheet for 25 or so technicians within my company on one worksheet.
I want to be able to have a source sheet that I can change daily and have it autopopulate the information needed into the appropriate cells/sheets from this one source sheet.
I am pretty sure there is a way to do this, but I'm not familiar enough with creating macros to do this myself, although if I had an example of one cell and a simple explanation of how to fit this to the information needed I think I could do this on my own.
View 9 Replies
View Related
Dec 28, 2006
I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following
Sub savemeas()
Worksheets("data").Visible = True
Sheets("data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub
to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be
ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
View 4 Replies
View Related
Jan 23, 2012
I have been really trying to get this sorted myself and I can get it to work with one exception. I can't get the sheet to lock with a password. It locks, but if I try to unlock it again, it does so without prompting me to enter a password.
Essentially I am wanting to unprotect two sheets, unlock the cells that were previously locked (so users couldn't enter data in them when the sheet is protected), then reapply the same password to protect the sheet, but now they will be able to enter data in the unlocked range of cells).
The second macro is to reverse the changes made in the first and 're-lock' the unlocked cells) (and again apply the same password to the sheet).
this is the code I have used.
Sub PART_YEAR()
Sheets("2012 Calculator 1 week").Select
ActiveSheet.Unprotect "taado"
Range("D20:D23").Select
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("2012 Calculator 2 week").Select
[Code]...
I am assuming I have superfluous code in there, in addition to my mistake with the reapplication of the password.
View 3 Replies
View Related
