I have created some link formula,
Example
CellA1 has ='Corridor Select- Input'!C5.
I was told by a colleague that there is a function in excel that allows me
to auto go to that linkd source. ie if I double click on Cell A1, I can
automatically go to Sheet"CorridorSelect-Input" Cell C5.
I have a spreadsheet in which I have several sheets, each of which has its own table populated by an SQL query that displays the data per sheet by customer name and then sorts by a status column that is either "open" or "closed" (Open in Descending order) and then sorts by created time (also in descending order). This table is 17 columns wide. Right next to this table, I have another 2 column table that is to be populated by hand. The problem is that the 2 tables don't talk to each other. For example:
If row 3 of table 1 contains an open action, then row 3 of table 2 will contain data pertaining to that action. If the action is closed via the database refresh, then it may move the action to row 10 now (as open is always shown at top) - however the data that was entered by hand in table 2 is still stuck in row 3.
find a way to make the 2 tables communicate and work together?
I cannot make it only 1 table as far as I can tell because the database refresh wipes out my additional manual columns every time.
I've recently aquired a spreadsheet previously maintained by someone else. After my initial review the speradsheet has many links that need to be updated, which I do via Edit-Links, but there are also some links that no longer need to exist. I have reviewed all sheets and some links just no longer exist, however when I try to break the link or change the source nothing happens. I have had the original owner also check and confirm and they have the same issue. Finally I tried copying all the tabs into a new workbook, but the link shows up again.
View 3 Replies View RelatedI have a periodic task which involves moving some sheets from an existing workbook and presenting it in a new workbook. There are some diagram that I move as well from the worksheet. However, I also move the source data for the diagrams into the new workbook as well. Everytime I do this I need to change the source data to the new worksheet which is quite tiresome. Is there a smart hack to actually point the diagram source data to the new workbook?
View 2 Replies View RelatedI have created a PPT which is linked with my excel data and charts. Now I have to copy it at different location (Both PPT and excel), But due to change in path; my PPT is not updating with latest data points and charts. I know, I can change link using edit link.
My PPT is having around 50+ link. some VBA code which can replace old path using the current path.
For example, if my graph currently shows B1:L1 range, how by clicking a spin button to make the graph show B2:L2 range?
...
I would like to copy cells from one worksheet to another, but have the cells that I paste link to the original document. Is there an easy way to do this?
So if I'm pasting from worksheet A into worksheet B, I would like worksheet B to link to worksheet A.
I have to prepare a monthly presentation for my company. I have all financial data in multiple tabs in excel that have been linked to many powerpoint slides.
The problem is, I update the same excel sheet every month and save it with a different name. How can I change the source in powerpoint to select and update all the slides with all the corresponding tabs.
At present, I use Edit link (ALT+E+K) > Select each slide on the powerpoint> Change source to new excelfile. When I select all the sheets, the "change source" greys out. Hence I have to do it individually.
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?
How can we program an auto filter a table linked to a cell. For example I have a table with 4 columns (A9:D20) and the 2nd row of the sheet is the filter item (A2, B2, C2 & D2).
Lets say column A has a list of months, column B has a list of names, column C is sales figure, column D is the customer. How can I filter let say for the total sales of Mr. X in the month of January.
I came across a code like this but I don't know how to apply it to my sheet. Of course this code is referring to a different table and links but I just need to learn how to apply this code to my sheet:
Activesheet.AutoFilterMode = False
Activesheet.Range("D2:D60").AutoFilter 1, Activesheet.Range("A2").value
AutoFilter.xlsx
how to make all cells displaying an item from the list source update with new information added to list source.
Example:
Cell "A1" on worksheet 2 displays the 3rd slot of information from list source on worsheet 5. I want cell "A1" on worksheet 2 to update when the list source information in the 3rd slot on worksheet 5 is changed.
Notes:
The list is set up by naming the columns on Worksheet 5 and is set as a validation list on the other worksheets. So on Worksheet 2 column "A" has the validation
VB:
AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
List=(Coulumn A 's name on worksheet 5)
Is it possible to have a code of say numbers or letter assigned to an explanation of that said code which automatically appears in the next cell?
See attached spreadsheet : Auto fill next cell with predetermined answer from list.xls
setting up a worksheet that forces a link to another worksheet when text is entered.
For instance, I will have several columns in the first worksheet (Sheet 1). For each entry, a tally ("X") will be added under the applicable columns. Most of the column headings are pretty straightforward. To keep things uncluttered, I want one of the columns to have an auto-link (?) feature so that when the user adds an X in this one column (we'll call it Column D), the user will then be auto-linked to another worksheet (Sheet 2) in the workbook. Sheet 2 will have room for more information regarding Column D, Sheet 1.
I'm trying to make a spreadsheet that can be used to easily build a collective list of steps, for a user to read and follow line-by-line.
I want a source sheet of "steps" that I can change over time, and the resulting tabs that reference the source sheet get updated/populated automatically.
I've pieced together some VBA code from other sources, which kind of does what I want it to:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Target.Column = 1 Then
Dim SourceSheet As Worksheet
Set SourceSheet = ActiveWorkbook.Sheets("Steps")
Dim TargetSheet As Worksheet
Set TargetSheet = Target.Worksheet
Dim c As Integer
Dim Source As Range
[Code]...
But there are some problems:
1) The data auto-populates into a row. It would read better if each step was in the same column, meaning rows would need to be automatically added upon selecting something from the drop-down list. The number of rows that need to be added vary based on the number of steps in the source sheet, for the selection made from the drop-down list.
2) If you make a change to the source sheet, my goal is to have the other sheets referencing the source sheet's lists of steps update automatically, so you only need to update the steps in one spot and everything you've built from them gets updated instantly. Currently, you must select a different choice from the drop-down list, and then change it back, before it populates the "new" steps from the source sheet.
This is my first time using VBA.
What I have so far is attached: testAutoPopulate.xlsm
Source tab contains vital information about some clients.
In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.
What i need to do is to be able to copy/paste my formula
[Code].....
(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:
Formula: [Code] ....
I will have to fill this formula to at least 100 entries down, so i need to make it work with ease
The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..
Attached File : samplev1.xlsx
Not 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 RelatedI am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
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 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 have the following code:
[Code] ....
When I run the macro, some columns are already hidden. The macro doesn't seem to autosize cells correctly. For instance, one cell in a row appears to have some contents hidden (or below the reading area of the cell). In other instances, the rows are auto-size to huge heights and widths.
I need the tabs of a project action log to auto-populate and auto-delete in a master log. (So when something is added or deleted in a tab it is added or deleted on the master) I use excel a little bit for work and personal finance purposes but I have zero experience with macros or VBA.
View 1 Replies View RelatedMaking a mission tracker that requires less upkeep than my units previous methods. I am using Excel 2007 and have some things in mind that I would like it to do.
On the down side, I have only basic experience with excel and have never used macros or VBA.
Even if what I am after could be solved with functions (which I prefer), I simply do not quite know how to make what I want without assistance. Ok, now on to the description.
The mission tracker aside from showing a list of the missions we have done during our program, it needs to also generate metrics automatically. In my worksheet, I am using columns A-G and rows 1-501.
The mission scheduling type in Column E are listed below. The letters on the left are what I am using in the sheet, the part to the right is what it really means:
Column D has the day of the week listed as: Mon, Tues, Wed, Thurs, Fri, Sat, Sun.
now the metrics I need to be generated are the number of different types of each mission compared, how many of each type of mission was flown per day of the week, how many of each type of mission was flown per month.
Basically what I need is a way to automatically count the number of rows that meet a specific criteria throughout the range. This would require being able to check the value/text of multiple cells at a time and count the number of rows that meet that criteria.
[SOLVED] 1. The first part would be to have the worksheet be able to count the number of each type of the 9 mission scheduling codes throughout the range. I would be storing the count in new cells that would then be connected to pie charts.
[SOLVED] 2. The second part would be to count the number of each type of mission that was flown per day of the week. That would require searching both mission type and day of the week. All I would need is a working formula for 1 set, then I could switch mission codes and weekdays to match all the 63 possible outcomes.
3. Knowing the number of each type of mission flown per month. This would be checking the date and the mission type. The trickier part that comes up in my mind is that our programs span multiple calendar years, so it would have to track it by month and year, that way there is a difference between Jan 2009 and Jan 2010. The counted numbers would be used to make histograms. It would be nice if the chart titles could be automatic based on the dates inputed in the mission tracker. If I needed to choose a maximum time length for it to cover, I would pick 3 years.
4. I would like to figure out how to make an inputted line of data on the main tracker sheet to create a copy in the corresponding worksheets based on schedule type, that way a person could look at/print a specific type without having to sort the main list.
The actual goal is to make it where a person can enter the 1 line of data per mission (cells A-G) and the mission metrics update automatically after each mission is inputed allowing upper level supervision to have current information quickly whenever it is needed. Currently we need about a weeks lead time to get the information asked for on our current progress.
Within a data validation selection, are you able to do both Auto Complete and Auto Delete? I have this posted at another forum [url]but have not been able to find a solution (a copy of the file, test. zip is there as well). I am not sure it is possible, or, at least I have been unable to get it working. I can do each, but not both.
I have also been utilizing:
[url]
[url]
I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell?
I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell? There is only one column of data.
Possible to auto count and auto sum based on Unique ID.
Download link to the file in question [URL] ..........
So for Example ID 79125 should get a sum 537.39 and count should be 12, also in any given week like 50 , 51 , 52 , week 1 etc.., duplicate entries must be all counted as 1
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
I have dates values in 3 columns.
A1 - Header - "Holidays in XXXX'
B1 - Header - "Holidays in YYYY'
C1 - Header - "Leaves by YOU"
A2:B11 have static dates consisting of 10 dates in each column.
C2:C11 - the user may enter any date at any point of time.
I would like to auto-merge the dates in all the 3 columns (A2:C11) in a single column say D2:D31 and then the system should auto-sort the column based on dates in any one order. So as soon as the user enters a value in say cell C2, all the 10+10+1 dates should get sorted.
Also the constraint here is a user may not apply all 10 leaves at in a year. So many of the cells may have blank values.
Below is my code. I need the row source for my combobox to be dynamic. I created a named formula and referenced the formula in my code. It doesn't work.
This is the formula "listname"
=OFFSET(MyList!$C$2,0,0,COUNTA(MyList!$C:$C)-1,1)
Private Sub ComboBox1_Change()
ComboBox1.RowSource = Range("ListName")
End Sub
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 Related
