Using Data Tables When There Are MULTIPLE Things That Can Change
Dec 22, 2013
I have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
My NPV is the result of a series of calculations to ultimately arrive at a cash flow figure and then apply the WACC (average cost of capital)
I understand how to use data tables But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV sensitivity
My sheet and calculations are all set up
I've done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
View 1 Replies
ADVERTISEMENT
Jan 21, 2013
I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.
Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.
Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.
View 4 Replies
View Related
Aug 29, 2008
I am trying to compare items on two spreadsheet. I have an existing spreadsheet that lists items and then I import another spreadsheet from a database to confirm if new items have been added that were not on the original spreadsheet. I am about to go crazy and cannot figure out why I keep getting N/A (I know there are some repeats).
I have tried formatting, adding in isna, making sure there are no spaces, and paste special as text... still doesn't work. There are hypens in the data so I have removed them with substitute.
View 15 Replies
View Related
Feb 25, 2013
I just want to remove all the parts after /p1/ and /p2/ and make each cell contains only the top level data. How do I do that?
View 2 Replies
View Related
Jul 18, 2006
I have attached an excel spreadsheet
how to set a macro to extract the following datas from it.
Game Title
Description
URL
Picture can be set at 1 side to the right of the URL
View 3 Replies
View Related
Jul 1, 2013
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
View 2 Replies
View Related
Aug 2, 2013
I use MS Excel 2007 and I was wondering if it was possible to enter data for certain things once, and have that data copied automatically every time I type the text. For example, if I type Blue Car in a cell and type 4 in the next cell, is there a way I can program Excel to enter that data every time I type Blue Car?
View 14 Replies
View Related
Jul 31, 2006
I want to a macro or a function that can search for the value which corresponds to a cell and copy it to another cell. please refer to the attachment for more detail.
View 9 Replies
View Related
Dec 9, 2011
I am trying to plot multiple tables on the same line chart in Excel 2007. I have a list of X and Y values for Table 1, Table 2, Table 3 etc.
However the size of the tables are not the same for each table. For example, Table 1 might have 200 X and Y values, Table 2 might have 175 X and Y values, Table 3 might have 105 X and Y values, etc.
So when I plot these data tables on one line chart the individual plots are off center. They should all be centered at 0.
View 1 Replies
View Related
Jan 29, 2012
I have one sheet with 25 tables in it, And i want to extract the whole row from this sheet to another sheet based on values ( Office1, Office2, Office3, Office4 ) if appeared in thrid column "C".
- the 25 tables created on the 1st sheet to be in the other sheets as formatted as well.
- once "Office1" appeared in the original sheet in the 1st table, Action : copy the whole row to sheet named "Office1"at the top row in the first table.
- once "office1" appeared again in the original sheet in the 1st table, Action: copy the whole row to sheet named "Office1"at the 2nd top row in the first table.
- once "office1" appeared again in the original sheet in the 2nd table, Action: copy the whole row to sheet named "Office1"at the top row in the 2nd table.
- and so on, the same with other 3 remains sheet.
View 5 Replies
View Related
Dec 12, 2013
I need to insert a blank row between each row of data under my headers (not the immediate row) until I reach the end of the particular table. I then move down to the next header and repeat the process.
My starting data looks like this:
HEADER
DATAROW1
DATAROW2
DATAROW3
-emptyrow-
HEADER
DATAROW1
DATAROW2
DATAROW3
I need it to look like this:
HEADER
DATAROW1
-emptyrow-
DATAROW2
-emptyrow-
DATAROW3
-emptyrow-
[Code] .....
View 3 Replies
View Related
Sep 17, 2011
I'm working on a national survey with valuable data from students. The plan is to turn around, 100 different reports to the departments with their respective students.
The issue that I have is creating multiple tables from multiple sheets in excel into a word table, any way of automating it.
What the tables look like in excel:
What I want them to look like in word, with a chart if possible:
I realize that I can just copy and paste easy table into word from excel and with a few clicks of a button, get what I want. But i'm looking for something to automate and simplify this process. There are about 300-400 variables that I need to crosstab with certain demographics.
If it matters any, I bought a software package (Q Market Research) but it doesn't make the tables look exactly how I want them to look. The data is from SPSS, those tables are flat out.
View 9 Replies
View Related
May 2, 2013
I have multiple pivot tables with different data source. I wish to have one slicer which control all the pivot tables. I would have one common colum for all the pivot tables which is the one i wish to control for instance the person in charge. Note that all my pivot tables are from different data source. how to do it?
View 1 Replies
View Related
Dec 16, 2013
The spreadsheet is comprised of 4 sales data sheets, one for each store. Each of these sales data sheets runs several pivot tables (One for dept sales, one for waste, one for sales on Selected UPCs etc) and I used to have 1 copy of each pivot table for each store (ie 24 pivot tables). Now I have found a way to have only 1 copy of each PivotTable which changes based on which store is selected using the following code:
Code:
Sheet13.PivotTables ("Top10LW").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
.List(.Value)
This works great however I was repeating it for every pivot table I had in the spreadsheet, causing messy code and file corruptions.
Is there any code which I can use for updating all pivot tables, using as minimal code as possible, all in one hit?
View 3 Replies
View Related
Sep 20, 2009
I am trying to combine sorted data from 2 worksheets to a new work sheet to process further. I have one worksheet with order number, part number, order qty but with different delivery dates. On another worksheet, I have the order number too, part number, the qty delivered. The qty delivered is not always the same each delivery.
My aim is to find out how many are already delivered under a certain purchase order and the balance of undelivered parts.
I used pivot tables to sort out the data but I am stuck here not knowing how to extra the sorted data from the pivot tables to the 3rd worksheet. I will need to match the order number and the part number.
View 9 Replies
View Related
Mar 9, 2014
I am trying to generate several pivot tables from one data source table. I have successfully created my first pivot table (A date field, and a water storage facility level reading) and subsequently a graph from this. I have worked out that I need to group my dates as I am supplied a daily reading, but only need monthly average. All worked great.
Now i need to create more pivot tables and graphs. The next one I want is to create one grouped by years. But when I create this new pivot table and change the grouping of the date field to yearly, it also changes the grouping on my first pivot table, which is undoing my work.How to tell excel that these pivots are independent, and I don't want them changing in unison? See screen grab of my source data and where I am up to...
Microsoft Excel - 401027_0100.00_0221.00.csv_2014-03-10_11-41-35.jpg
View 1 Replies
View Related
Sep 13, 2013
I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010
Here is a copy of what I have
Code:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsUSERNAMEDesktopSCHEDULE STUF" _
, _
"FAcrewCurrent.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
, _
";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;J" _
, _
"et OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt " _
[code].....
Is there a better or simpler way to do this? The tables don't need to be forever linked to their previous spreadsheets so if copy/pasting is a better way than I am all for that as well.
View 1 Replies
View Related
Jun 16, 2014
I've already entered a variation of the formula below into 180 different columns. The only variation is where worksheet 322 is referenced. Each column references a different worksheet.
Formula:
I have at least four other tables to build of the same size, and they're each going to use this same formula with an additional IF formula housed around it. Ideally I would be able to copy the table, then run find/replace, where I could substitute = with =IF(new formula, and then run find/replace a second time and sub ))) with ))),more new formula). The problem of course is in between those steps lies a formula error preventing me from running the second step. The only alternative I can think of is to build the new formula, copy it into a word document, and run find/replace 180 times to tailor the formula for each column. That's what I did to build the first table.
View 8 Replies
View Related
Feb 2, 2010
i've attached a pivot table where some cells are empty (see GCP tab). i want the empty cells to appear empty, and not as they currently appear, with the word "blank" in parentheses. i am aware that if i enter 2 spaces in any cell and press return, all empty cells in that column will also appear empty. is there any other way to have empty cells in a pivot table appear empty?
View 4 Replies
View Related
Aug 10, 2014
I am trying to monitor my turnover and taxes but struggling slightly to get things to work. I have attached a spreadsheet that has comments inserted to show what it should be doing, I just can't get that to happen at the moment.
OzGrid help.xlsx
View 5 Replies
View Related
Nov 3, 2008
I have a few excel calendars.
I was wondering can I take a calendar and then put hyperlinks or vba to make a sheet if clicked on.
So If I click on an icon on a date, it will copy a certain template sheet, then link it to that date and that icon.
See an excel calendar, I have a few others to play with aw well.
But this one gets updated on the same the page and not sure how the links/icon would follow the dates. I was hoping for maybe 3-4 different icons on a date.
View 7 Replies
View Related
Apr 20, 2007
Here's a sample data set. How can i perform a vlookup to populate my forecast columns and have it match BOTH the customer column and the SPFKey columns?
******** ******************** ************************************************************************>Microsoft Excel - Receiving Qtr1 2007.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL20=
ABCDEFGHIJKL1Business*UnitCustomerSPF_KeyFORECASTACTUALS*2"NBPRO"Sold-to*PartySPF_KeyJanFebMarTtlJanFebMarTtlVariance3NBPROAAR*CORP.883***01*1224NBPROAAR*CORP.933***020**20205NBPROAAR*CORP.934***09**996NBPROAAR*CORP.999***02*1337NBPROAEROTHRUST*CORPORATION6600***01**118NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD918***0*11229NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD919***0*112210NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD933***042422210610611NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD934***042424112512512NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD1742***0*1*1113NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD1793***0*0*0014NBPROAIR*CHINA*IMPORT*&*EXPORT*CO.*LTD1795***01**1115NBPROAIR*INDIA933***036408215815816NBPROAIR*INDIA934***046176512812817NBPROAIR*INDIA1742***0**00018NBPROAIR*INDIA6601***0*1*1119NBPROAIR*INDIA6602***0*1*1120NBPROAIR*INDIA6603***0*0*00Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Apr 13, 2014
i have a macro which takes the results generated and puts them into a table and filters the data so that zeros does not show and arranges the table from largest to smallest. Now i have a problem where i need to select the power, fuel oil consumption, weight and area from one table and the other power, fuel oil consumption, weight and area from the other table and combine them together so that i can have all possible combinations of the data, but i don't want the zeros to show in the combinations. i need the combined data to show on a separate sheet. The data of table 1 varies with rows as shown:
The Data of Table 2 varies with rows as shown:
I would like to achieve a result like this in the following columns and rows without having to input them manually:
View 1 Replies
View Related
May 12, 2009
Sub Moveit2()
Dim Quantity As Range
Dim Cell As Range
Dim Cell2
Dim Breaks As Range
Set Quantity = Range("a2", "a21")
Set Breaks = Range("g1", "s1")
For Each Cell In Quantity
For Each Cell2 In Breaks
If Cell2 = Cell Then
Intersect(Cell2.EntireColumn, Cell.EntireRow) = Cell.Offset(0, 1)
End If
Next Cell2
Next Cell
End Sub
I have this macro to move things to the right. Sort of like a vlookup in a way. However, it runs using one range @ a time. For example it will look up the quantity in A2 versus what's in Row 1 and if they match, data from B2 would be moved into correct column. However, I'd like to run for multiple columns at the offset so I can don't have to keep changing data in the macro. Like want to run for these ranges at the same time as running the first range.
Set Quantity2 = Range("c2", "c21")..........
View 9 Replies
View Related
Aug 20, 2009
I am trying to develop a point system to rank things. For eg: in the below chart, I have the follow criteria.
In a particular Cell A1, i enter a numerical value like 1.5. Then Cell A2 (Points) should show that the Points earned is 80.
If MTTR is 1.00, then the Points received by the candidate is 100. Similarly if 3.5 or higher is there, then the points should be 0
MTTRPoints
1.00100
1.2590
1.5080
1.7570
2.0060
2.2550
2.5040
2.7530
3.0020
3.2510
3.500
This is only one type of calculation. For other tables, i will have to change the criteria.
Is there any way i can set the limits for A2 as 0 to 100 and based on what appears in A1, the cell A2 should change automatically
View 9 Replies
View Related
Mar 12, 2013
So I have this assignments. Its about rental services. I need to create a formula where the price is automatically found from a table depending on what drop down menus are selected. One menu is the rental name and the other is the season i.e. high or low, therefore each rental has two prices.
I know i can use Vlookup if there was one drop down menu but how to do the other. I was think it will interms of if statement but i dont know...
Here is the link to the worksheet: [URL] ...........
View 5 Replies
View Related
Dec 23, 2008
I have a multiple rows of data, but one column which is K has data like:
1020
1020
1020
1020
1020
1030
1030
1030
1030
1050
1050
1050
What i want my macro to do, is whenever that number in column K changes to copy all the data in row A thru K to a new sheet.
View 9 Replies
View Related
Oct 15, 2013
Say I have 10 tables and want to copy them into one single table. Easy right? Just copy / paste.
The restraint with copy paste is, if I make an adjustment to one of the 10 tables, it will not be reflected in the master table. I would have to recopy the table every time I make a change.
I was wondering if this there was such a function that says: "Once done copying with X table, start copying Y table, and so forth."
View 3 Replies
View Related
Nov 27, 2012
I have a workbook that contains mutiple tables in multiple sheets that are all in the same columns in each sheet but contain various rows. Data may not be entered in every single row within each table. I'm trying to create a master list of all data from all of these tables on a single sheet so I can analyze the information in total. I don't want to show any blank rows on the master file where data is not entered in each table. I've tried using the consolidate function within Excel but am not looking to perform a function (i.e Sum, count). I've also tried a pivot table from multiple consolidation ranges (Alt + D + P) but do not like the limited functionality of the pivot table. I do not have much experience with VBA but am assuming this may need to be done using code.
Attached is a sample workbook with named ranges of each table. Example.xlsx
View 9 Replies
View Related
Jul 15, 2014
with this macro?
I have a lot of pivot tables on multiple sheets in one workbook.
I want to deselect all "(blank)" from rows, and sort both rows and columns in ascending order.
I recorded a macro but it only refers to specific pivot tables, I would like it to do the same sorting for all pivot tables in the workbook.
Sub PivotBlankSort()
'
' PivotBlankSort Macro
'
'
Windows("Alt Assessment Y10 13-14.xlsx").Activate
Sheets("L&C Prog Sum2").Select
With ActiveSheet.PivotTables("PivotTable10").PivotFields("English")
.PivotItems("(blank)").Visible = False
[Code]...
etc as I move to each separate pivot table.
View 2 Replies
View Related
