or Connect
Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 36

post #526 of 533
Quote:
Originally Posted by Reggs View Post

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.
post #527 of 533
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?
post #528 of 533
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...
post #529 of 533
Quote:
Originally Posted by jgold47 View Post

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.
post #530 of 533
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?
post #531 of 533
max(25,min(75,your_calculation))
post #532 of 533
Thanks so much otc! It worked perfectly. I'm frustrated I did not arrive at that conclusion myself. This is the best forum ever.
post #533 of 533
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?
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread