Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

File size limit Results

Hi,

I am trying to generate html files using data on an excel sheet. I have had quite a lot of success with this in the past using macros and functions etc. But in the past the data used to create an html file has always been in one row. eg 100 rows of data would create 100 html files. The excel datasheet contains data on paintings eg "painting name", "size", "artist" "image link" which is then all combined into an html file. Each row of data is about a different painting...

html file 1: painting_row1 & "by" & artist_data_row1 "size:" size_data_row1
html file 2: painting_row2 & "by" & artist_data_row2 "size:" size_data_row2

What I am trying to do not is create an html file that uses data from several rows. The best way to explain the problem I am having is to explain the desired end product. I want an html file with data about several "paintings".

eg:

Html file: artist_data_row1 & "painted" & painting_row1 & "and" & painting_row6 & "and" & painting_row34

So if an Artist has painted 23 paintings then I want to gather the data from the relevant rows. I am not sure how to do this.

More confusing for me is that I am not sure how to limit the data being gathered eg. If there are 23 paintings by an artist I only want data from 10. I would prefer the 10 to be randomly selected from the 23 but that is not essential.

Can I do stuff like this with functions and macros in excel or do I need to start programming?

Thanks, James

I keep running into this issue in a spreadsheet that keeps making its way back to me. The bottom line of text in some of the rows is getting cut off when I print. It also appears in print preview and when I PDF the document. I could fix this manually, but there are thousands of rows so you can see why I am trying to avoid this.

The file has no merged cells, none of the cells are anywhere near the character limit, autofit does nothing for this, and I've tried changing the cell alignment among other things in my search to fix this, to no avail.

Many months ago I remember searching and being able to fix this problem with a macro that increased the size of each row by a small about (2 pts or so) and that proved to be a good solution for me--but this time around, I can't find a code that does this, and my old document doesn't seem to have the code anymore--I either didn't save it or erased it for some reason. All of the rows are different sizes so I don't want to change all of the rows to the same height. I just want to increase the space in the cell around the text so it stops cutting off. Is there any way to either increase each row size by a few points, or increase the space between the text and the cell border?

Hello - I posted a quick question this morning and got 0 response...think my query looked ridiculous.... so a brief explanation.

Have a new group of employees using excel for daily reports - very limited excel skill (and computer skill in general).

Mgt has decided this group will be saving there files to a network drive, specific directory structure, there are numerous quality checks & weekly reports to be run from the daily submissions, end-of-job reports....etc.

Anyway, as foolish as it sounds it would be very helpful to create macros to automatically do the following:
*Name / Save all files (so the operator only has to open the "master" book and fill in forms to properly place these things in a network drive...
*Create new daily sheets (from a template in the master)...
*Pull weekly & end of job reports and save them to another spot...again on the network).

Other odds and ends...I've been able to macro away most of the issues. BUT!

These workbooks grow to different sizes and the "controls" for the macros are what have me flumoxed.

IF I COULD launch them from the RIBBON with a friendly little menu of my own...life would be easier (cause i could just train people to use the menu).

I tried editing the Quick Access Toolbar...and things worked OK for the sheet i was building...as soon as I saved a copy of the sheet with a diff name, the buttons to launch the macros always opened the original book....which will not work
Anyone know where to start on building what would have been a custom menu command bar in the ole days? what is the modern equivalent?

Sorry to not post an example, but I just need to know where to start researchin I'm not expecting a miracle.

This is not so much a question as an observation on what appears to be an extraordinarily bad bug in Excel 2010.

Open a new, blank workbook and do the following:

1. Use the developer tab to Insert an ActiveX control [a checkbox is what I used] on a worksheet

If the 'Developer tab' is not displayed on the ribbon, click File | Options | Customize ribbon and on the right panel click Developer tab

2. Right click the control, Format control...| Properties tab | Move and size with cells
3. Hide the row (maybe 2 rows) on which the control is placed.
4. Save the file and close it (the rows are still hidden)
5. Reopen the file and unhide the relevant rows- your control has disappeared??????

