Selective Columns In Connected Workbooks
Mar 20, 2014
I have a workbook with several columns, some of which are for internal company use only.
Sometimes we need to send an updated list to business partners, but they don't need all columns.
Is it possible to make another workbook which takes only specific columns, and excludes the stray info at the bottom of the original workbook? See examples.
Alternatively: Would it be simpler to make an extra sheet in the original workbook, and somehow choose "Safe only this sheet to a new document" whenever someone requests the list?
View 1 Replies
ADVERTISEMENT
Jul 21, 2013
How to selectively transpose a row of dates to columns. I'm not sure exactly how to explain this, so below is an example of what the data look like entered into the spreadsheet:
study ID
provider
visit 1
visit 2
visit 3
visit 4
visit 5
[Code]....
I'd like to extract the data into a new table on another worksheet that looks like this:
Date
provider
study id
visit #
7/21/13
Test Name
10001
[Code]...
This is just a quick example, but basically it would continue through all possible visit dates for the first study ID, then move to the next row of data (i.e. the next study ID) and extract the data from the row and transpose it in the appropriate columns moving down...
View 14 Replies
View Related
Nov 15, 2012
I'd like to grant access to selective columns on a shared worksheet.
For example:
Columns A to D should be edited by only me
Columns E to G can be edited by anyone
Column H should only be edited by someone else
The worksheet has to be shared and has been shared by me. I know how to protect & hide locked cells etc but the problem I'm running into is this...
After locking & password protecting say columns A to D and then sharing the worksheet, I am unable to edit my own columns (columns A to D) without first entering the password.
However in order to enter the password I need to unprotect the sheet. And in order to unprotect the sheet, I need to unshare it!
Is there any way around this or perhaps a completely different way of approaching it?
View 3 Replies
View Related
Jan 11, 2008
I have created an application for users that requires the user be connected to the network. I wanted to do a check to see if they are connected, and if the user is not connected they would get a message box telling them that they need to be connected to the network, then the workbook would close. Below is the code I have, but when a user is not connected they do not get my messagebox, instead they get the excel error message "excel run-time error '52': bad file name or number", and the debugger line that gets highlighted is in the function DirExists that checks the length of the directory of the file path: Len(Dir(strpath))
Option Explicit
Function DirExists(strpath As String) As Boolean
If Len(Dir(strpath)) = 0 Then 'this is where the debugger highlights
DirExists = False
Else
DirExists = True
End If
End Function
Private Sub Workbook_Open()
Dim strpath As String
Dim strfile, strfile2 As String
strpath = "\n530fs1PCLFileSharespcl_repositPricing_ToolsPAT"
If Not DirExists(strpath) Then............................
View 2 Replies
View Related
Nov 3, 2008
I have an excel assignment that has about 4000 orders for wood. I am suposed to find the company that ordered the most wood and of what kind. I am really at a loss for how to do this and have combed over my book quite afew times trying to figure it out. However it has little if any excel info and nor does the ** that came with it outside of how to install data analysis tools. I figure I need to find a way to get excel to combine the numbers together for orders from the same company but after trying for afew hours I have gotten no where.
View 3 Replies
View Related
Feb 14, 2014
I have a macro which copy text from a sheet (sheet-1) and paste it into another sheet.
I want sheet-1 to be hidden, or even better, very-hidden, but when I hide sheet-1 the macro run fails. Respons Run-time error '1004'...
Is it a solution around this 'problem' or does sheet-1 always has to be visible ?
View 3 Replies
View Related
Feb 20, 2008
Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.
My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?
Const intCells=9 'actually, this is no constant but a variable
Const i=4 'actually, this is no constant, but a nested for- loop-value
Const intLinesInBetween = 12 ' amount of lines, which separate my values
Dim j As Integer 'simple counter, runs through all cells
Dim rngUpperRow As Range 'Range of the upper row = normalized time vector
Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted
Dim intCells As Integer ' amount of experiments in range............
View 4 Replies
View Related
Jun 6, 2013
I want a boolean function to return whether the PC is connected to the Internet. How to start creating this.
Reason:
I have code that checks an online database and downloads data after the user presses a button.If the host computer is not currently connected to the net, I would like to display a message of my choice instead of trying to run the code.To determine whether to run my message or not, I need a function that will return TRUE/FALSE if the host PC is net connected or not.
View 11 Replies
View Related
Apr 26, 2009
I have Excel 2003 SP3 on Vista Home Basic fully patched. My code runs very slowly until I pull out the ethernet cable then it runs quickly.
View 9 Replies
View Related
Mar 4, 2012
I am seeking some code that will check that the PC / Laptop has an online ethernet connection via it's NIC (Network interface card). I want it to output to the spread sheet to a Conditional cell (Connected / unconnected).
View 2 Replies
View Related
May 7, 2009
Is there any way to have a web query stay connected and get live data from a site while still being able to use excel at the same time?
Basically the site has information that changes by the second and i need to keep a running record of it, and when it hits the variables needed i want to be able to trigger a set of code.
View 9 Replies
View Related
Jul 25, 2012
I have a couple of points, and when i do a scatter plot with connected by smooth line plot, it seems like excel does a bezier splines interpolation. I am wondering am i able to extract the data plots from the data chart?
for example if i have
X - Y
0 - 5
180 - 0
360 - 5
I would like to have all datas of Y from X (0 - 360 degree every degree).
View 9 Replies
View Related
Jun 3, 2012
I now have XL 2007 but no longer work at the same place so do not have access to the external data source. I would like to view the SQL statements that I wrote back then but keep getting error that I am not connected to external data source and so cannot see my sql statements. I have MS Query 2007.
View 1 Replies
View Related
Dec 13, 2009
workbook1 column A has tag numbers, workbook2 column A also has tag numbers. I want to compare the 2 columns and if the tag number in book 1 is also in book 2 i want it to return 25B-F138-28-01 in column B workbook1.
View 4 Replies
View Related
Aug 22, 2013
I want to compare two Excel workbooks which have multiple columns and identify the rows only if all the valves match in both the workbooks, i am attaching a file to understand better.
View 3 Replies
View Related
Aug 26, 2009
when I run macro get an error
The code will go through each line of data checking to see if it can find a match. If not, the cells will be highlighted yellow. It checks from both sides, first checks sheet 1 compared to sheet 2, then sheet 2 compared to sheet 1. That should encompass the variations between the 2 sheets.
View 7 Replies
View Related
Feb 15, 2012
I would like to have a macro or a function, which could compare two "A" columns in different workbooks. macro should run from the first one wb, which gonna have less data in a column than wb2. so, if, let's say, A3 in wb1 differs from A3 in wb2, an empty row should be added in wb1, and now we would jump and compare A4 in wb1 with A4 in wb2 and so on..
View 2 Replies
View Related
Jul 28, 2008
I d like to ask for a vba code that adds two columns starting from d5 and e5 for several worksheets called multi1,multi2,multi3,........The d column contains only numbers, the e column contains number and letters (product descriptions)
View 9 Replies
View Related
Feb 10, 2008
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 Related
Feb 13, 2009
I am trying to create a form with dropdowns (see attached file), so that when a particular company is selected in the first drop down, the second and third only display the relevant business units and departments respectively.
The INDIRECT function has confused me and I am not sure how to use it.
View 4 Replies
View Related
Apr 28, 2009
IS it poss to sort an entire sheet by one column then another, but to exclude certain rows that include certain data.
This is what i mean, I have a sheet where data is entered on a daily basis. this data has tobe kept with in its dates. Each day starts with a start time and ends with an end time, and on occasion a break is included within the data. then between dates there is a blank line.
IS it poss to sort the data and exclude the blank lines and the start, end and breaks within the data, but keep the data within date, so all data for the 12/4/09 stay within this block...?
View 6 Replies
View Related
Feb 20, 2009
I have a inventory report that has multiple stores. I am trying to add the total of all stores in two columns named "AE" and "ASC". Anytime the store name says "AE" i want to add the totals of that store in the "AE" column. Anytime the store name says "anything except AE" I want the totals to add in the "ASC" column. Hope this makes sense. How do I do this? Do I use conditional formating?
View 9 Replies
View Related
Oct 17, 2007
I need to be able to extract particular named columns from multiple files and then populate them in a new excel workbook. Ideally, this would allow me to specify the file directory, the column of interest (based on standardized names), and then run the macro. If this could be done but I would need to have the excel workbooks open to extract the data, that's not a problem (not sure on this). In the multiple workbooks, the data of interest are all on worksheets that have the exact same name, and the column names in Row 1 of those worksheets are all consistently named the same.
Ideally, in the new workbook I need the Row1 column names to contain the source file name from which the data in each respective column came from (with what I'm needing to do all of the extracted data will be the same parameter, e.g., water quality pH values stored in columns from 20 excel files all in the same file directory). Attached are two examples of the many files I have in this format. The worksheet containing the data of interest is called "Station_Comprehensive_Cleaned". In this case, an example column to be extracted from the latter named worksheet in each example workbook and pasted into a new workbook (hopefully/ideally with the respective source workbook file name as the column name) is Column 34 called "Solids_Dissolved"
View 3 Replies
View Related
Dec 15, 2007
I have two workbooks, one is reference (W1), other is the one that needs update(W2). I need macro that compares reference column in W1 with targeting column in W2, then for match cells update two or more cells in same with new data from reference cells in W1, and for end to report what data in W1 in reference column wasnt find in W2. I tend to use macro in reference book W1, and to update book W2 without opening, so I need this macro to work just with file manager from excel . No need for user modul for targeting columns, they are static in both woorkbooks. In practise in W1 is invoice prices with part numbers, at other one is the same, but with prices for distribution (with formulas), I want ti automaticly refresh part numbers with new prices, an to have report what part numbers are not in price list (W2)
View 2 Replies
View Related
Aug 29, 2013
I have turned off show zeros on a sheet. This is good in general, but for certain cells I would like excel to show zeros if the formula output for that cell is zero.
Background information: I am working with a very large workbook, and the final sheet is my output file. This sheet is then exported to a CSV file and imported to another program in my workflow process. I need most blank cells to remain blank, so that they are interpreted as blank in the output program. Some of my cells are actually zeros which I intentionally inputted as zeros...those should be interpreted as zero and not blank.
View 5 Replies
View Related
Mar 13, 2013
I have a cheque list with Column A for Cheque number, Column B for Date, Column C for Payee Name and Column D for Amount
Cheque number ranges from sequence 1200 to 3600
Is it possible to set up a formula to sum the amount of cheques in column D based on the cheque sequence number of column A . In other words if based on criteria cheque sequence number is
View 4 Replies
View Related
Feb 19, 2007
i have a VLOOKUP formula
=VLOOKUP(W3,CAT_A,3,1)
where CAT_A is a dynamic named range on the CAT sheet, I also have CAT_B thru CAT_F
I would like to make the VLOOKUP formula neater by using INDIRECT and the initial letter from column I as follows
=VLOOKUP(W3,INDIRECT("CAT_" & I3),3,1)
However I keep getting #REF, where have I slipped up
View 9 Replies
View Related
Aug 15, 2014
I'm using two workbooks and looking to see if two columns match, then value in the cell should be the corresponding value of that cell.
For example:
I'm using
If(Match(A2,"name of other workbook",0, I want the value in column F of row 2, "not in raw data")
But it keeps saying too many arguments.
View 6 Replies
View Related
Aug 14, 2009
I have a set of workbooks, with multiple sheets within each which I receive each month from field units. The formats are identical.
What I want to do is to extract data from one column on one specific sheet within each workbook to a summary sheet on a new workbook. In the snapshot below, I would like to collect data from the "Actual" column (in yellow) and then paste it on a summary sheet.
Monthly reportingEntity 13. Risk reportingJanuaryMarket Risks - LoansJanuaryTargetActualVarNumber of competitors 110%Market share 1%1%0%Ranking in market 110%Concentration of portfolio – region-wise (number)1%1%0%Concentration of portfolio – region-wise (value)1%1%0%Market growth rate 1%1%0%Inflation rate 1%1%0%
This would appear in a new summary worksheet as below: (the cell labelled "Entity 1" would then appear as the column header in the summary sheet.)
Monthly reporting - summary sheet3. Risk reportingTarget rangeEntity 1Entity 2Entity 3Market Risks - LoansActualActualActualNumber of competitors 1Market share 1%Ranking in market 1Concentration of portfolio – region-wise (number)1%Concentration of portfolio – region-wise (value)1%Market growth rate 1%Inflation rate 1%
The steps I imagine are:
1. Open all workbooks in a specified folder (folder name upon prompt)
2. Search each workbook for a sheet titled "Risk Reporting"
3. Copy the specified cell (for the column header) and the specified column into a new sheet in a new workbook
4. Move on to fill up the next column, and so on ...
Also is there a way to order the copying so that the columns always line up in a specified order? (e.g. Entity 1, followed by 2, followed by 3 ...). One way I guess is to fix the column headers in my summary sheet, and then fetch the data from the corresponding worksheet, by matching the names.
View 9 Replies
View Related
Aug 23, 2008
I have 2 reports with the same column headings. I want Excel to compare each one on the common OrderID and then just show me the ones that don't match--either one invoice can't be found on the other, or in the cases where they are found, the invoice amounts do not match (IOW create an exception report).
View 2 Replies
View Related
