How to link to Excel Sheets Together

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Misc
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Help with a formula please? I have a spreadsheet which contains rows of client data. The clients at the top of the sheet are the active clients, and are separated from those at the bottom (non active) by a blank row. Can anyone help me with a formula which will give the number of active clients i.e. by identifying the row of the topmost blank cell in column A. Many thanks, V Tag: How to link to Excel Sheets Together Tag: 808604
  - 2
    - Need to pick up data from left of a space starting from the right? 1 4210210 134.40 2 30362920 395.40 3 820401-2 116.20 4 728435514 5152.06 5 820401-1 194.57 6 820501-1 365.90 7 42111438 1158.50 I need to find the space from the right to left then bring back left of that mark to the right. Should look like this: 134.40 395.40 116.20 5152.06 194.57 365.90 1158.50 Thanks Tag: How to link to Excel Sheets Together Tag: 808603
  - 3
    - Non-Contiguous Named Range? Hi - I've created one dynamic named range to hold all values from cell D2 to last cell in column (so if user adds another value to bottom, named range extends automatically to include that value). Now, I'd like to create another named range which is a subset of the main named range ... this subset would include all values which start with "escrow". Is there a way to build a named range like this? The values won't necessarily be contiguous (ie, user adds a new 'escrow' value to bottom of column and doesn't re-sort the whole list). Thanks, ray Tag: How to link to Excel Sheets Together Tag: 808599
  - 4
    - Rearrange data into other columns I have a spreadsheet I use to assign our committee audit areas. The same group of people is rearranged and put into the other columns (per month). It is rearranged in no particular order, but I attempt to ensure that the same person does not have to audit the same area two months in a row. Currently, I manually create this. It looks similiar to this: Area to audit May June July ... East Side Nancy West Side Ann South Side Joe North Side Jamie Office Ruth Metals Sherry Warehouse Becky Is there a way to rearrange the data for each month so that I'm not spending hours attempting to ensure that everyone is seeing each area? Tag: How to link to Excel Sheets Together Tag: 808598
  - 5
    - Formula for formatting font/style Hello! I am creating a spreadsheet where, in a certian column, each entry starts with a bold capital letter that codes for something, followed by a text description. I am hoping a formula or setting exists so that I can type my information in and automatically capitalize and bold the first letter, then keep the rest unbolded. Is this possible, and if so, what's the formula? Thank you! Tag: How to link to Excel Sheets Together Tag: 808593
  - 6
    - PivotTable - Sum and Count As part of my PivotTable, in the Data field I have 2 columns, one for the Sum of a value and one is for the count of another value. The PivotTable displays what I want, each on a row, but I would like the currency sign to appear on sum values but not of course on the count values. I tried clicking on the Sum column in the data table and changing it to a number using the PivotTable field option but it doesn't stay. Is this possible to do? Tag: How to link to Excel Sheets Together Tag: 808590
  - 7
    - X EQUALS A NUMBER I would like to place an x in a cell and it repesents a number: For example: Always Frequently Occationally Seldom Never =1 =2 =3 =4 =5 Only one x per row. There are several rows with questions and each answer needs to be represented with an X and the X needs to have a value. Tag: How to link to Excel Sheets Together Tag: 808581
  - 8
    - Format row color based on individual cell I have a list of work orders. In column G, they are listed as being one of four types, A, b, c, or d. I would like row 1 to have a background color based on the type in column g. I would like to do this throughout the spreadsheet. I have tried conditional formating, but I haven't been able to get it to work. Thanks for your help. Tag: How to link to Excel Sheets Together Tag: 808574
  - 9
    - contact list I already have a contact list made out that has 3 rows and I print it out when I'm done adding/deleting. Everytime I add a new contact it moves the list down off the page and I have to manually move each contact to the top of each list. I want it to automatically move it to the top of the next list. Tag: How to link to Excel Sheets Together Tag: 808570
  - 10
    - Sorting Pivot Table by Total Does anyone know if there is a way to use the Grand Total column of a pivot table to sort data in decending order? Tag: How to link to Excel Sheets Together Tag: 808566
  - 11
    - Select, find in another book, copy, return and paste Excel 2003 I have multiple catalogue workbooks, all use the same layout. I want to update the on hand stock in column H for the item numbers I select in column B. All on hands are in workbook peter1.xls in column G. I have recorded the steps I would like to do with the macro recorder but I want this to work with any cell or range I select in B from any of the workbooks I open. Sub Find() ' ' Find Macro ' Macro recorded 15/08/2008 ' ' Windows("Beverageware 111 Basic Decorated.xls").Activate Selection.Copy Windows("peter1.xls").Activate Cells.Find(What:="P221558", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Range("G1991").Select Application.CutCopyMode = False Selection.Copy Windows("Beverageware 111 Basic Decorated.xls").Activate Range("H8").Select ActiveSheet.Paste End Sub I hope this explains what I need. Tag: How to link to Excel Sheets Together Tag: 808556
  - 12
    - Select Wich Cells To Include In Print Good Evening All, Excel 2007. I have a ss (report), in which I have included notes for the user, eg 'click on button to refresh data'. These notes have been placed directly in available cells, (ie are not n a comment). I would like to exclude these note ranges eg D4:F4, (note in D4 but carries over into F4) from appearing in the printed version. Is this possible, or do I have to use a comments box, & then clickon the not to show in print option? Cheers Guys, Mathew Tag: How to link to Excel Sheets Together Tag: 808555
  - 13
    - Maximum Allowed Rows I need to exceed the 65536 rows allowed in Excel. My work needs to be saved in the csv format, and it must be a single column. Is there any way I can create a single column with several hundred thousand rows? Tag: How to link to Excel Sheets Together Tag: 808554
  - 14
    - Scheduling Functions I'm creating a spreadsheet to track approaching deadlines for tasks to be completed (on a construction project). I'm trying to figure out if there is a function/formula that I can use to highlight cells as the dates approach. For example, if today is 8/15/08, all cells that contain a 8/22/08 date or closer would be highlighted. Any help would be much appreciated. Tag: How to link to Excel Sheets Together Tag: 808553
  - 15
    - Using index and match Hi all- Iâ??m, trying to use an index and match function to return text from cells on anther sheet. =INDEX(Formulas!$J$3:$K$6,MATCH(AM7,Formulas!$J$3:$K$6)) The formula looks for a value that matches AM7, it is suppose to return text from cells on another page shown below. I keep getting the dreaded #n/a. I have the text organized in cells Clmn J Clmn K Row 3 4.0 This was out-standing Row 4 2.8 Pretty good but you need ot work on Row 5 1.5 You really need to focus on this more 0.0 No comment here Anyhelp would be very appraciated Cheers George Tag: How to link to Excel Sheets Together Tag: 808552
  - 16
    - Issues when trying to print file from Excel 2008 Mac to Excel 2007 The file was created in Office Mac 2008 Excel and when we try to print in Excel PC 2007 it formats the document from 11x17 to another size that is not satisfactory. Can someone help please? Tag: How to link to Excel Sheets Together Tag: 808538
  - 17
    - Copy fill I have a large file that resulted from a pivot. Along the first column of the pivot I have cost center text that often looks like numbers but isn't because many of the centers also have letters. In the second column of the pivot I have job identifiers. Now, each cost center in column A may have multiple jobs. I have to do further work on the pivot results so I copied the values into another worksheet. My problem then becomes copying and filling the blanks in column A with the corresponding cost center text. If I grab the fill handle and double-click, the resulting fill assumes the number looking text is numerical and increases each cell by one (when I want to fill the blank spaces with the exact same text). Since my file is very large, this becomes a major pain (I have to highlight the copy-from cell, do control-copy, highlight the range of copy-to cells, and do control-paste). Is there some easier way to do this? Tag: How to link to Excel Sheets Together Tag: 808534
  - 18
    - On excel, can you merge the data from 2 diferent cells I import data from another program to excel, and it place the last name on one column and First name on another column. Is there a simple way to combine both without cutting and and pasting. Tag: How to link to Excel Sheets Together Tag: 808530
  - 19
    - Stange numbers Hello all, Some one in my office has ask me for help and I could not figure this out. The setup:you have a set of rows with number and those number are formatted with "Accounting" two decimals no symbol. When doing a manual sum or using the AutoSum, whey would my number come out like so â??0.00â?? and not a dash? If you increase the decimal out there is a number, â??0.00000000000000164316543â??. ?? The number are whole numbers, e.g. 24.25, 100.25, 562.64 ect. Thanks Tag: How to link to Excel Sheets Together Tag: 808523
  - 20
    - Can I assign keyboard shortcuts for special characters in Excel 20 I work a lot in Excel, using Chinese PinYin (Chinese words using our letters, rather than Chinese characters) - where every single word has at least one character with an accent on it! In Word 2007, you can assign keyboard shortcuts to enter the relevant letter - such as an "a" with a grave accent (CRTL+`,a) or with an accute accent (CRTL+',a). These shortcuts make sense (ie. easy to remember) as they involve the actual letter + something that's on the keyboard, and you don't have to go into the Insert Symbol menu/window each time. You can also assign your own shortcuts to characters that aren't pre-assigned. In Excel you don't appear to be able to use these. Instead it appears that the only way to do this is by going into the Insert Symbol menu. This is very slow!! Even if you pin the menu to your Quick Access Toolbar, it's still very annoying since you have to switch between keyboard and mouse for every single word. And you can't leave the window open whilst you continue with your typing. Can anyone suggest a way I can assign shortcuts and use them in Excel 2007? (Preferably these would be the same shortcuts as in Word, as I won't be able to remember the ALT+0000 number for all the different characters). Help please!!! Tag: How to link to Excel Sheets Together Tag: 808519
  - 21
    - Spacing with formulas Hello all, I have the following data. I need to get the first set of data, then the 2nd set and then 3rd. The problem is that the data will not be consistent, as you can see below. Now, the data is gather with a formula, (1st formula) =OFFSET(A1, MATCH("Line#Number AmountReason For Difference",A:A, FALSE), 0, 1,1), (2nd formula) =LEFT(B158,1), then (3rd formula, final) =IF(C158="1",B158,""). I know that you can use â??text to columnâ?? and have it put in the cell next to the original data, but is there a formula that I can use? I would like to avoid using a VB code for this (my user are not to savvy). 1 4210210 134.40 2 30362920 395.40 3 820401-2 116.20 4 728435514 152.06 5 820401-1 194.57 6 820501-1 365.90 7 42111438 158.50 End results is such: 1. 4210210 $134.40 2. 30362920 $395.40 3. 820401-2 $116.20 4. 728435514 $152.06 5. 820401-1 $194.57 6. 820501-1 $365.90 7. 42111438 $158.50 Thanks Tag: How to link to Excel Sheets Together Tag: 808518
  - 22
    - Next time sheet is touched, remove items from cells? I had this before and did not get any takers. I think the chalenge is more getting to do it once and not to check each time? ============= Not sure how to do this, but I have a sheet that I have a macro export data and update a field to say processed in cell F5. I want to know if there is a way to remove that if someone does anything to the sheet like update a description or update numbers. This is more a control thing so I know that the file will match the export and if someone updates something on the sheet, then the word "processed" in cell F5 will be gone. thanks in advance Tag: How to link to Excel Sheets Together Tag: 808517
  - 23
    - Help I used to use F4 for locking the cell or in other words it used to put $45$ around the number so it would not change as you copied it down a range of cells. Now it does printing. How do you change what function does what? Cannot find this anywhere... Thanks... Tag: How to link to Excel Sheets Together Tag: 808513
  - 24
    - Should i delete heading/subtotal rows when consolidating? I am ready to try RDB's macro for consolidating several worksheets into one using Excel 2003. 1. In preparing the worksheets to be used for consolidating, do i need to delete rows that are not "data" rows? I would still need them in the final worksheet but more for formatting the report. 2. If answer to 1 is yes, is there any way to automate the report formats in the consolidated/merged worksheet? Thanks greatly! Tag: How to link to Excel Sheets Together Tag: 808512
  - 25
    - Days and Dates look up 1/2/2008 VC 2.00 5/21/2008 VC 10.00 5/30/2008 VC 10.00 5/31/2008 VC 10.00 6/21/2008 VC 10.00 6/22/2008 VC 10.00 Is it possible to correlate dates with days by looking up a 2008 calendar? What I am trying to do is find day of the week which corresponds with the date (e.g. 01/02/08 - Wednesday). Thank you Tag: How to link to Excel Sheets Together Tag: 808511
- Next
  - 1
    - How do I abbreviate words? I am a data modeller and I need to abbreviate business names into column names. I have multiple words in a cell that I have to abbreviate by looking against a dictionary For example I have Invoice Amount and it needs to be abbreviated to inv_amt. I have a worksheet containing 2 columns one with the abbreviation and other with the complete word. How do I write a macro to convert a long list of business names in to abbreviated column names? Any advice is appreciated Regards Tag: How to link to Excel Sheets Together Tag: 808509
  - 2
    - data from sheet to sheet automatically Is there a way for me to be able to get data that I input on one worksheet automatically inserted into another worksheet... Ex: I have a yearly budget with the monthly totals, And then I have the monthly budgets with the weekly totals. Can I get it to where when the weekly totals are added up for the month, (Worksheet 1 monthly total) that the monthly total in that worksheet automatically gets inserted into the monthly total on my yearly budget (Worksheet 2 yearly total)? Tag: How to link to Excel Sheets Together Tag: 808501
  - 3
    - If Statement Help Hello, My boss needs me to do an IF statment on a spreadsheet. Here is what I need: In the Column "Interest Rate" the cells are percentages 0.00%, 5.50%, 2.25% and so on. In the Loan Type Column are the loan types depending on the Percentage Rate. What I need is this: If Interest rate is 0.00% then Loan Tpye is "Letter of Credit" How do I get Excel to do this if the user enters 0.00% in the interest rate column? Thanks!! Tag: How to link to Excel Sheets Together Tag: 808497
  - 4
    - Link Merged Cells From reading the comments here, it looks like the general idea is not to link merged cells but is there any workaround to linking merged cells to merged cells? I've tried to reference the whole range and the top left cell, e.g =Sheet1!A9 but neither work for me. Please give me ideas!It seems so simple but I can't get them linking! Thanks, Orla Tag: How to link to Excel Sheets Together Tag: 808495
  - 5
    - Compati Checker - erroneous links warning I copied a sheet from one workbook to a new one where it is the only sheet. Every time I try to save the new workbook (in XL11 format) I get the Compati Checker warning me about links to unopened workbooks - even citing 6 occurences. Problem is that: - Edit Links is grayed out (suggesting there are none) - Connections shows no Connections - manually looked at every cell in the sheet and found no links - Search using Find for "=" found no occurrences in entire workbook How do I 'fix' links that don't exist ? Since I am still getting used to developing in XL12, I don't want to turn off or ignore Compati Checker, but if it's giving me bad info that's a problem and I am losing confidence in everything it tells me. Tag: How to link to Excel Sheets Together Tag: 808487
  - 6
    - footers I need to set up a footer in the following way: File Name page # of # Date & Time I use this format in Excel and Word and I cannot get it set up in 2007 version. Tag: How to link to Excel Sheets Together Tag: 808486
  - 7
    - Inserting a checkbox into Excel 2003 How do you insert a checkbox in Excel that you can check and uncheck? Tag: How to link to Excel Sheets Together Tag: 808482
  - 8
    - General question about 2003 and 2007 VBA in Excel I am currently using Excel 2003. I noticed in buying various book regarding the setup and coding it pretty much the same from 2000 - 2003. However, I have purchase Office 2007 and by reading the forum that 2003 and 2007 and are like night and day (for the most part). My question is this: Before I start buying a bunch of 2007 books, will it be necessary to buy books referring to VBA or is the VBA coding that's used for 2003 the same for 2007? I ask this because I have books showing how to code for example "If" statements. So would I code and "If" statement the same way in 2007? I hope this is not going to be like learning all over again. :o) I know my question may seem like a bit simple but, I just to make sure I know what books I need and which ones I can still use before building another library. :o) Thanks!! Tag: How to link to Excel Sheets Together Tag: 808480
  - 9
    - Bold When I bold one word in a cell and then add a new row below the words are also bolded and I have to go back and unbold them is there a way around this so that I don't have to continue to do this? Tag: How to link to Excel Sheets Together Tag: 808478
  - 10
    - Really bad with formulas... need help please :-) Hello everyone, I'm really bad with formulas but I am sure this is possible. I hope this is possible I need a formula that will look at a text value in any given cell, A2 of Sheet 2 for example, and search and tell me if that same text value appears in ANY row of Column B on sheet 1, and if so return a 1(yes) or 2(no) type result, Is this possible? Looking around it seems like VLOOKUP coul be used to do what I need but I don't follow the logic required. Any help greatly appreciated Tag: How to link to Excel Sheets Together Tag: 808477
  - 11
    - Tracking time editing spreadsheet With manually logging, is there a way to track the amount of time spent working on a spreadsheet? Tag: How to link to Excel Sheets Together Tag: 808475
  - 12
    - My arrow keys in Excel move the opposite direction We installed Office 2007. In my Excel workbook, the left an right arrow keys move in opposite directions. Also, my backspace and delete keys do opposite functions. This is only happening in one workbook. Tag: How to link to Excel Sheets Together Tag: 808474
  - 13
    - Protect a block from line insert/delete I have an Excel doc where there are two parts side-by-side. On the left is the data that I manipulate - adding and removing lines as needed. On the right side are the labels that I copy into the left side. These do not change. If I add or remove a line on the left side, I'd like to know if there is a way to protect the right-side from line insertions and deletions? Tag: How to link to Excel Sheets Together Tag: 808472
  - 14
    - Pivot Table - Calculated Item I have greated a calculated item to sum the first three months of the year as Qtr1. Is there a way to edit this formula. Thanks Nigel Tag: How to link to Excel Sheets Together Tag: 808463
  - 15
    - creating a quote How do i create a quote? Tag: How to link to Excel Sheets Together Tag: 808460
  - 16
    - Protect, unprotect Hi, I have made a timeheet in excel for around 100 users. I have emailed this and the majority work just fine. On the sheet is a button with a macro attached that clears the form for the next weeks use. Very basic macro that selects cells in which they can enter data and deletes them returning to the first cell afterwards. This works great. I have spoken to one usr who receieved a run-time 1004 error because some of those cells are obviously locked. There is no reason for this as it works on many other PC's. However, to combat this I thought I would simply add ActiveSheet.Unprotect to the beggining of the macro and ActiveSheet.Protect to the end and solve the problem. When I do this however, running the macro causes Excel to close down. Please help as the only other way around this is talking him through selecting all the required cells and making sure they are indeed 'unlocked' which I would prefer to avoid. Also, does anyone know why th ecells would have locked for him and no one else? He is on the same version of Excel 2003. Tag: How to link to Excel Sheets Together Tag: 808456
  - 17
    - Bold in a cell Question, Is it possible to bold only part of a cell with a sentence in it example: "Delivery Contract" Can you bold just "Delivery"? Tag: How to link to Excel Sheets Together Tag: 808451
  - 18
    - Exporting from Report Central to Excel I'm trying to export from Report Central (sql-3) to Excel (2003) and I have a few issues: 1. Can I suppress the rows at the top of the report so that I don't have to delete them in the spreadsheet? 2. When the report is large (e.g. 20,000 rows) the export takes a long time (e.g. 15 minutes) - is this because drilldown links are being exported? and can I stop that since I don't want those drilldowns in the spreadsheet? Thank you to anyone who has suggestions! -- PMML Tag: How to link to Excel Sheets Together Tag: 808443
  - 19
    - highlight I have this formula =IF($A1="","",IF(TODAY()>=$A1,"expired",$A1-TODAY()&" days")) How can i make this formula highlight the cell when the date it's one month before today() I want the cell to become red with one month before today If A1 is 15.09.2008 and the "today" is 15.08.2008, the cell it's showing me 30 days and the cell to become red. Thank you in advance Tag: How to link to Excel Sheets Together Tag: 808440
  - 20
    - Please Help Hi All, My stock of products are in four stores, for each store either I am over stock or I am having a lower stock than the needed qty. I need a formula that can transfer the products from the stores were I am over stock to the stores that needs this stock. Can any one help with this. Example of the isuue, P.ref store 1 store 2 store 3 store 4 over missing over missing over missing over missing p.ex 4 6 9 7 P.yx 10 12 20 5 P.xx 3 5 2 4 Thanks in advanced. Tag: How to link to Excel Sheets Together Tag: 808438
  - 21
    - Moving Columns Excel 2007 You can no longer move rows/columns in 2007 like you could in 2003. Help tells you to grab the move pointer and drag it to a new location. However, rather than move the new column between a couple other columns it wants to replace the contents of one of the existing columns. This task used to be easy. Is there some other way to do this (vs. inserting a blank column and copying and pasting. GRRR). Is there a fix on the way for this? thanks, BK Tag: How to link to Excel Sheets Together Tag: 808435
  - 22
    - add 2nd Name and Date to Code Hi I copied this code from Ron de Bruins site everything was fine until I tried to add a 2nd name to the TO: field and to the subject line I want to add todays date below is the part of the code that I need help with thanks in advance On Error Resume Next With OutMail .To = " I WANT TO ADD 2 NAMES HERE" .CC = "" .BCC = "" .Subject = " Stats for" here I WANT TO ADD TODAY'S DATE Tag: How to link to Excel Sheets Together Tag: 808432
  - 23
    - Formula Help HI, I have to set up a formula to multiply only if the number is greater $100 by 0.60, The first $100 are not charge but bigger than that is $0.60 per each hundred starting at 101, 201, 301, etc..Please help Tag: How to link to Excel Sheets Together Tag: 808421
  - 24
    - Extract multi level data Hi I have a spreadsheet (Excel 2003) that contains a column for employee name, and a second column for manager. These two columns contain up to eight levels of organisation heirarchy. What I am trying to do is extract the reporting structure. I have tried pivot tables but can only get one level of reporting lines. Anyone have a suggestion? Tag: How to link to Excel Sheets Together Tag: 808418
  - 25
    - Inserting a zero Hi all, I am working on a spreadsheet that as a column telephone numbers, however, the '0' at the beginning of every number has been dropped. How can I insert this so every number begins with a '0' ( the std's of the numbers are from all over the UK so vary greatly) Many thanks to anyone that can help me. -- Janice Tag: How to link to Excel Sheets Together Tag: 808416

Greetings,

Can I get feed back on how to link information on two excell sheets.

I have information on two sheets. The information basis is the ID number,
but I need certain information to be included in both sheets or make a new
sheet that combines the information of both by using the ID to link them
both.

Thank you.
--
Grace

--
Grace

Top

RE: How to link to Excel Sheets Together by James_Thomlinson

James_Thomlinson
Fri Aug 15 16:56:15 CDT 2008

Take a look at the vlookup function...
--
HTH...

Jim Thomlinson

"Grace" wrote:

> Greetings,
>
> Can I get feed back on how to link information on two excell sheets.
>
> I have information on two sheets. The information basis is the ID number,
> but I need certain information to be included in both sheets or make a new
> sheet that combines the information of both by using the ID to link them
> both.
>
> Thank you.
> --
> Grace
>
> --
> Grace

Top