The Excel Questions Thread

[Biggskip]



How much do all of you do to make your spreadsheets pretty?

I always color headings, center text, use different font in headings, make sure my cell boarders look good, and formatt as table>convert to range to get every other row colored. I also think numbers look best in Calibri. Delete unused tabs, color all used ones based on their purpose.

I think I'm the only one who does this. When I get spreadsheets from other people they are always so ugly.

Most of what I show to other people is pasted into a powerpoint. That I always make sure to dress up and make it look nice. The rest of the spreadsheet I don't have time to make look nice, and chances are nobody is ever going to see it anyways.

 

[Biggskip]

So I have sort of a weird problem. There is a file that I use to produce a report. The file was originally created in Excel 2003. I have converted it to work in Excel 2010. There are two tabs in the workbook that each contain a number of free from drawn images. During the process of assembling the report I run a Macro that does a SaveAs on the workbook to a new name, deletes most of the worksheets that aren't essential to the report, and then copy -> Paste Special -> Values everything on all of the remaining sheets. After the Macro is complete, all of the images are visible on the two worksheets in question. If I close the workbook and then re-open it, the images on one of the tabs are visible, but are invisible on the other tab. The images are still there and if you click and drag one of the invisible images to another part of the workbook it will become visible again. If the image is then dragged back to its original location, it is invisible again.

Thoughts?

 

[jgold47]

Hi guys - here is my question.


I have a worksheet that calculates a pair of values based on a single input. the calculation is based on 13 different variable that are driven by the value of the input.

I need to create a table of varying input variables, and their respective output (2) variables. Is there a way, short of manually entering those values onto the table to get excel to run each input variable through that model and return a value in each cell. Basically treat my worksheet as a formula. I've got no more ideas short of creating some sort of massive 9x13 if formula...

 

[Biggskip]



Hi guys - here is my question.


I have a worksheet that calculates a pair of values based on a single input. the calculation is based on 13 different variable that are driven by the value of the input.

I need to create a table of varying input variables, and their respective output (2) variables. Is there a way, short of manually entering those values onto the table to get excel to run each input variable through that model and return a value in each cell. Basically treat my worksheet as a formula. I've got no more ideas short of creating some sort of massive 9x13 if formula...

Yes, but you'll need to write a small macro that either uses a Do Loop or a For Next Loop. Let's say that your list of input variables has 20 different entries that go from A2:A21 and B2:B21. Have your formula be driven off of the content of cells A1 and B1. Then program your macro to pull the values of A2 and B2, put it into A1 and B1, then take the output from C1, and paste it into C2. Then loop to the next row for which you want to calculate.

 

[Reggs]

I have a calculation that can return any sort of number. That said, the min and max of the calculation has to be between 25 and 75. So for example 2+2=25, and 200+2=75. The value that needs to be returned has to be between 25 and 75. What can I do to satisfy this?

 

[otc]

max(25,min(75,your_calculation))

 

[Reggs]

Thanks so much otc! It worked perfectly. I'm frustrated I did not arrive at that conclusion myself. This is the best forum ever.

 

[henrikc]

Any recommendations on VBA guides? I'm looking for tips on best coding practices, preferably aimed at non-coders.

I just finished up my first proper macro at work. It's for a finance function where we invoice spare parts -- with 3 data sets from external sources containing spares we have on stock, customer quote for the project (i.e. price for each spare and qty) and finally what we have already invoiced. The macro creates a bunch of output sheets - including what we can invoice, any faults in the data (duplicate prices, spares on stock with no price, spares where the stock exceeds the quote, spares where we invoiced too many items according to the quote etc).
All in all the macro consists of about 15 subroutines, some doing (somewhat) advanced work and others doing simple stuff like formatting or copying data. The macro needs to be ran in a particular order, but it should also be possible for the user to run every subroutine one by one -- and occasionally fix things manually before proceeding with the next subroutine.

I'm about to put this into production, but I would like to take a second look at how I've organized it and read some general tips on how it's best to organize large macros.

TLDR; No real experience with coding, have a 15+ subroutine macro w/ 2000 lines of code, how do I organize it? What's the best practice?

 

[aglose]

Hey guys, I am in college and taking a course that focuses on VBA as the language, if any of you are experts/semi experts in VBA and are willing to answer the occasional question/help with issues I run into please shoot me a PM so that I can keep from clogging up this thread with questions that extend to Word or PowerPoint.

Thanks in advance!

 

[Reggs]

I have a cell with time formatting in it. The cell displays as "8:00" due to time formatting. but if I copy>paste value it's .0498567434. Anyone know how I can the cell to actually contain 8:00?

This is really frustrating. A lot of people seem to have issues with time formatting in excel but I can't find the right answer.

 

[gomestar]

why would you paste value of a time or date or even a percentage? Of course it's going to paste a weird number, it's how excel calculates the days between numbers like Feb-19-2015 and Dec-1-1999.

why do you need time to display? Are you calculating hours between time? Or is it just a presentation thing, which shouldn't be done in excel.

 

[otc]



I have a cell with time formatting in it. The cell displays as "8:00" due to time formatting. but if I copy>paste value it's .0498567434. Anyone know how I can the cell to actually contain 8:00?

This is really frustrating. A lot of people seem to have issues with time formatting in excel but I can't find the right answer.

copy the cell

to paste, use Alt- H V S U Enter

This will paste the "Value" of the cell, but formatted correctly. This works if say your "8:00" cell was actually a formula and you want to take the value produced by that formula and place it somewhere else where it won't change with the formula.

If you truly want to paste "8:00" and not just the formatted value...I don't think there is a good way. Either use an =TEXT(A1,"H:MM") formula and copy the value from there...or copy the cell,. paste it into notepad (or even the google search bar), and then copy/paste it back into excel.

 

[otc]

And gome is right that unless you are just dicking around, pasting values is not a great practice.

I do it fairly often when I am just exploring some data or checking over calculations done by someone else. Maybe paste values of the current results, then change some inputs or formulas and see how the results change. Or make a quick and dirty duplicate of some data that I want to move around or edit by hand as a check...

I do sometimes use paste values to populate a table shell that is already set to accomodate the values, such as if I made a table in some statistical software (already organized how I like, and all calculations done in the program, not in excel) just so I can send it to someone. This isn't necessarily a great practice...but **** you if you think I am going to export that stuff to a CSV, link the CSV to excel, read the data into the table with formulas, etc. all for a one-off table with 4 rows and 5 columns.

 

[gomestar]

I have a cell with time formatting in it. The cell displays as "8:00" due to time formatting. but if I copy>paste value it's .0498567434. Anyone know how I can the cell to actually contain 8:00?

This is really frustrating. A lot of people seem to have issues with time formatting in excel but I can't find the right answer.

copy the cell

to paste, use Alt- H V S U Enter

This will paste the "Value" of the cell, but formatted correctly. This works if say your "8:00" cell was actually a formula and you want to take the value produced by that formula and place it somewhere else where it won't change with the formula.

how is this different from Alt - E S V to paste value than Alt - E S T which (I think) is paste format? I rarely deal with time formats, so honest question.

 

[otc]



how is this different from Alt - E S V to paste value than Alt - E S T which (I think) is paste format? I rarely deal with time formats, so honest question.

it isn't--it is just "Paste Value and Format" rather than doing it twice.

and I know new-excel shortcuts, so doing it twice would be Alt-H V V, Alt H V S T Enter....which just seems gross.