Replies

Hi Sam,

I didn’t understand the CorrespondingValue = & the difference in the way you found the last empty cell. I got what Pieter was saying in his second post, so I think I’m all good now!

Thank you guys for all your help! I always know I’ll find what I’m looking for on WOPR…

Hi Pieter…

I know the vlookup function, but this has to be done in VBA!

I used Sam’s code and it worked great, but I have a quick question on yours.

What is the difference between the code Sam gave me and the code you gave me. There seems to be a difference in the way you find the first blank cell as well as the actual code in the Select Case statement.

Is this just a difference in syntax preference, or is there something more?

Thanks!

Thank you Sam! This works beautifully and explains how I have to do a couple of other things I needed to do as well..

Thanks!

OK, I think I just solved my own problem.

I took a look at the original code (down through recording a macro) and started commenting out lines one-by-one. It continued to work until I commented out

.zoom=false

So by keeping in the .zoom = false my code works to set my page to print the way I want it..

Thanks!

Hi Steve,

I have reposted my attachment because I attached the wrong file. ‘

As I mentioned in my other post the actual spreadsheet is much, much larger. The sheet that most of the data will come from goes out some 30 odd columns. Most of this data is not needed by a particular group of people who need to look at the data in a clean summary format (They also need to print out the summary)

Auto-filter will not work for what I am doing.

Hi Everyone,

I have attached a sample worksheet. Here is what I would like to do. I would like to type in a DMA number in Sheet 2 Cell B1. In the appropriate cells below this I would like all of the data that is in the DMA (from Sheet 1) to show up in the table.

Not sure what I have to do to make this happen? I think vlookups, offset, arrays???

Could someone help me

Thank you,

Oops..

I attached the wrong file!!!!

I have attached the correct file that I wanted and changed my original post with the right file.

The reason a VLOOKUP won’t work in what I am attempting to do is that there is more than 1 unit in each DMA. As you can see in my new attachment I want to see what units fall in that DMA…..

Thanks for your reply, but that won’t work for what I’m doing…

I actually have about 8 worksheets in the workbook and the summary sheet will be collecting data from each tab based on what DMA is typed in…..

I did it all on my own!!!

I am so excited!!!!!! Here is what I did:

{=SUM(IF(Data!$D$3:$D$598=Scorecard!B31,IF(Data!$L$3:$L$598>0,IF(Data!$M$3:$M$598=””,Data!$J$3:$J$598,””))))}

Hans – Is using the ISBLANK better than saying > 0 ?

Hello..

I need help.. I think the appropriate way to solve my problem is with array’s, but I’m new at them, and still learning how they work.

In the following attachment I am trying to figure out the sales sensitivity based on the attached cash flow. I would like for the IRR & Payback (yrs) columns to compute automatically without having to manually enter each sales level (like I have done for 100,000, 120,000 etc.)

Please take a look at the attachment and let me know if you have any ideas…

B5 (Investment) is only used in 2 calculations: IRR & Payback. The 18000 is a one time expense in year 0. It is not part of any of the COGS, Labor, or Fixed Cost.

-Leslie

For our purposes this is the same as cumulative cash flow. We us cumulative cash flow (profit) to determin payback.

I just threw this example together to provide more details of how it feeds into the table.

Thank you Andrew…

I appreciate your response (and in the future will remember to put XL in my subject line

I will try your solution in the future, but this time I think I will use Hans!

Thank you!

Hans…

Thank you very much! I was actually looking for the quick & dirty solution, so this will work.

Somtime in the future I will look into Andrew’s solution!

Thank you….

John….

I really like your answer. I used it and it worked great!

Thanks everyone!