What's even more odd is that if you now click the 'Find and Select' button on the right of the Home tab and choose 'Selection pane', the control will be shown as being on the worksheet but it isn't possible to make it visible. I've done a limited internet search and found this behaviour being experienced by others.

My question: does anyone know of a fix or whether Microsoft is aware and working on this problem (and how to report it to Microsoft as a problem)?

Thanks,

Brian

Hello All,

I am in need of help to solve a problem in a spreadsheet I have been working on. I need to average a set of values from several columns and from certain cells in those columns, for example................

BA3 BA23 BA45 BA66 BA87 BA108 BA129 BA150 BA171 BA192 BA213

Also the same cells from columns BB BO and so on, and this would continue across other columns and as far down as cell 7663.

I have a formula to average across connected cells that works fine.

I just can't wrap my head around the correct way to do the same thing from non connected locations. I also need to be able to
have the formula not return the dreaded #Div/0 as not all cells will have a number to return, and the average needs to be
correct with cells that have 0s in them.

I would like to be able to attach this file so someone could take a look at it but it at this time is over 123mb in size, so that might not be possible through this forum.

If anyone can point me in the right direction I would be MOST grateful.

In case anyone would need to see this file to fully understand what I am wanting to do I can upload it to the You Send It site and provide a download link.
I began building this file in Excel 2000 until I ran into the 64mb limit, then started using Excel 2007 preserving the xls format.

ok, first off just a little background. I work with a Civil Engineer in Tallahassee, Florida. For every project we do we need to provide the city a "Tree Debit/Credit table. the table itself is not very complicated. it's actually very basic. the problem is when we run into a project that has a ton of trees on the site. the table breaks down into 3 maybe 4 parts. the first part of the table is just telling if the tree is protected. anything over 17 inches is considered a protected tree. the second part of the table has to deal with tree removal and the debits or credits that go along with the tree removal. this part is based on the size of a tree. a tree that is lets say 2" gets a value assigned to it. this is the "credit" so to speak that we are given if the tree remains. if the tree is removed then a debit is given. pretty basic, i know. what this determines though is the extent of the landscaping required. i have used a lookup formula for the debits/credits in the "Tree Value" portion of the attached file and an "IF" statement in the "Credit" "Debit" portion of actual table that will be submitted. the 3rd part of the table though is giving me problems. i know it is a format error but can not figure out how to get the "Totals" for the "Credit" and "Debit" portions of the table. If anybody could help me with this problem the help would be most appreciated.

To say my knowledge of Excel is limited is a vast understatement. i mean i can understand what the formula does, once written but actually making them all play nice together is where i stumble and fall flat on my face.

The spreadsheet I have created for work currently consists of 9 worksheets and about 200 charts, with each chart having its own tab.

I use it to capture and calculate trend data that is then transferred automatically to the charts which, in turn, are linked to a small handful of PowerPoint Presentations and Word files that I use to publish the results. This happens monthly and quarterly, so automating the process has saved me far more headaches than the year it's taken me to create and "perfect" the current system would suggest.

The structure and function of the spreadsheet is as follows:

- Eight of the worksheets are used to capture monthly data. Each month, my direct reports and others send me the numbers and I simply plug them into the row for that month. These sheets also have a series of calculation tables that the charts use.

- The ninth worksheet has a very small handful of cells that the spreadsheet uses as variables for calculations. Chief among them is the "as of" month, which I use to generate the charts and trends "as of" the desired month. Since the historic data in the other eight worksheets goes back from two to three years, and the charts only trend back a year or two, this cell allows me to generate historic data at the flick of a switch. This switch (selecting the month) drives the calculation tables mentioned above, which means the charts all update as soon as I enter the new monthly data and enter the "as of" month. It's a beautiful thing, really.

The problem is, the file is now 40MB in size, and takes upwards of 5 MINUTES to load!

So what is it? The charts? The calculations?

At one point I saved a version that had all the charts removed. No improvement.

While the delay is far from crippling, it is a tad annoying. Was there a better way to have done what I did to avoid this, or is it simply time for me to ask my boss for a new computer?

