I have a question about linking between Workbooks. I have 2 Workbooks and some cells have Formulas targeting each other, formulas like IF, VLOOKUP... Etc...
I have it that way because the users cannot access other user whole information, even though there is no need of real time sync there is a need to share some of the information between the users.
So.. some cells with the formula appear as #REF, I
If you are working in an excel spreadsheet that is linked to other excel files, when you double click on the cell that is linked to another workbook, it immediately opens the linked workbook if your settings are correct. How do you change your settings to enable this functionality?
View 2 Replies View RelatedI have a workbook which contains countifs and sumifs reading from about 10 different workbooks.
WHen I have the 10 files open, the values appear but when I close the 10 files, I get the 'VALUE' error. How I can keep the value amounts when the workbooks are closed?
I have all documents in the same folder?
How can I use VBA to set up multiple ranges & or Columns in Workbook 1 (Sheet 1) Col A:C and have them linked to Sheet 1 Col D:F in another workbook 2 EG. I want to run a Macro that writes data in Workbook 1 and have it show up in the other workbook. regular Copy/Paste is not an option. The macro writes to varying columns weekly in Workbook 1. Can I also link irregular named ranges across workbooks.
View 2 Replies View RelatedI seem to be having problems with using relative RC in vba within an automated reporting spreadsheet..
in my 'left lookup' below.. the R seems to point to row 1 no matter what row i am writing to..
The file that I am currently working on is getting really big. It is currently 101MB and we aren't done yet. In contains a lot of Macros and also a lot of ordinary excel formulas. I have recently noticed that excel sort of gets unstable with this file. For instance if I select a very large area eg A200:IK19000 and delete it, it only deletes the first entry (A200). What is going on? Is this a typical excel thing? What can I do to fix it or avoid it in the future?
View 2 Replies View RelatedI need to link to a different workbook but using cell values.
For example, in the attached file in cell B4 it looks like this:
='C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls]Week 9'!$D$20
I need a way to change Week 9 in the above formula using a validation list.
I would also like to know how to specify the above workbook referenced using a cell as well, for instance, C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls] would be the contents of a cell or the ability to click on a cell and have it bring up an open dialog box to browse to the file and input it's path into that cell.
I am trying to put together a small rota/hours manager. I would like each employee to have their own workbook file and update it everyday. Then I would like to have a master workbook which reads each workbook and returns the data inside the workbooks when opened or on the press of a button. I have looked at paste special and other linking ways, but couldnt quite get them to work. Is it possible for the master workbook to keep the data retrieved in date order and then perform a calculation for each row/employee to calculate that days wages. (rate of pay stored in Pay_Rate sheet.) Finally, if there is an easy solution would it matter if the employee spreasheets were password protected?
View 4 Replies View RelatedThere are two teams in my department, and each is assigned to maintain their respective work book and I'm looking to link them in order to save some time.
Team A - Responsible for receiving Invoices (Bills) and entering them in an excel spreadsheet when received and update when bill is paid. Only one tab in this workbook.
Row A - Name of company billing us
Row B - Invoice #
Row C - Invoice Amount
Row D - Once Bill is paid the check amount is entered here
Row E - Balance Due (Row C - Row D = Row E)
Team B - Is Responsible for maintaining a list of all checks issued. All of the checks issued to pay the bills received by Team A are entered here plus other checks to pay a variety of different stuff. On this workbook a new tab is created every month. One tab per month. Since we need to follow accounting rules and record the check NOT on the month it was paid, but on the month the service was provided. for example I might be paying a bill in the month of November for services that were provided in September, so I would need to enter this check in the September Tab.
Row A - Name of company check is paid to
Row B - Invoice #
Row C - Amount Requested to be paid
Row D - Reason for payment
Row E - Date of check issued
Row F - Amount paid
Row G - Check #
Here is what I want to do.
I want to link both of these workbooks so that when Team B fills out the information of the check issued this will automatically update the Workbook of Team A so that
the balance is zeroed out.
He is my challenge. Workbook of Team B has multiple tabs so I can't just do a simple Vlookup and also every month a new tab is created (very dynamic workbook).
TO add to this in Team B's worksheets have to be in alphabetical order, which means that rows are inserted everyday. for example if I paid yesterday to A and C, I enter company A in Row1 and Company C in row 2 but today I received invoice from Company B so in order for them to be alphabetically I would need to insert a row between Row1 and Row2. So if I had links to this workbook they wold not update when the new row is added.
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
I have 25 files with certain worksheets that I need to move to 25 other files.
Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....
Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that
1. Opens Workbook A copies the worksheets
2. Open Workbook A-2014 paste the sheets
3. Save and close Workbook A-2014
4. Close workbook A without saving
then doing the same for Workbook B, etc.
Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.
View 3 Replies View RelatedI have created a DDE link which is connected to Excel and refreshes on every change but the problem is it's only 1 row in height, so as new data comes in I lose the previous. Instead I want the old data to move down a row and I want this to continue as new data comes in.
View 9 Replies View RelatedI have a couple of workbooks, workbook1 pulls information from closed workbook2. The current link works fine, it returns the value that is in cell E10 from the worksheet 'Totals' from within the workbook 'Week 12 Yellow.xlsm'.
[Code]....
What I would like to do is make two parts of this link variable; Name (so 'Marc owens' in the above example) Worksheet (so 'Week 12 Yellow.xlsm in the above example)
As stated, the worksheet in the formula will be closed so I cannot use the 'Indirect' option. I have come across a lot of talk about the 'Concatenate' option but this this isn't working, don't know if its the way I have the formula or if I need something extra adding.
Cell A1 = name variable & Cell A2 = worksheet variable
[Code] ......
But this just returns "G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals!%E%10" in the cell the formula is in.
I've changed it so that it is
[Code] ......
But again I get "='G:HelpdeskTotalsMarc Owens[Week 12 Yellow.xlsm]Totals'!%E%10" returned which is the same formula as originally used (top of page) but it doesn't return a value.
is it possible to create a link from Excel to MS Visio, to a specific Visio worksheet?
View 14 Replies View RelatedI have created a data base of all the spare parts we have at work.
In the last column I have entered a hyperlinks that take you to a layout of the store and shows you where the part is located i.e. 1C would take you to shelf 1 level C on the drawing in another tab in the same Excel workbook.
I would be pleased if anyone could help me by suggesting away that I can make the destination cell highlighted or more prominent once you have clicked on the link.
Also can I make the Find window automatically appear when the workbook is first opened?
I would like to have a link in a merged set of cells that puts the insertion point to another cell. Scenario: In cell k30, the user puts in the amount of credit card debt a individual has, and scripts the user based on that information. Later on, they get a more accurate running total of the amount of debt and need to change the amount in k30 for an accurate quote. After ther running total, in cell j172(a merged box for instructions) i instruct the user to be sure to enter the new total in cell K30.
Questions:
1. Is there a way to put a button or a link that would put the insertion point back up to K30 automatically, but allow the user to change it themselves and not change it automatically?
My workbook contains a link to a source it cannot find. I cannot find any reference to this source in my workbook.
I added a new blank worksheet
Deleted every other sheet
Deleted every module
Deleted every line of code
what remains is a workbook with 1 blank worksheet and a Link it cannot find the sourse file and I cannot delete this link. How can I eliminate this non-existent link?
I would like to ask if there's a way that i can create a link in a cell to another cell. Sample: in cell A1: there's a word "click to go to cell A150" in this case i dont have to use scroll down to go to cell A150.
View 2 Replies View RelatedI have 4 workbooks that I want to link together. The first three are exactly the same as each other in the setup, cell size ect, ect. The forth book or "book4" is the book that I want link to the other three books. I am planing on using a countif formula to add up certain symbols in designated cell ranges. Eg, Make a formula in Book 4, cell B4 to count all the black triangle symbols "" in the cell range B6 to B35 in Book1, Book2 and Book3 on each work sheet (There is 31 work sheets which represents a work sheet for each day of the month). If I can just get the formula for doing this I can aply it to everything else.
One last question, these 4 books that will be linked togther are used for each month. So every month I save them under a that months name, but if I have the books linked I will always have to keep updateding the formulas in book4 b/c it will still have the formulas for the previous month. Is there a better way to setup what I want to do or will I just have to keep changing the formulas each month as I update?
Here it is
1. I want to put the AMOUNT on E6
2. from there on F6 put the account number on there (FOR)
3. Now once the account number is put on F6 it would link up the same account number on (H6-H32)
4. then when its linked up from those cells put the amount where you typed it up from to move to that account to THIS MONTH (J6-J32)
so from E6 to F6 it would link up, put an amount of 100.00 on E6 and an account number on F6.
Once account number is typed up it would link up to the account number to the other side to H6 -H32
Finally the amount that you typed up from E6 would link up to the account number and pop up to J6 - J32 (THIS MONTH) and if you put on other spaces on the cells it would add up different amounts and put account number and it would link up and the amount would move to THIS MONTH.
I attached one excel workbook...in that workbook main sheet & data sheet is there. I need formula in main sheet...while select drop down list in column of name & month data should come other columns. I tried vlookup ...but error came. I need drop down name & month only...other data should come automatically...
View 1 Replies View RelatedI wish to have a floating link in large spreadsheets that will always be visible and when clicked will return to a particular cell in that same spreadsheet.
View 2 Replies View RelatedI have a macro that works across 2 workbooks. In the middle of the macro, a message box pops up "This workbook contains links to other data sources" and I need to select yes or no. This is because I am using a vlookup. Is there a code that I can put in to the macro to select Yes to this box and therefore not stop the macro half way through??
View 5 Replies View RelatedI'm wondring how secure a dde link is? Can the source from where the data is coming from see what is within the spreadsheet that the ddel link is within?
View 2 Replies View RelatedI have a cell that a I want to get the results of a SUM to 20 cells in 20 worksheet out of 60 worksheets. When I enter my' =sum( shows up with serial_numbers. When I go to each sheet and select the cell I want (same in all worksheets) like in sheet 2 it shows my sheet name and cell. But when I go to my sheet 3 and select that cell it replaces sheet 2 with sheet 3 it does not keep adding the sheets with the cell numbers. When I select all sheet tabs it still only changes the sheet number in stead of running a range. I could type all the sheets names and cell but lots of work and I have a lot of this type of ranges to do.
View 2 Replies View RelatedI have workbook with worksheet 1 and worksheet 2. I need to creat a link on Worksheet 1 so that when you click it it will take you to worksheet 2
View 1 Replies View RelatedI just started to do excel, and our church needs to tally tithes and offerings.
I just would like to ask if i can be able to link the envelope number to the Names (as shown on the attached picture).
Instead of inserting the name one by one, I can easily know who had given the envelope by their number.
I have a hyperlink in my excel file which refers to cell in a separate excel file (which no longer exists). I therefore would like to remove this 'dead' link as it keeps trying to update it when I open the file.
I don't remember where this hyperlink is in this huge multi-sheet file so I can remove it! How do I have excel show me all hyperlinks and the option to remove them?
I have data taken from the web which includes hyperlinks. Is there a VBA technique to extract the link itself and place it in a seperate cell? IE end up with the original text that was clickable in column A and the underlying URL in column B?
View 9 Replies View Related