I have a question and wanted to know if it was possibe and if so how. I wanted to link cells from one workbook to another (Which I know how to do) the problem im having is the linking works but why doesnt the cell color chang like it is in the workbook im linking from? Example: If A1 has text (Me) with the cell being green then the linked workbook would only have the text (Me) and not the green cell.
View 4 RepliesI have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.
View 3 Replies View RelatedI am trying to link data from one workbook into another and get it to work but it puts in sample data or dates which I dont want. How can I set it up were the cells are blank until data is input in the main sheet and then linked to the destination sheet?
View 4 Replies View Relatedhow to link cells from one workbook to another and being able to update figures using the refresh (!) button?
View 6 Replies View RelatedUsing Dropbox, a lot of people will fill in formulas in personal workbooks from using data from another, central workbook. Since I cannot control where they install Dropbox or where they keep their personal workbooks, I must account for that in functions.
Now, my idea is to make them define the directory of the source in their personal workbook in a specific cell and then using that as a basis for the rest of the functions.
Example
Cell A1: C:Usersuser1Dropboxfolder (copy paste from path in Windows Explorer)
Cell B1: A1&[CentralWorkbook.xlsx]SheetName'!$H$1
Basically I want the unknowing user to copy-paste the path of the central workbook into a specific cell in their own workbook and then build my functions bsed on that. How can this be done?
I am looking for a very simple script that will achieve the following:
On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:
From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
I have values on master sheet and want to link those values to source documents in the same workbook. But the values on master sheets are made up of more than one value on another sheet.
For example on master sheet I have $137,000 that is made up of values in P7 + Q7 + P57 + Q57 on source sheet. If user wants to see where this number is coming from; the user will click the number to go to source document.
On a single valve I linked to the source sheet to a single cell. One thing I was thinking, using conditional format, so if one of the cells is selected in source document the other cells that combine the total of the master sheet cells should be highlighted, just my 2 cents.
I want to link cells B1 thru B16 to Cells F1 thru F16 receptively.
However, at times I need B1 thru B16 to show other columns 1 thru 16.
I would like to type in the column designation in A1, ie. F,G,H,I.....and have the values of those columns shown in B1 thru B16.
I have one cell with $20,000 hard coded (cell A1). In another cell I want the cell to say "20,000 Capital Raise" (cell B1). And i want B1 to link to A1 so that if i change the number in A1 it will also change in B1.
Here's what i have in B1 so far: ="$"&J6&" Capital Raise"
This produces "$20000 Capital Raise" in cell B1.
So the only thing i'm trying to figure out is how to get the comma in the $20,000 so it will read "$20,000" instead of "$20000".
I am trying to create a VBA to:
1. Search workbook for a specific values and then to color that cell with a corresponding color.
2. Search workbook for a specific values and then color other cells underneath (the next 3 merged rows after the cell containing the value) with a corresponding color.
I have a macro that generates multiple sheets in a work book then formats the data on each sheet the same, I would like to highlight the same two cells in each sheet with a background color of yellow. My format code is below, I can seem to get fill to work with the sh. property
For Each sh In Worksheets
sh.Range("O1").Value = "Total Amount"
sh.Range("P1").FormulaR1C1 = "=SUM(RC[-2]:R[99]C[-2])"
[Code]...
I have a workbook Book1 that has 4 sheets, one of those sheets needs to be an exact replica of a sheet from another workbook Book2. I tried to directly link the data and use Vlookup's but the problem is that when someone opens Book2 and inserts more data (Inserting rows) it does not show up on Book1.
View 3 Replies View RelatedI have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?
For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?
I'm using a macro to try to pull data from a worksheet but it only works when its opened. I found a macro that is supposed to get data from an unopened worksheet but I don't understand VBA very well so is there anyone here that could tell me how to merge my macro with this one to allow me to access the unopened worksheet?
This is the website for retrieve data from unopened worksheets:
[url]
And this is my macro I'm trying to use:
Function HyperLinkText(pRange As Range) As String
Dim ST1 As String
Dim ST2 As String
I'm trying to link specific data from one workbook to another. So from the demo I've attahced, I need all of the 3rd row "ATP BASIC SERVICE : 1" to be linked to another workbook.
So normaly I could copy the row, go to the other workbook, "paste special" and link it to a column.
The only problem is the "ATP BASIC SERVICE : 1" data will not always be on the 3rd row.
So i assume I would need to automatically search for "ATP BASIC SERVICE : 1" and if it is found, copy the column over to the other workbook.
I'm looking to link/embed a summary sheet from one work book to be displayed in another. When i update this summary sheet i wish for it to update the linked work book.
View 7 Replies View RelatedMy question is as follows:
I have two workbooks in the same folder on a shared drive. Workbook2 is linked to workbook1. I have protected Workbook1 with a password protected to limit access.
When my collegues who use workbook2 try to open it, it asks for the password for workbook1 before opening. When they ignore that and go a head to open the file. The new information from workbook1 is not updated into workbook2.
How can i mentain my password in workbook1, and keep the files linked such that they can be able to see the new information in workbook2 without knowing the password for workbook1.
I have an excel sheet that has a list of codes and next to each code there is a price list for that particular item.
What I am trying to do it: I have another workbook that I use to type up quotes. In this workbook there are 2 columns: one with the code for the item and then another with the price thereof.
What I'd like to do is when I enter the code of the part, I'd like the price to be referenced from the other workbook and be entered into the price column.
I will attempt to describe my problem the best I can. Thank you in advance:
I have a series of charts (and the tables in which the data for the charts is stored) located in a worksheet. I copied and pasted each one of these charts next to its original. I then moved each one of the new, pasted charts into a single chart sheet at the end of the workbook. Is there a way, via macro or otherwise, to update the charts now located in chart sheet if any changes are made to the original charts?
I am trying to do a countif in another workbook.
I enter my formula:
=COUNTIF('path[filename]tab'!$AH$11:$AH$300,"yes")
So this formula works fine when I have the other workbook open, but the next day when I open up the file (with the formula in it) and I click "update" button on the "this workbook contains links to other data sources" popup I get a #VALUE! error in the cell. Does anyone know how to correct this?
As soon as I open up the linked workbook the error goes away, but I didn't think this is how its supposed to function. I thought you didn't have to open up any linked documents. All the documents are on my desktop, so its not a share drive issue.
To test it out, I tried doing just a simple link to cell A1 in the other workbook and that works fine, so I'm thinking its the COUNTIF function that causing the problem.
I am importing data from an outside excel file to several other files. I requested that the data be refreshed every time I open the file that references the outside excel file. Whenever I have a file open that references the outside excel file, I can only open the outside excel file in a read-only state. Why is this, and is there any way around this issue?
View 2 Replies View Relatedive managed to come up with this formula: = OFFSET( 'G:gailMVTR2007[B052,B261A.xls]B261A45'!$B$5,COLUMN()-3,0,1,1)
which pretty much does the same job as a paste special/translate, this works fine when both files are open and updates all the values that i could ever enter, however, when i open only the file containing the link and press "update" it returns a "#value!" error, i dont understand because it works fine when the other sheet is opened and there are no values other than a single number used.
I have a question regarding linking the content for data validation lists stored in a different workbook.
The scenario is as follows: I will have say 50 users using a template which contains various dependent drop-down lists. These lists work by using the INDIRECT function which calls the correct drop-down based on the previous selection. The drop-down entries are currently stored in named ranges on a hidden worksheet in each user's file.
However, if I need to make a change to the content of these lists, I will have to open all 50 files individually and change each one. So I'd like to have a master file to which all the templates would be linked which would contain the entries for each drop-down. So when I update a list in the Master file, it would automatically update the drop-down lists in each user's template with any additions deletions.
My question is, how do I create the links between the user templates and the master and have it so that the list ranges will be dynamic. So if a drop-down list contains say 10 entries and I add 2 more in the master, that those 12 now appear in each user's template?
A simple one for you, but again my Google skills seem lacking. This must be common practice but until one knows the key combination...
I have created a summary sheet with lots of formulas which analyse other sheets in a workbook. I want to copy this summary sheet to a seperate workbook to use as a template.
Of course, the copying process always links the new (template) sheet to the original workbook it was copied from. How does one copy without forging this link? All I want are the formulas copied across totally unchanged; I really *don't* want to manually edit 50 or so formulas!
At our small firm, each staff member keeps a small excel spread sheet on the network that contains their "work in progress." This spreadsheet is roughly six columns wide and roughly twenty lines long.
What I am attempting to do is have one "master" spreadsheet with a tab for each staff member that links their "work in progress" so that partners and managers can easily see how much work each of the fifteen or so staff members have.
I can create fifteen different tabs and fifteen different files on the network and link =[Staff1.xlsx]Sheet1!$A$1:$G$25 =[Staff2.xlsx]Sheet1!$A$1:$G$25 =[Staff3.xlsx]Sheet1!$A$1:$G$25 and so on.
If I make a change to the layout of the work in progress sheet, I have to delete and copy the file fifteen times, then go back through and re-link fifteen tabs to fifteen workbooks in order to keep it uniform.
In the code below I am defining ranges in a sheet and then using an offset and a scrollbar linked to the offsets. Then I make a graph using the data so that I can use the scroll bars to move the linegraphy left or right to compare the data sets.
My problem comes when linking the values to the graph. I know I can do this by referring directly to the xls file, but as I have loads of different files I want to apply this to I would rather use an active workbook reference...however this does not seem to work. The current code referring to the sheets works but then I have to go into the graphs and change the source data to get them to operate off the scroll bars.
I am trying to implement the idea of creating an application with two workbooks: a hidden code workbook (CodeWb) that holds all of the forms and macros, and a separate visible data workbook (DataWb) that uses the macros in CodeWb. The DataWb houses a little bit of VBA code to open the CodeWb, which then supplies the programming power to handle click events, etc., performed by the user in the DataWb.
I have advanced to the point of being able to open CodeWb from DataWb and trigger execution of a macro named "DataFileOpen" in CodeWb. This macro initializes things in DataWb and creates buttons there on a particular sheet, using the line:
Application.Run "CodeWb.xlsm!DataFileOpen"
So far so good. This works fine. My problem is that nothing happens when I click on the buttons created in DataWb, with their event handling code residing in CodeWb. Obviously, I'm missing something that maintains a connection between the two workbooks.
How can I use the code in CodeWb to handle all events occurring in DataWb?
On one workbook I have "column A" that lists serial #'s, one serial number per cell. What I need is to be able to take all the non blank data from that column and copy it to another workbook with all those serial #'s in 1 cell numerically sorted and separated by commas.
View 1 Replies View RelatedI am currently working with a hidden sheetname "hide_template" which contains formula linking to another sheetname "rawdata" of same workbook.
In this "rawdata", the contents is updated from time to time.
The problem is when I open this workbook and update the entries in "rawdata", the formulas/results in "hide_template" is not updated and invalid (ie. #N/A, #VALUE..)
This is the overview of process that Im working with:
===> open workbook ===> paste new entries on "rawdata" ==> run a macro to copy range in the "hidden_template" to paste to new sheet of same workbook
I already have a working macro to copy range from the "hidden_template" which works as follows:
===> set to visible the "hidden_template" ===> copy range from "hidden_template" ===> paste to new sheet ===> set to hidden the "hidden_template"
I am working on a code in which i have to change the color of certain cells if the value of a cell is Y/N. The color should change with the user input.
Set wsheet = wbook.Worksheets("XYZ")
For Row_start=10 to Row_count 'Row count is the last used row in the worksheet
RowValue = ActiveCell.Row
colValue = ActiveCell.Column
if colValue = 17
[Code] ....