Im currently using excel 2007 and have my workbook saved with the extention .xlsm. I have put a vlookup formula in a cell and have placed $ constant sign se the formula will look in the same column but different rows. However, when I copy and paste the formula down the rows, the formula captures the same data.
I have an excel 2007 file which contain a lot of macros. File size is 100MB. This file is not developed by myself, it's post in the webpage and let the members of the page to download on daily basis.. I've been downloading and using this file for over a year with no problem until this week.
Problem: During opening the file. There is a message box popped up, saying "Excel found unreadable content in filename.xlsm'. do you want to recover the contents of this workbook ? If you trust the source of this workbook, click Yes." When I click "yes", excel is trying to repair the file. Once it's done, I could see only the contents, however, all the macros are gone. When I copied this very same file and open in other notebooks, everything is working fine...
Assumption: Couple days ago, I was writing/testing macros in other workbooks and also opened this particular file. I might did something stupid which cause this problem...
During the same time, I used to have the same problem with another file. I solved it by copy and open with another PC then copy macros and paste it to the file on my notebook.
This is designed to be run on bid proposals, to calculate special discounts. My idea was that whoever was designing the bid would open this file, which has the discount information and the macro, and then run the macro on the file containing the bid.
This macro works. It worked really fast when it was in my personal.xls and referenced an .xlsx file for the vlookup. It was slower, but still tolerable, when I moved it to the .xlsm file and it still refenced the .xlsx file (an oversight on my part). Now that I fixed the vlookup reference to the .xlsm file, the same one that the macro itself is in, it has slowed to a crawl. What is going on?
I have a macro-enabled template file in Excel 2007. I would like the user to be unable to save in any format other than macro-enabled. They should be free to choose a path and filename, but not the file type.
I presume this means some VBA code in the before save event, but I don't know what.
I have a button the runs Application.GetOpenFilename And puts the output filepath into a label. I then have another button that opens a inputbox asking to change the file name and then copys the file from above path to my destenation.
when setting the destination i use ThisWorkbook.Path & Application.PathSeparator & "Images" & Application.PathSeparator and the imputbox value as the filename. doing it like this it wont alocate a extention for the file ".jpg,ect". The label.caption has the .ext and i would like to be able to store everythink after the . as a variant.
I am using the following save as function on a template to save it as a workbook:
Sub SaveFileAsDate()
Dim WSName As String, CName As String, Directory As String, savename As String
WSName = "TMG Work Order"
CName = "C4"
Directory = "Maintenance Service Agreements"
savename = Sheets(WSName). Range(CName).Text & Sheets(WSName).Range("F3").Text & Sheets(WSName).Range("K4").Text & Sheets(WSName).Range("K8").Text
If Directory = "" Then Directory = CurDir & ""
On Error Goto errorsub:
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xls"
Exit Sub
errorsub:
Beep
MsgBox " Changes not saved!", vbExclamation, Title:=savename & ".xls"
End Sub
It works fine but I need to be able to capture future changes to the sheet as the same saved name but to add and extention like (-Rev1). Once it has been saved as -Rev1, each additional time it saves, the -Rev number would increase. I searched the forums but didn't find an exact match.
I have some code which works perfectly and emails the active workbook (which the code is within) as an xlsm to a email address. All is well.
However - how do I change my code so the workbook is sent as a CSV?
[Code] ....
I thought putting the following would work after 'ActiveWorkbook.FullName' but it did not:
[Code] .....
My office just converted to 2007 and I am running into a problem with code used to save a workbook. I need to use a dialog box as the file could be placed in several different locations which the user needs to choose.
When the save as dialog box opens it defaults to being saved as the xls type. I would like to modify the following code so the file type drop down defaults to the xlsm type. Below is my code.
Sub SaveFile()
Dim Fdate As String
myDate = Range("HeaderPage!B13")
myFile = Range("HeaderPage!b8") & "_" & Range("HeaderPage!b9")
Application.Dialogs(xlDialogSaveAs).Show myDate & "_" _
& "_" & myFile & ".xls", 1, , , , False
End Sub
Well to start off I am using Excel 2007, I have an invoice template set up across two sheets (Invoice & Price Table), on the first page is the actual invoice that needs to be filled in. I have made an userform for being able to fill in the invoice number,what type of test was done, and also things like hours and miles (an their costs per also)
. Now I am wanting to try and get it to where I can have the form look at the type of test cell and from there select the price located on the second sheet that is appropriate to that type, the thing is, there are two possible prices that it can be depending on if the company wanted the results "rush". I was suggested that I could use a check box on the invoice to indicate in the spreadsheet that it is a rush, and somehow link that in with a vlookup to be able to get what I wanted, but I'm not sure exactly how I would go about doing that.
Never got this message before with the old excel so i dont know how to resolve. I get this. "Invalid Reference. File conversion can not contain formulas that reference cells beyond a worksheet size of 256 columns(columns IW or higher) or 65536 rows." Im doing a VLookup and when i input the table array it is always invalid when i know it is valid. The spreadsheet im referencing does not have any formulas beyond that range either. Another guy i work with is now getting the same error with the new excel.
View 9 Replies View Related I have two worksheets, contractor & list. Assume that Column (A) on the "contractor" worksheet is a named range from Column (A) on the "list" worksheet. On the "contractor" worksheet I would like to put in the contractors name, and auto populate the pay value in column (B). I have been using a Vlookup formula, but need to automate this process a bit more.
"Contractor" worksheet - Two columns: (A) I will input the contractors name from a dropdown list based on name range from my "list" worksheet. (B) is where I would like to populate the pay base on column (B) in my "list" worksheet.
Contractor (A)
Pay (B)
Jill
Fred
Jack
I am using Excel 2007, I have a customer sheet and a sales sheet in the same work book. My customer sheet lists all customers with a single entry only each with a unique customer code. My Sales sheet lists at an item level but does include the customer code so if my customer has purchased more than one item the customer code can be repeated on the Sales sheet on multiple rows.
When I do a vlookup to display the amount each customer has spent on the customer sheet, only the very first sales figure is displayed, if further down the sales sheet the same customer has made another purchase I want this figure to be added to the first figure and all I want to see on my customer sheet is the final total figure.
The vlookup formula I am currently using is:
=VLOOKUP(G2,Sales!$A$2:$B$1785,2,FALSE),
I'm thinking maybe a Vlookup will not do what I require, maybe I need a different formula/function?
I'm looking to simply find a function that allows me to lookup a certain value in a table of data and return the value in a specific column. So if I looked up the current date, 6/27/2011, it would return the week it is in (26). I dont want to be limited to just the monday column however, as the dates I will be throughout the entire week. Is there an easy way to do this in excel 2007?
MondayTuesdayWednesday ThursdayFridaySaturdayWeek
5/2/20115/3/20115/4/20115/5/20115/6/20115/7/201118
5/9/20115/10/20115/11/20115/12/20115/13/20115/14/201119
5/16/20115/17/20115/18/20115/19/20115/20/20115/21/201120
5/23/20115/24/20115/25/20115/26/20115/27/20115/28/201121
5/30/20115/31/20116/1/20116/2/20116/3/20116/4/201122
6/6/20116/7/20116/8/20116/9/20116/10/20116/11/201123
6/13/20116/14/20116/15/20116/16/20116/17/20116/18/201124
6/20/20116/21/20116/22/20116/23/20116/24/20116/25/201125
6/27/20116/28/20116/29/20116/30/20117/1/20117/2/201126
7/4/20117/5/20117/6/20117/7/20117/8/20117/9/201127
7/11/20117/12/20117/13/20117/14/20117/15/20117/16/201128
7/18/20117/19/20117/20/20117/21/20117/22/20117/23/201129
7/25/20117/26/20117/27/20117/28/20117/29/20117/30/201130
8/1/20118/2/20118/3/20118/4/20118/5/20118/6/201131
8/8/20118/9/20118/10/20118/11/20118/12/20118/13/201132
8/15/20118/16/20118/17/20118/18/20118/19/20118/20/201133
8/22/20118/23/20118/24/20118/25/20118/26/20118/27/201134
8/29/20118/30/20118/31/20119/1/20119/2/20119/3/201135
9/5/20119/6/20119/7/20119/8/20119/9/20119/10/201136
9/12/20119/13/20119/14/20119/15/20119/16/20119/17/201137
I have used this template for a Vlookup and have been having troubles that I simply cannot pin down. Some duplicated numbers in Columns A and C will NOT appear in Column B. I am also trying to work it out where when the results are displayed that Excel will tell me in a pivot how many loans are on both lists and how many loans are on each specific list.
View 7 Replies View RelatedExcel 2007
I have a workbook (book1) that is modifying multiple other workbooks (book2). I need to do a vlookup of a sheet in book1 from a value in book2. I then need to insert the value found by the lookup into G7:H7 (merged cells) of book2.
value is in column B of page "Routes" of book1. Found by value B3:C3 of book2. The vlookup looks in column A of book1. The found value is put into G7:H7 of book2.
In a step by step, to possibly make this more clear:
1) check value of B3:C3 in book2
2) find value in Routes sheet of book1, column A
3) upon match, grab the value of column B
4) insert that value into G7:H7 of book2
[URL]
I am using Windows xp and Excel 2007. I'm creating a user form and have a vlookup formula that goes to a table and looks up calories and sodium based on the text entered by the user. My problem is that I want to be able to populate the 3 columns with information and then sum the values that are returned to the Calories and Sodium columns. Is that possible with Vlookup?
=IF(ISNA(VLOOKUP(F8&G8&H8,Food!A:E,4,FALSE)),0,VLOOKUP(F8&G8&H8,Food!A:E,4,FALSE))
Water 24 ozOther DrinksGelsSolid FoodCaloriesSodium
00
00
00
I have data in a sheet in Excel 2007 that I am trying to move to another sheet in the same workbook.
I am using Vlookup to move the info from one sheet to the other.
However it seems that if the data in the original sheet cell is too large I get a #VALUE! error.
If I copy and paste the info from the cell it is fine.
How do I copy the information (hyperlink) in the original sheet cell to the destination sheet cell?
Here is the formula that I am using.
=HYPERLINK(VLOOKUP(A67,'verified1-4-12'!B:E,4,0))
verified1-4-12 is the original sheet name
I am trying to get one cell to look at different sheets. if the first sheet doesn't have a value, than look in the next sheet. current formula is
=IF(ISNA(VLOOKUP(Q2,'sheet2'!$D$2:$K$1807,7,FALSE)),"",VLOOKUP(Q2,sheet2'!$D$2:$K$1807,7,FALSE))
I am having cell return blank if no value is found. it is currently looking only at one sheet.
I don't want it to combine the values like this formula does.
=(VLOOKUP(Q2,'sheet3'!$D$2:$K$200,7,FALSE)&","&VLOOKUP(Q24,'sheet2'!$D$2:$K$1807,7,FALSE)) #.###,##.##
sheet 1 is where the cell I am writing the formula is in, and I want to look in sheet 2, 3, and possible 4 for a value, I want to only have the greatest value show between the sheets. is this possible.
I am using excel2007.
I have two workbooks one names "Quote" other named "product supply". In "Quote" i would like to have somthink like a Vblookup that on a givin line can enter price's, disciptions and other data automaticly on that line, From the "Product Supply".
Example:
"Quote" A:1 Dropdown list. Select product, Then refers to "product supply" of that product, then trasphers data into "Quote" A:2
i have a simple question regarding vba code involving opening/eleboration/closing of different excel workbooks' format. I saw that with xls/xlsx the macro works with relatively high speed, but it is very slow when it comes to xlsm file. Is there an explanation? Does a command which enhance speed for xlsm file exist?
View 8 Replies View RelatedI'm trying to open a .xlsm on an Android tablet with only OfficeSuite installed. Initially, that didn't work. After installing a DocumentsToGo application, it shows me my spreadsheet, but none of the .jpg's that are visible in the original file. I copied all .jpg's to the tablet; both the .xlsm and the folder with the pictures are in the same parent folder, to simulate the original folder structure in Windows. Still the pictures will not show.
View 1 Replies View RelatedI know there are plenty of examples on how to do this at a basic level but I am looking at making it a bit more custom.
I would like to put some variables on a sheet in a workbook and use those to drive the behavior of the code.
EG I would like to specify the save-as-pdf path (eg c:users.etc), the email addresses to be emailed TO (specify a range eg A1:A14 where each cell has a different email addy in it), like wise for CC and then BCC (new ranges), also be able to specify subject of email (EG Cell F1.value is email subject).
I made the excel file with the VBA source code, saved it, opened it many times, and all at sudden I cannot open it anymore. When I try to open the file I get the following screen:
actually, Excel asks me if I want to enable macros or not, but this window is different than usual window:
when opened file with macros works fine.
So, in my case I click the Enable Macros button of the first picture and my file seems to open in some hidden instance, because the file name isn't writen in the title of the Excel window.
When I close the Excel window it asks me if I want to save changes to my file, and that proves that it was opened in some hidden instance of the Excel.
I have to say that I was not playing with the security options of hiding any part of the file that I want to open, and which doesn't open properly.
Code:
=D5-VLOOKUP($C5,$C$45:$F$80,2,0)
I have formulas such as above in my Worksheet. So the above is in Cell H5
For the Range $C$45:$F$80, I am inserting rows (So moving down the data) and copying data from $C$5:$F$40 as values into the new space in $C$45:$F$80
I am doing the above using a macro, but when I run it I want this
Code:
=D5-VLOOKUP($C5,$C$45:$F$80,2,0)
to stay static, but instead I end up with
[CODE=D5-VLOOKUP($C5,$C$85:$F$120,2,0)[/CODE]
So my Table Array $C$45:$F$80 changes to $C$85:$F$120
How can I keep it as $C$45:$F$80
The other references in the formula seem to stay as I want them.
Sample data
2014
2013
2012
Country A
2%
3%
Country B
5%
6%
7%
[code].....
Now, whenever I type in any country it should show me the most recent figure available for that country.
Suppose, if I type Country A in, say cell A14, then it should give me output 2% in cell, say A15. When I change that same cell to country C, it should give me output 10%, country B should give me output 5%.
Currently I have used IF and multiple/nested VLOOKUPs to arrive at this output. Is there any better way to do this with VLOOKUP and OFFSET?
I am using Excel 2007.
I have an MS Excel 2010 xlms file which is password protected. Unfortunately, the person who has the password has left the company!
Which is the best tool to remove the password / recover the file?
I'm creating a template xlsm file that has built in marcos. This file will be used by the Engineer as a template, where they fill in the data, and then send it to the team. when the case in finished, they will use clear data macro.
The Problem I want the template to have a macro runs with startup of the file that checks for the lastest template in a network folder (shared folder where they will have read only access). If the used file is the latest one, OK. If not, give a custom msg with the location on the network.
I have two data sets across two worksheets. The first worksheet contains sales order numbers (Sheet1:column A) and other data . The second worksheet contains sales order numbers (Sheet2:column A) and the product details.
On sheet1, I had to manually duplicate a sales order number (inserting another row) if the number of units of the order is greater than 1. I then need to fetch the product details from sheet2 for each unit for that specific order number. However in sheet2, there are multiple products for one sales order number.
How do i create a lookup/match to fetch the product details for each sales order number without duplicating the product details if there are multiple products for one sales order number?
I know a simple vlookup function will return the values that it matches first and that is not what i want.
Example: Sheet1
Column A (Sales Order#) | Column B (Quantity)
0417436GPCP | 1
0417436GPCP | 1
0413412FACY | 1
0413412FACY | 1
[code].....
very basic Excel user (using Excel 2007). Trying to see how I can use the VLookup function from multiple worksheets to get an average. I know it can be done, but just not sure how to go about doing that. My spreadsheet is attached - basically what I want to do is to get an average for the individuals listed in the "Consensus" tab for the figures that appear in Worksheets "1", "2" and "3".
View 14 Replies View RelatedWhat I have attached is spreadsheet I've built to quickly generate an army list for a tabletop game. Anyone can click on the Force Organization Slot (FOS) and choose the type of unit and from there, select the specific unit name attached to that FOS. Each individual unit has a list of options/upgrades currently attached to the sheet from cells AD through AX and the points cost listed next to each upgrade.
For example, when selecting the Rhino unit from the Dedicated_Transport FOS, there should be 4 drop down lists of selectable upgrades (Storm Bolter, Hunter-Killer Missile, Dozer Blade, and Extra Armor). Currently, I have only named the ranges for the Rhino and Razorback units in order to get this working properly without having to go through the entire list of units and change names/create ranges/ect.
What I'm aiming to do is to create an individual drop down list that is directly related to the Unit Name cell in order to populate a complete and selectable upgrade list for each unit. I have played around with some functions that work in the spreadsheet (see cells X10:X12) but do not work properly with Data Validation.
Oddly enough, when I enter this formula under Data Validation:
=IF($A$3<>$AD$1:$AD$182,VLOOKUP(A3,Options,2,FALSE),"")
I do not get an error, yet the drop down list does not allow me to select anything as I thought it would.