Is it possible when i am in my current workbook to refer to the value in another cell in another workbook within a formula?
Example:
I have a workbook named "Sample1.xls" that contains a spreadsheet "revenue" and in cell C24 is the value that i want to have in my current workbook.
I wanted to exchange data between two cells. i.e if i have mistakenly put one value of A1 in A2 and A2 in A1. How can I exchange it. I am asking this bec by copy paste method one cell is overwritten so the value of that data is lost.
e.g A1 has value 200 but the correct value is 150
A2 has value 150 but the correct value is 200
How can i exchange/swap the values between the two cells..
I have a large employee spreadsheet list with first name then last name in the same cell. Is there a formula that will change the order so it will read last name, first name?
View 9 Replies View RelatedIs possible exchange text in a cell like this "street San Miguel de Allende-Cp 4780 031 1304" to "street San Miguel de Allende, 1304 -Cp 4780 031". how to do this?
View 3 Replies View RelatedAs you can see in the attached sample file, I have different currencies, that all need to be converted to USD.
The exchange rate are displayed on sheet 2.
Currently the formula just picks all values without considering the exchange rate to USD. What I need to have, is a formula which, based on the currency, picks the correct exchange rate and outputs USD.
Apart from that, another thing the formula should consider is the value of G1 in sheet 2. With G1, the user can later define a specific type to be calculated by simply pressing down the button and chose either FG or RM.
The formula needs to:
Find out what currency the value is in and automatically convert it to USD via the exchange rate values. It also needs to consider the value of G1 in sheet 2 in order to filter out the other values. If, for example G1 is FG, then only FG should be taken into the process and therefore only values where type FG is marked.
I have a sheet which calculates payment amounts.
Column titles:
Hours | Rate of Pay | Total
In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.
What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.
H=2 x rate of pay = total
I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.
I work with different currencies in my company, now I would to get an up to date state of the cask book. So I have $250, and 500EUR, now I want a formula (connecting to internet) that automatically multiplies the $250 with the current exchange rate, so I know how much I have in Euros in total.
View 5 Replies View RelatedI have a financial sensitivity sheet setup by year, where for example below each year is a cell you can change the exchange rate in and it effects the outcome of cash flow as these cells are linked to part of the finances. Then I have a "reset" button setup that is assigned to a macro. When you click the reset button all cells in the exchange rate row will be changed to a value that is entered in a "base case" cell. That way various years can be changed but also everything can be set back to the default or base case. My macro for that case is this:
View 5 Replies View RelatedI work with a broker that have an API function and all the prices goes directly to an Excel Sheet.
What I want is a way to keep track of this prices in a new sheet, as they change. Like a historical price.
Is there any way that excel can do it for me?
I have two different columns of currency time series data with dates that do not align. One trades Monday-Friday, the other seven days a week.
I want to either a) add dates to the shorter series that only trades Monday - Friday (and just use the closing price on Friday for the Saturday and Sunday dates)
b) (not as good an option, as I will lose data) is delete the Saturday and Sunday data from the longer series that trades on the weekend.
Dates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C
And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.
In Sheet 2 I have a table of the historical exchange rates like this:
DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089
I'm looking at publishing a number of spreadsheets to the Public Folders section on our local Microsoft Exchange Server.
In Excel I can select File > Send To > Exchange Folder...
but I was wondering if there is a way to automate this in a macro?
I would also like to specify a folder it should be posted?
My task is to somehow access [url] and copy the exchange rates on the table to my Excel workbook. Obviously a macro will not record outside of the Excel program, so I am just wondering if this is possible at all (without hacking of course). My plan B is to have the user manually enter the exchange rates.
View 3 Replies View RelatedIs it possible in Excel 2013 to have one cell show current exchange rate of Euro to dollar?
View 1 Replies View RelatedThe file is a simple sheet which upon entering the actual/Invoice cost (C5), calculates the estimated landing/final cost (C8).
In between the process involves changing the currency from US$ to PKR, make some calculations, and changing back the currency again to US$.
The default rate of currency exchange is set to ave 60 (C12). However if the user knows the current rate he can put it manually in C6 and sheet will make all the calcs on this instead of using the default rate.
Problem:
Everything is working just too perfect. But probs occur when i manually enters the exchange rate.
It does successfully changes the US$ to PKR and calculates everything perfectly but doesnt reverts the final cost back to US$ successfully instead it keeps using the default value instead of user's value
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View 7 Replies View RelatedYou have two Workbooks open. Workbook 1 and Workbook 2.
You COPY all the data from Workbook 1 and PASTE the data as a 'Link' into Workbook 2.
You then save and close both Workbooks.
You then open Workbook 1 and edit the data. You save and close Workbook 1.
QUESTION
Will the linked data in Workbook 2 update automatically (i.e., without opening the file)?
I have an excel spreadsheet supplied by a client, let's call this the master sheet, with about 500 URLs and I've been asked to gather information from Google Analytics and place in a spreadsheet. This spreadsheet can't be changed as it goes into their CRM system.
On the master sheet, we have a list of URLS, what I need to do is go through each URL and place the number of visits to each URL on a weekly basis.
Google Analytics excel spreadsheet, will spit out a list of URLs and the page visit numbers.
My question is:
If I have both spreadsheets open, the master spreadsheet and the information from Google Analytics spreadsheet. Is there a way of having both sheets open and doing an exact match on the URLs between both spreadsheets, so we can copy and paste the information directly.
Or even a formula that says something like:
If the master sheet spread has the same URL in the Google Analytics spreadsheet, paste the relevant information into the correct cells?
As you can see, doing this manually will take a long time. I'm just looking for a productive method to save time.
Itransferring data across two work books I have.
Every month I get sent sales figures of products, from now I have been typing in every sales figure which is long and boring. So I was wondering whether there is a way to make excel match the products on each workbook (mine and the one I get sent) seeing as there is a product number on both workbooks and then have excel automatically fill in the field I type manually which are quantity and value. So excel would see on the one workbook that product X has sold 10 units with 1000 value and then would go across to my workbook and fill that in the area I need on mine matching from the product code?
I have to workbooks and I want to populate one of them with data from the other. The receiving document has the fields Account and SubAccount which I need to match up with the account and subaccounts in the other workbook.
However, in the other workbook, the line looks like "1000 Wages 000001" (which is account, name, subaccount). I can't find out how to have my receiving document scanning the providing document and when it finds an account and subaccount (in the above string) that matches the account and subaccount in the receiving document, to bring over the data.
I recieve a data file on a monthly basis. Is there a way to take all of the monthly worksheets and combine into one workbook without doing a copy paste every month?
View 1 Replies View Relatedi had received a new project. i want to compare the data among 2 different workbooks. there are book1 and book2 which i attach at here. if there are 1 cells is different with both workbooks, then the whole row will copy and paste to a new workbook "output.xls".
i also attach the "output.xls" at here.
the background color i highlighted which data is different, just for easy to view. so can ignore the color.
A while back someone helped me out with a code to pull data out of all workbooks in a folder.
View 10 Replies View RelatedLook in folder and find workbooks with date in name:
Date format is MMDDYYYY
Workbooks have multipule sheets
Look at columns in worksheet E and G.
If E >=10 and G is "ENABLE" copy A:G and
the tab name and paste in the workbook that has
this VB code.
What I am going to do is create a workbook
with a button on Sheet1 and put your code
behind the button that will tackle this great
feet that I do manually that takes about
3 days to do.
Is it possible too do this if they are on the same computer. Lets say I want all the data on page3 in one workbook, data on page2 in another and have it show up in another workbook. If this can be done can it delete cell entry that match?
View 14 Replies View RelatedI'm trying to copy data from several workbooks into another - in Excel 2003. The following code works perfectly except when there is only the header row in the target workbook. Then I get the following error message, "Run time error 1004. Application defined or Object defined error". How can I get it to find A2 the first time - without coding A2 in?
View 4 Replies View RelatedI am working on a project that requires me to copy columns from multiple workbooks to a main one. I am a new comer to VBA what I could come up with gives me an error. Here's where I am right now:
Code:
Sub Import_Click()
Dim MainFile As Workbook
Dim ComFile As Workbook
Dim RDFile As Workbook
Dim UTIFile As Workbook
MainFile = Dir("C:ModelingProcess FilesMain File.xlsm")
ComFile = Dir("C:ModelingProcess FilesModeling - Commercial.xlsx")
[code].....
I have a series of of quesitons but honestly, I think it'll be answered by ONE question.
This is the my request as described below:
Bob, Jim, and Lisa are Project Managers (PM). Each PM has a Project Template Workbook with required info they need to fill out PER job. This Template workbook has a VBA button that submits their info to the PM Master File.
The PM Master file is a seperate workbook that sums up each project as filled out by the Project Template Workbook.
What I need is the code to either copy/paste the values from the template to the master file ensuring that the most recent addition of information goes to the next row and doesn't paste over the existing data.... or ... I need a different approach.
I have two workbooks book1.xls and book2.xls
I want to load data in sheet1 of book2 to sheet1 of book1.
This sheet1 of book2 has a table of 10 rows and 10 columns.