I've a set of excel sheets reading from a single excel data source, the only problem is that the data source is constantly changing, for example:
=DCOUNT('T:GreenbeltProductivity ToolWeek 42[Dept Overall and Data Entry.xls]Insert Data - Current Position'!A:F,5,AC139:AD140)
The week number is the only bit of the code that needs to change week on week, to update the data source on all the sheets and I'd like to password protect the data source.
I am trying to monitor the status of a cell on another sheet and autopopulate a cell depending on that information. The formula works well until I give the spreadsheet to a 3rd party and the formula ranges change after they paste new data to the source sheet. I have tried locking and password the formula cells but they change range regardless!
Here is the formula from the first cell.
[Code] ......
It scans for a number in an adjacent cell. If the number is present on the sheet 'Test', it autopopulates the cell with a string from the source sheet. The cell remains blank until there is data present.
If I cut data from row 6 and paste it to row 17, the formula cahnges itself to:
[Code] ........
How I can lock down this formula so that the ranges remain the same i.e. $AT$6:$AT$26, despite changes on the source sheet? I have tried F4 to toggle relative and absolute references and this has made no difference.
I am trying to create a macro which will allow me to change the source data for a particular chart. what i want to do is to have a code which looks in a range of cells (A8:B28) and then updates the chart to only include those cells within the range that contain a value? The number of cells with values changes depending on what options are selected elsewhere in the spreadsheet.
View 2 Replies View RelatedI have a workbook with some pivot tables connected to a single slicer. My macro needs to disconnect the pivots from the slicer, change the data source, and reconnect the slicer. When my code gets to reconnecting the slicer it bugs out as if the data source is not the same for all the pivot tables (also doesn't let me reconnect manually). But when I look at the source, it is all the same and correct. If I manually set the data source the slicer reconnects just fine. Why is my code causing problems with the slicer?
Here is my code for setting the data source. Pretty sure this is the code that is causing problems for me.
Code:
newaddress = "$B:$" & Mid(Cells(1, YTDcolumn).Address(), 2, 2)
.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="TOP250Excel!" & newaddress, _
Version:=xlPivotTableVersion14)
Here is my code for reconnecting the slicer connections.
Code:
ActiveWorkbook.SlicerCaches("Slicer_Top250Excel_cypheren_Query_Name"). _
PivotTables.AddPivotTable (.PivotTables("PivotTable1"))
I'm trying to change the source data of an existing PivotTable so that it is linked to a raw data source in another workbook. The workbook it pulls the data from changes every month so I made this flexible code that is meant to pull the raw data every month:
Sub ImportNewSource()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FilePath As Variant
Dim ThisPivot As PivotTable
Dim FileName As String
Dim ShtNum As Integer
Dim LastRow As Long
The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.
When I do it manually, it works just fine. There aren't any changes to the PivotTable field or anything like that so I'm stumped. I've tested the range that has the source data and its selecting the proper range and when I do it manually it works fine.
Each week I have to add a new column to a report and then update the source data of all the charts that use that newly added column. I am trying to create a macro that will use the last 12 columns of data located on a different worksheet as the range for the source data of the chart. I have attached an excel document that contains an example of how one of the charts that I am using looks and where it pulls the data from.
View 3 Replies View RelatedI currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ...
Y-Axis is the data that I am trying to present - Total $
The source of this data is simply two columns - Column A is the running date, Column B will be data.
Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily).
Can a macro be used to auto update the bar charts based on today's date?
I have the following code to update a pivot table:
Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies View RelatedI have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
Thanks in advance for any help,
Jon
I'm trying to create a chart with different source ranges e.g if i used
With ch.Chart
.SeriesCollection.Add _
Source:=Worksheets(1).Source:=Worksheets(1).Range("C1:C8,J1:J8")
End With
That works fine but I want to modify the columns it looks at. I was going to use the cells(x,y) method but I can't get it to work. icol = 3. Range(Cells(1, iCol), Cells(8, iCol)). This works for a single range(C1:C8), but how do i reference Range("C1:C8,J1:J8") in the same way.
I have 2 sheets, lets say SheetA1 and SheetA2. I am using Vlookup to serial number & getting data from SheetA1 to SheetA2. But I want to modifty the data. If i modify the data in SheetA2 that modification should relect in SheetA1. Is it possible...
View 5 Replies View RelatedI am trying to copy a formula in B38, down to B36
My formula needs to refer to data in of cells in a horizontal succession, G1, H1,, I1...etc
Copying my formula down from B38 causes the formula to refer to G1, then G2, G3... instead of G1, H1, I1 ...
The exact formula in B38 is: =$B$3*(G3*(1-$D$13))
Where G3 should advance to H3, when I drag to copy the formula from B38 to B39. Instead, G3 advances to G4.
I have a sheet ( Graphs A) with 50+ graphs on all linked to a datasheet (Data A).
I now want to copy these graphs to another sheet (Graphs B) which links to a different data sheet (Data B). The data is in the same layout format etc.
The problem is that the find-replace function will not replace the sheet name in the charts series formula from Data A to Data B
Is there an easy way to change the source sheet for the new graphs?
I have a formula in cell A1 that I need to drag downwards so that it fills cells A2 to A10. That formula is picking up the figures located in another sheet but the figures on that sheet are arranged horizontally (A1 to J1). Is there a formula to perform this action? I've tried playing around with the $ sign in my formulas but it does not work.
View 2 Replies View RelatedI have data. 2 columns a2:b5
each row has a different font color
apple 4
banana 3
peach 2
pear 1
I want the label in a chart to march the font color of the source cell
So in a chart I'd have 4 labels:
"apple"
"banana"
"peach"
"pear"
I can't see how to do it. When i select the albels it's "all or none". I can change ll the labels to a color. But, I want each individual label to be a diferent color.
I have been working on a workbook that uses as a source information on Sheet 1.
Sheet 1
Each item reports a number of different details from column A to column BY.
Each row is for a different item.
25 rows of information.
Sheet 2
This is my master formula sheet, the one I've been using to create the page that reports and formulates information on a single item. The source for this sheet is taken from the cells in Sheet1!Row2.
Now that I have the sheet pretty much finished, I want to paste the same report on each succeeding page for a total of 25 sheets. I can copy Sheet2 and paste into each successive sheet, but then I need to change every single formula so that where it said AT2 it changes to AT3, where it was H2, H3, etc., from A to BY.
Problem: Can I use something that will automatically take the information for succeeding sheets and take as its source the succeeding row from the source page?
I don't know that I'm describing that sufficiently, so:
Sheet 3 would take as its source Sheet1, row 3.
Sheet 4 would take as its source Sheet1, row 4.
Sheet 5 would take as its source Sheet1, row 5.
Sheet 6 would take as its source Sheet1, row 6.
Etc., etc.
In the past I used the find/replace function, but this sheet seems way too complicated for that. As an example, one cell alone on my master sheet is
=IF(ISBLANK(ConnectMLS_export!S2),(IF(ISERROR(LEFT(C15,FIND("X",C15,1)-1)*RIGHT(C15,LEN(C15)-FIND("X",C15))),"",LEFT(C15,FIND("X",C15,1)-1)*RIGHT(C15,LEN(C15)-FIND("X",C15,1)))/43560),(ConnectMLS_export!S2))
so I would need to change S2 to S3 everywhere it appears, but there are probably more than a hundred places on the sheet that I'd have to manually change 2s to 3s, etc., for 25 sheets, which is why I'm wondering if there isn't a better way than going cell by cell, sheet by sheet.
See an example of the problem I'm having in this excel file with the explanation inside
example.change.values.question XLSX - Free Download - Uploading.com
I basically want the source of the data in a cell to be changed once another cell value is changed. I tried multiple formulas with no luck as well.
Is there any way to directly go to a formula? Instead of searching it through all the sheets? For example, if I have a formula =SUM(Sheet1!A1:A5) is there any way for me to automatically go to the source?
View 7 Replies View RelatedI am sure this is simple but cant get me head around it (tried paste & paste link).. I am wanting to copy formulas in file (A) to file (B) with data source linked in the file (A).
i want is when i copy the formulas to file (B) sheet 2 and it should retain the original path say H:excelfile A[sheet1]$G$1+....xls and source from which the data is being used.
I am currently pulling in all cells from various worksheets, into a specific tabs in a workbook. Each source is pulled into a seperate tab such as Workbook A is pulled into tab Company A using the following formula which works fine.
Code:
"Path[Workbook.xls]Worksheet!ReferenceCell"
Note: This is done due to the use of indirect in the next formula and its inability to work on closed workbooks. Within a summary tab, I am then pulling in specific fields from each of the aforementioned worksheets using the following formula
Code:
=IF(OR(M$220="",M$218=""),"",IF(ISNA(VLOOKUP($B223,INDIRECT
("'"&M$217&"'!"&M$220),COLUMNS(INDIRECT(M$220)),FALSE)),"",VLOOKUP
($B223,INDIRECT("'"&M$217&"'!"&M$220),COLUMNS(INDIRECT
(M$220)),FALSE)+M$222))
M217 - Worksheet Name
M218 - Worksheet Date
M220 - Worksheet Range
M222 - Additional Fee
B223 - Lookup Value
The issue is that one company out of 14 pulls into this workbook (using the first code) just fine with all values visible, but ONLY when the source file is open. Even if I manually update the link, the values do not change.
Is there a setting or something to check, maybe in the source workbook? I have ensured that automatic updates for links is on and there are no macros in the source workbook.
I'm compiling statistics from a number of separate workbooks. When I (or more to the point - one of my staff) open the Stats Workbook the formulas show #VALUE unless the other Workbooks are opened. I want the Stats to calculate without the user having to open the other workbooks as most users won't know which ones to open.
I could generate VBA code when the workbook opens to also open the other ones but wondered if there was another solution.
I have a simple reporting sheet where the data for orders place is in one sheet and on the other sheet is an imput box for 'date' and it filters through and presents a table of data and two graphs for the date chosen.
I give a daily report (contents of this sheet) but I also need to leave this sheet available for anyone to open and change to another date.
I want to copy the repor sheet into a new one and email but I want the graphs to remain, not go blank when anyone changes the original sheet.
Excel 2007 / Windows 7.
I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
View 3 Replies View Related[URL]
However, when I use the IF statement in the data validation Source box, it invalidates the fact that you can only choose from the list. In other words, the user can now enter anything.
I am linking two worksheets together. Formulas work fine except when I close the source sheet, it turnes into #REF error. I saw in one of the posts that you can not use INDIRECT formula on closed workbooks (INDIRECT is a part of the formula in my sheet) and downloaded the changingExternal. zip file as directed. Although, after staring at it for a while, I realized that I had no idea as to what was going on in there.
View 3 Replies View RelatedNot sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?
View 5 Replies View RelatedIs there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
View 3 Replies View RelatedI have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies View RelatedI have tried numerous versions of macros I found. Most get the same error of; Range object error or script error.
Here is the formula: VLOOKUP(C$13,VESSELS_DATA,ROW(30:30)-12)
What I would like to do:
1) double click to on the cell with the formula
2) goto the worksheet "DATA_TABLES" that contains the Named Range "VESSELS_DATA"
3) go down the rows until the c13 is matched
4) offset column the same as in the above formula "Row(30:30)-12"