I think there was a comment upthread about how to make a template that you can use when creating new workbooks to get the formatting that you want.
Is that what you needed?
If you have date/time data in cell A1, you can use a formula something like this to convert it to text and pad it out:
=TEXT(A1,“dd:hh:mm”)
Or if it’s already in text format, something like this:
=IF(LEN(A1)=4,CONCATENATE(“00:0”,A1),IF(LEN(A1)=5,CONCATENATE(“00:”,A1),IF(LEN(A1)=7,CONCATENATE(“0”,A1),IF(LEN(A1)=8,A1,“ERROR”))))
Okay, my turn - I have a file that I’m having trouble with. I have a system where I have one source file that feeds into two other files. The idea is that I load in the proper data into the source file, update some pivot tables and then when I open the other two files, they are auto-linked so that all I have to do is run a macro that paste-special values over the entire file and voila - I have two finished files that are pre-formatted.
My problem is that when I open one of the files that gets fed by the source file, I keep getting a “broken link” message. When I look to edit links, the only file that is feeding it is the source file. I’ve searched for #REF and it’s not in any of the tabs. Even after I’ve eliminated all of the links, I still can’t find a #REF.
I’m trying to find a way to find the links that are broken, but unfortunately I’m linking somewhere along the lines of 35,000 cells so I can’t just do a visual search. Any idea on how to search out and identify which cells contain the broken links?
taxi78cab – Sounds like you want to assign a macro to a keystroke or a toolbar button. This is all kinds of awesomeness. In fact, I have the exact same shortcut you are describing set up on my machine (one key stroke to format “units” or 1,234 and another to format “dollars” or $1,234).
Here are the steps:[ul]
[li]Tools – Macro – Record new macro[/li][li]In the box that appears, change “Store macro in:” from the default “This workbook” to “Personal macro workbook” from the drop-down list.[/li][li]In the same box, choose a shortcut key to assign the macro to[/li][li]Click OK, then format the cell like you normally would[/li][li]Click the stop recording macro button[/li][li]voila! You now have that formatting assigned to your shortcut key.[/ul][/li]You can also assign the macro to an icon on the toolbar:[ul]
[li]View – Toolbars – Customize…[/li][li]Scroll down the categories list to Macros, click and drag the Custom Button up to your toolbar[/li][li]Right click the button you just put in your toolbar (with the Customze dialog box still open), and select Assign Macro[/li][li]Find the macro you created in the list and you’re all set.[/ul][/li]Make sure you answer yes when you are asked if you want to save changes to Personal.xls (you’ll be asked when you exit Excel, if I recall) and now you will have access to the shortcuts in any Excel file from now on.
Is it possible to get Excel to calculate the coefficient between columns of numbers that are separated over multiple sheets? That is, if I want to find the correlation between Column A from Sheet 1 and Sheet 2, and Column B from those same sheets, is there a way to do that, or would I have to manually combine the columns on one sheet?
Thanks.
Varlosz,
If I understand then yes it is just the =correl() function, so for example if you had numbers in A1:A10 in Sheet1 and A1:A10 in Sheet2 the formula would look like =correl(Sheet1!A1:10,Sheet2!A1:A10).
No, it’s not that I just want to compare columns from two different sheets. It’s that I want Excel to combine the Column A from Sheet 1 with the Column A from Sheet 2, then combine the Column Bs from those sheets, then calculate the correlation between the two combined columns.
It’s not a big deal to combine the two columns manually onto a new sheet, but it’s kinda ugly and inelegant.
Another question, more to do with statistics than Excel, per se.
When measuring variance, what’s the effective difference between the VAR function, which estimates variance based on a sample, and the VARP function, which calculates variance based on the entire population? Why would someone prefer to use one rather than the other?
OK, got it. The way you suggest is one way, another would be more brute force but could be less ugly. You could literally construct the correlation yourself by creating sub sums and products and combining them in the correct way, calulate the standard devaition and covariance with “handmade” formulas. That would be a bit of a pain to set up, but would then automatically do it without having to combine columns. Other than that I got nothing.
Heh, I guess that would work, but I already went ahead and combined the relevant columns onto a new sheet while waiting for advice here, so I’ll probably just stick with that. Thanks, though.
Dear Excel Guy
This is probably an infantile question, but I must ask it -
I have a colum of numbers 1-40, representing thweeks of the school year. In the column next to them, I have the day of the year on which each of those weeks end:
(A) (B)
1 2/2/07
2 9/2/07
3 16/2/07
4 23/2/07
5 2/3/07
I also have a table of information of people who have paid levies on a pro rata basis and the week in which thier prorata payment expires
Name paid paid to date
Fred Smith $50 11 xxxxxx
what I need is a formula at xxxxx which will look through the row of week numbers in (A), find the week number that matches “paid to” in B and then reports the value (thats is, the date the week ends) in the field to the right
Is it dobale, oh Excel Guy?
mm
OK, I have a column of names of vendors. I want to see how many lines each vendor has. Is there some way of counting and totalling by vendor [for sake of ease, say i copy the entire column to a separate tab so i am not changing anything on the main spreadsheet] Is there some easy way to do this?
Well, I am not the Excel guy but perhaps you’ll find some advice from an Excel guy to be helpful.
I think the VLookup command might help you in this situation. Let’s say your table of weeks is located in A2 through B40 (assumes A1 & B1 contains column headings) and the “paid to” number to be looked up is in D2. The formula for xxxxxx would be:
=VLOOKUP(D2,A2:B40,2)
The 2 at the end means you want to return the value from the second column within the table of weeks. (Important: The VLookup function assumes the first column in the table is sorted ascendingly*.)
*Is that a word?
mamboman, one more thing… Since the VLookup formula will be returning a date in your case, you may want to format the cell(s) for xxxxxx in your preferred date format. Otherwise you’ll probably just get a number value representation of the date.
Anything on this one? I’m still unable to find an answer. The best answer I can come up with is to open the second file with the source file already open. Then it doesn’t come up with the “broken links” message.
Just a couple of thoughts.
-
Is the source file password protected? This could explain why it would work when you have opened it but not when Excel is trying to reference it.
-
When updating the source file do you add or delete rows/columns? This throws out the functions in some calculated cell even when they are on the same worksheet and it could have the same effect here.
Since I’m here anyway and I think I have an answer, I hope you and intention won’t mind if I give this a stab.
Though it’s not the prettiest of Excel features, I think Subtotals might give you what you want. It does some funky things so you should try it out on a copy of your sheet just to be safe. First sort your data by vendor name. Then highlight a cell somewhere in the vendor name column and select Subtotals from the Data pull-down menu. Fill in the dialog as follows:
At each change in: (select the column for the vendor names)
Use function: Count
Add subtotal to: Check only the column for the vendor names)
Replace current subtotals: Yes
Page break between groups: Optional but probably not
Summary below data: Yes
Another idea would be to use the CountIf function. This would be good if you know all the possible vendor names and have them in a short list somewhere else in Excel. To get a count of how many times one vendor, say the name Acme stored in cell D2, appears in the list use this formula…
=CountIF(A2:A100,D2)
This assumes your big list of multiple vendor names are listed in the range A2 through A100 (A1 being a column heading). This doesn’t require your data to be sorted in any way. Repeat and modify the formula for each vendor name in the short list.
Nope, it’s wide open.
No, the updates in the source files are copy-pastes directly into four sheets and then updates of several pivot tables. No structural changes.
Lazy Excel Guy clone chiming in…
It sounds like the file that does the calculations has cells that just contain the file name for your source file, but are not actually links. This could bring up the broken links message if your source file is not open and it is in a different directory from your analysis file. If you ever just typed in a file name into a cell to create a formula, you could have created this problem.
To fix this problem, you could try following the instructions at http://www.digdb.com/excel_add_ins/file_links_broken_find/ on how to find and fix broken links with their trial software.
Great, cant wait to try it later today=)