Posts by bradster

Re: 3 column data lookup

Thanks to all for your answers. I think concatenate is the way to go, though will try both methods formy own taste - I might learn something!

I have 3 columns of data (with 200 rows) and need a lookup function that will find the result from 3 selections from those columns. Eg Find the result from col 4 (or anywhere really) that satisfies criteria for col 1, col 2, and col 3...A vlookup is part way there but not quite. thx

Hi, Im trying to write a formula to return certain data from a row (just 1 cells data), where the row has been identified as being non-blank in col x.
What command will identify a non-blank cell, even where there could be more than 1 non-blank cell in the column ? thx

ps: so If I look down a column, I could see 10 non-blank cells in this column.

Re: vlookup+offset+function

Super stuff. Now I can get paid.... ;>

Re: vlookup+offset+function

WIllR - You have outdone yourself. Thx very much. I look fwd to looking at the function in detail. Is this the easiest way to do a basic lookup?

Thx again.

Re: vlookup+offset+function

I have attached a copy of my sheet. Im trying to set up a worksheet function that will allow me to alter Timesheet!B7 and see the lookup results in the orange table. Its a timsheet with the source data on the Input tab. I thought offset and vlloup would be the best way.. thx

Hi Im trying to lookup a series of values using the offset function, by reference to one cell only.
Using a vlookup to get to c4, I then need to return 5 seperate values from various cells awayfrom c4. For eg. The 1st value is 1 row down and 5 across, another is 3 down and 1 across. These are seperate lookups, but Ive been using a nested lookup/offset fundtion which doesnt seem to work. here is it:
=offset(VLOOKUP(B7,week,3,FALSE)),0,1,0). Any thoughts pls ??thx

Re: Rounding numbers

Thanks to you both for replying. The int command worked a treat. Problem Solved. cheers lads.

I have a small list of numbers that I need to reduce to a figure, but keep positive. The "reduction" figure needs to be rounded to the nearest million.

Eg:
39,790,834.
I need a cell function that will produce a positive number, in round millions, that will reduce the number above, to a figure between 1 million and 2 million. The answer in this example would be -38,000,000, and the formula will need to reference to a single source cell. That is - 39,790,834 will reside in c4, for example.

Im stuck !

Thanks to you both for your replies. Derk - your solution is perfect. Thanks again.
Brad

I have a spready which get data via basic links from another s/sheet. When updating the links I get an error that the source spreadsheet "contains an incomplete calculation". How can I trace and repair this situation pls ? Thx, Brad

Thanks Viking, but not sure this is what I want.
I run one macro that runs a different one (lets call it #2). As part of #2 some yes/no question boxes (just to print the current page) need to be answered. But i would like to have macro #1 answer these boxes without me having to press "no" each time. This is so I can leave it to run by itself to the end... hope this clears my question...?

Hi all, I have a small routine that is called by a larger macro. The subroutine brings brings up 5 yes/no dialog boxes, I'd like my macro to answer no to all instances of this question. Is there an overriding statement I can insert to assume no in all cases ? Thx, Brad

Thanks D,

that rocks.

B

All, I have a procedure that turns on filters... but sometimes the filter handle on the column wont drop down for use. Has anyone seen this before ? Also what is the VBA command to remove filters ? I need to switch them off at the end... Or how can I test to see if they are on, then remove... Thx

There is one,and it has dynamic ranges etc.. so maybe one of these is askew.
Will take a look, cheers.

Bradster

I do have one chart, and it has various dynamic ranges etc.. for getting its data.
Sounds like this could be the culprit.
Cheers,will investigate
B
:))

Hi all,
I have a spreadsheet that does not require any external links, but get "Your formula contains an invalid external reference to a worksheet", that I would like to fix.
There are unneccesary links to external XLS also. Firstly how can I find where these exist ? And then remove. Ps: I have tried searching in formulas for the file name, but no luck..

Hi again Ritchie,
Thank for your earlier reply, but Ive found a small problem.
As the code loops around to count upto the required cell, it looks for a blank cell as the the sign to move to the next row. As this varies each day, it will be a prob. If you wouldnt mind, and using visible cells again, I would like to count down a fixed amount of rows down column I, to get the same result. Is this possible ? Thanks:wink1:

Hi again Will,
Thank for your earlier reply, but Ive found a small problem.
As the code loops around to count upto the required cell, it looks for a blank cell as the the sign to move to the next row. This vaired each day and therefore will be a prob. If you wouldnt mind, and using visible cells againj, I would like to count down a fixed amount of rows down column I, to get the same result. Is this possible ? Thanks:wink1: