Any way in which i can 'paste link' and then 'paste link' again without overwriting the first link. example i have a row (say A1 to H1) with varying values in Book1 Sheet1. i know how to copy and paste a link to that onto, lets say Book2 Sheet1. but now in Book3 Sheet1 i want to copy and paste a link from A1 to H1 over to Book2, but have both links open for auto updates.
View 3 RepliesI 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 want to stack columns in blocks. Have not been able to work out a VBA code for the same. Let me explain the need:
- There are N columns of data, for e.g., Col. A, B, C and D has Data (total of 10 rows) ... 4X10 layout
- I want to stack columns in blocks as follows:
AB
AC
AD
... 2X30 layout
I am looking for a way to that I can stack certain columns in an excel chart. For example the first column will be a total and the three next to it will be the breakdown of the first total. I want the second, third and fourth columns to stack on top of each other so that they will equal the height of the first. Is this possible? Also, I'm looking for the simplest solution because others will have to use this.
View 3 Replies View RelatedIm' working on an ROI spreadsheet at work and looking to add multiple IF statements leveraging one cell. an example is this:
If E51 is > 1 and < 100001 then the cell should equal "PREMIUM"
If E51 is > 100000 and < 175001 then the cell should equal "PREMIUM PLUS"
If E51 is > 175000 and < 250001 then the cell should equal "ENTERPRISE"
I have a formula already prepared in the spreadsheet that will come up with a value anywhere between 1 and 250,000 in cell E51. I need that cell value to be PREMIUM, PREMIUM PLUS or ENTERPRISE; depending on the value and can't work out the correct formula.
I have a question about putting data in the right order. I have this order in my excel files:
City A, City B, City C
10 20 20
20 40 50
15 30 40
45 20 10
50 10 10
20 30 40
I need to put them in order from top to bottom like this:
10
20
15
45
50
20
20
40
30
20
10
30
20
50
40
10
10
40
Is there any way to do this in a fast way. Because I have 440 cities and 13 numbers per city copying and pasting will take a long time..
I have a spreadsheet with a bunch of formulas in Cells B4:E100 in Sheet1. Many of the formulas will show up blank information in the event I do not have any data in Column A. I then have formulas which perform a similar function in Cells G4:Q100, S4:AC100, AE4:AO100 & AQ4:BA100.
What I need to do is to take the data from the last 4 sets of cells (G4:Q100, S4:AC100, AE4:AO100 & AQ4:BA100) and stack then one on top of the other into Sheet2 starting in Cell A2. I need the code however to be smart enough to realize that if there are blanks which exist in any of those sets of cells to skip over to the next set of data I which to copy and start pasting that set of information without leaving any blanks.
So far example, if I only have data between Cells A4:A25, I want the VBA code to go only through cells G4:Q25 and then start pasting without any spaces S4:AC25 and so on.
Is there an easy way to change
='Week 1'!$D$21
='Week 1'!$D$30
='Week 1'!$D$39
='Week 2'!$D$21
='Week 2'!$D$30
='Week 2'!$D$39
to
=IF(ISERROR('Week 1'!$D$21), "", ('Week 1'!$D$21))
=IF(ISERROR('Week 1'!$D$30), "", ('Week 1'!$D$30))
=IF(ISERROR('Week 1'!$D$39), "", ('Week 1'!$D$39))
=IF(ISERROR('Week 2'!$D$21), "", ('Week 2'!$D$21))
=IF(ISERROR('Week 2'!$D$30), "", ('Week 2'!$D$30))
=IF(ISERROR('Week 2'!$D$39), "", ('Week 2'!$D$39))
Without having to change each one by hand?
There is a pattern to the numbers.
There are 5 weeks, rows 12, 21, 30, 39, 48 in columns D,I,N,S,X
Been trying to work this one out and have ended up banging my head against the wall.
Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability.
The team name is a selection from a list, the availability is 1-6 (different levels of availability)
I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up:
Name Team 1st 2nd 3rd 4th 5th 6th 7th etc.
Mr X ___1st___1______2__1
Mr Y ___2nd_______2________5__5__5
Mr Z ___1st___1____________3__3__3
Total 1st: 2 2 1 3 3 3
Total 2nd: 2 5 5 5
So basically the formula in the last row goes along the lines of:
Add each cell in the column IF the team name = 1st
I've got a file that is currently linked to two other files. The links are summed in the cell as below:
='[Book1.xls]Funding and Interest'!D$17+'[Book2.xls]Funding and Interest'!D$17...and so on.
I want to get rid of the link to Book1, so I'd zeroed the values in Book1 and my plan was then to Break the links to this, leaving zeros and the links that I want, but because both files are linked in the same cell (the same nested SUM), when I go to Edit, Break Links, it breaks the links to both files, even though I only select one form the list.
I have a master spreadsheet that links to various other spreadsheet for budget figures. I have now created and updated the budgets for the 2008/09 budget year.
It is painstaking to update the links and browse to the new files one at a time and point them to their new folder. Is there a way I can update all of the link workbooks to the new directory in one fell swoop?
My first post at last.
I've recorded a Get External Data macro and wish to modify source and destination components. I would like to replace the absolute link to the file name with a variable and the Destination range to the active cell at the time I run the macro.
This is the is the macro code line that handles the source and destination components I want to modify.
Range("AQ2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:filename.csv", Destination:=Range("AQ2"))
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
I have an unwanted link and I can't remove it. I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.
View 9 Replies View RelatedI'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:
A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
I have a couple spin buttons in my worksheet.
What I want each one to do is to scroll through cell links.
For example, cell I4 has a lookup reference of "=LOOKUP(2^20,Sheet3!A:A)". What that does is get data from the very last line on sheet3 column A.
What I want the spin button to do is scroll through the lines in column A.
So if a user decided he/she did not want that data from column A, then all they would have to do is press up or down on the spin button, scrolling through other data that is in column A.
I am linking to cells on a spreadsheet and in some cases the formula is showing in the cell and in others the contents of the linked cell. I have forced updating (F9 )and F2 enter with no effect. Is their something i am missing as I want all the links to update automatically. If I copy and paste a working formula into one of these cells it updates albeit with the wrong reference but if i go in and edit F2 etc then it shows as a formula.
View 4 Replies View RelatedThe goal is to have a data validation drop down list that adjusts the File.xls name portion of the external cell reference (example given below) and return the result of the cell I am searching in the new file after updating my links.
I am trying to set up an external cell refence within my workbook for example:
='[File.xls]Tab'!$C$9
When I have tried setting the equation to pull from the drop down list in cel A1 for example:
="'["&A1&"]Tab'!$C$9"
It only shows the new file path name and not the value in the new sheet--even after updating links.
I am also attempting to do this using the my network places file path instead of the letter drive in my computer.
Windows 8.1, Excel 2010. I have this code that updates the links. I use it in various workbooks, but they are all going to the same document; "Data Master (QC"
ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources
When I change computers, I have to manually tell each document where to fine the linked document. I want the documents being linked to look for the file path in a designated cell, i.e Cell E3 = "C:UsersOwnerDocuments1-QCDataData Master (QC).xlsm".
Something like ThisWorkbook.UpdateLink Name:=ThisWorkbook.Range("E3").Value
The master has rows corresponding to numbered files:
4300 | CellValue1 | CellValue2 | etc
4301 | CellValue1 | CellValue2 | etc
In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following:
='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1
This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself.
Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.
How can i break the links in a row of cells but still retian the cell comments thereafter.
At the moment, the comments dissapear after editing the links in excel 2007 that i am using.
Changing the display (foe cells with comments, show) under file-options-advanced has not really worked.
WednesdayThursdayJob1Job2Job3Job4Job5
would like to be able to put the total amount in dollars on each job number for each day if that is possible. I cant think of an easy way of doing this just multiple sheets.
ex: on wednesday job 1 would be $167; job 2, $0; job 3, $104;etc..
NameJob #WednesdayJob #ThursdaySS#Rate/Hr23ABC123$11.001718ABCD1234$15.001646ABCDE123245$13.003838ABCDEF123456$16.004927
Here is what I have In cell H4 I put the following code =B5:G5
But when I add number in thous cells nothing happens. I want cell H4 to keep a running total of cells B5 through G5 so as I add numbers to the cells I would like the total in H4 to change.
I have several cells that I use the "DATEDIF" function to added dates and display as, "1 M 2 D". I need to add the multiple results to get an sum of all of them.
Column C1 is the start date.
Column C2 is the finish date.
Column C3 is a separate start date.
Column C4 is a separate finish date.
I used the following formula to get the month/day count for each separate start/finish dates: =DATEDIF(C1,C2,"M")&"M"&DATEDIF(C1,C2,"MD")&"D". Both give me the result I need. I have blown a gasket trying to add all the start/finish into a single Month/Day number. Sample result should look like this:......
I'm having issues with a formula that I am using to add three vlookups together. The problem is that there may or may not be data in one of the columns I am looking up with the vlookup. I am looking up hold times for reps each week then adding them together to get a monthly sum. This formula works when data is available for all 4 weeks. When I have a rep that started in the middle of the month there isn't data for all 4 weeks so I want Excel to just treat that as a 0 or Null, Currently it just provides a 0.0 for the whole thing instead of adding what is there.
=(IFERROR(VLOOKUP([@Name],Sheet2!A:H,2,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,4,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,6,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,8,0),0))
I am performing a sumif to look to see if a number starts with #, then another to see if the the class is text, both are fine and both return values of 1 if it meets the criteria. My problem is i then need to add a sumif (or something im not quite sure wot) to add the cost if the number begins with a # and the class is text (so baically if both the other sumifs = 1).
View 9 Replies View RelatedI have a quotation that I am working on that I need to add 12.5% onto each seperate line price.
There are 300 individual lines on the quote all with different pricing but they all need 12.5% adding on so I am hoping there is a simple way to do a bulk sum for all of the rows rather than having to go through each row and creating a sum for each. - ie =(K28*1.125)
=IF(A5:A6="John","Present", "Off")
How to add few more names aside from John. What if I wish to add names like William or Leo on the search criteria?
For example, John or William or Leo
I'd like to add the numbers in column C that correspond with Fuji, Fuji Royal, or Fuji Premium in Column B. Essentially I am trying to count up the total number of all variety of Fuji apples. I've tried using the sumif and sumifs formula, but haven't been able to crack it.
RED APPLESSIN ESPECIFICAR2.352
RED APPLESROYAL GALA1.029
RED APPLESCRIPPS PINK280
RED APPLESROYAL GALA896
RED APPLESCRIPPS PINK1.064
RED APPLESFUJI867
RED APPLESFUJI PREMIUM1.919
RED APPLESROYAL GALA4.871
RED APPLESCRIPPS PINK1.176
RED APPLESFUJI112
RED APPLESROYAL GALA1.064
RED APPLESSIN ESPECIFICAR2.282
RED APPLESSIN ESPECIFICAR4.522
RED APPLESCRIPPS PINK1.176
RED APPLESROYAL GALA2.352
RED APPLESCRIPPS PINK7.056
RED APPLESFUJI1.96
RED APPLESPINK LADY1.344
I have a spreadsheet with 4 columns.
A, B, C, D
I want to multiply A*B and then add that to C*D
=SUM(A*B)+(C*D)=TOTAL
However, when I dont have a value/number in A or C I get a #VALUE in the total cell. It will only calculate properly when I have numbers in all 4 cells.
Is there a way to set it up so it will calculate if cells A or C are empty? I dont want to have a zero in these cells - just leave empty if no value.
Also - when the total cell is empty I want it blank.