Speaking of computers, I am on a Dell Latitude D620 laptop running an Intel Core 2 at 998 Mhz with 2 GB of RAM. Windows XP Pro Version 2000, SP2, and Excel 2003 SP3. My options for playing with software are limited since this is a work PC. Unless I can get a hardware update, my options are limited to the spreadsheet itself.

Thanks!

hi.

I have a very interesting problem here and its solution, if possible, culd be very beneficial.

ok the question is like this. i have a spread sheet which u can consider to be that of Classes in a center. So i have different batches and diff no. of students in different batches. AT the bottom of each batch i take the total of each column, based on certain conditions, month wise. Different batches months coincide on the same sheet so that the totals for all batches for any month all fall in one column at various rows.

This formula for totals is same for all batches except that it works on a different range for various batches in the same column (for any one month). If there are 25 batches in a sheet, then every time i feel i need to modify this formula, i need to make changes at all those 25 places.

What i want to know and do is this.
Is it possible for me to create a formula template in some cell. The take that formula from there to all the various 25 places in the sheet. Find a dynamic way to calculate the range for each of those 25 batches which is automatically lifted by this formula and the results calculated. This would be tremendously beneficial because the next time i need to make some modification in the formula, i would be able to modify it at just one place and all changes would be accomplished automatically.

the formula template shud be sth like this (the formula itself is not important, it cud be anything). (if(iscount(RANGE),"",if(......) that i may place in just one cell. then place this formula at the various places reqd. and the RANGE be dynamically calculated (or Lifted) from a given cell against each Batch. If i insert or delete rows the RANGE shud be automatically modified as well.

i do have a my excel file but i am not able to upload it as its size exceeds 100kb, which is the limit to upload the xls file on this forum. Any help on that as well ?

I hope i have been able to express myself well enuff for u to understand the problem. Hope to get a reply to this very interesting and very useful problem.

Thanks All & have a great day,
Ajay.

Hello! I have a question here.

I have a MASTER sheet that is pulling in data from 22 other identically designed/formatted workBOOKS (not worksheets). It is an enormous amount of data to the point where the Master workbook contains approximately 3-Million formulas.

While the sheet does return a value for all the formulas, I question its integrity due to the sheer size of the 30-Megabyte Master workbook. I have audited in the past to see if it is producing accurate results, and have found discrepancy in the numbers whether the SLAVE workbooks were open, or closed.

My question is: Does excel have a limit of cells that can be linked to from a master sheet? I realize it may be because of PC-resources consumption by having all these files open at once, but I'm not sure if that is the reason, or a limitation in Excel (2007)

Hi,

I am trying to import a text file (1.2GB) into PowerPivot. I am not sure how many rows it has, but it has 32 Columns and I am thinking somewhere around 10 milllion rows. I have read all over online about how PowerPivot can take in many more rows than this, ten times as many and more. I have imported many text files into PowerPivot and I have never really had a problem. If I did get a problem I tried importing again and it seemed to work. I have tried to import this file multiple times and I keep getting the same message. I am not sure if it is a problem with my text file or with PowerPivot because it is taking around 40-48 hours to get through about 7 million rows of data importing. When I come in, in the morning 2 days later is says "Memory error: Allocation failure : Not enough storage is available to process the command. The Operation has been cancelled." I know that this can happen, but from what i have read it should not be happening on such a small file, in PowerPivot's perspective that is. Here are the Specs to my computer.

Windows 7 - 32 Bit
Microsoft office Proffesional 32-Bit
PowerPivot -32Bit
3.0 GB RAM
Intel Core Duo CPU E7500 @ 2.93 GHz

I have heard that 32-bit has limitations on how many rows it can handle, but i cannot believe a file this size, only 1.2GB can take 48 hours to import rows and in the end it doesnt even have the memory to get all of the rows imported.

If anyone has any suggestions on how to increase memory usage or if i should have a 64-bit system, or increasing Processor speed, any help is greatly appretiated.

Thanks


No luck finding an answer? You could always try Google.