Posts by 126389

• Dependent Combo Boxes

I am using dependent comboboxes because this way the user always sees the dropdown arrow. Isn't there a way to always show that dropdown arrow using lists instead ?
Thanks

• Formula to compare columns if the value is same

Re: Formula to compare columns if the value is same

I have seen that you were presented with some high-tech solutions to your problem, written in (for me) too difficult code.
Usually, I try to find a low-tech solution first ... and there is one.
Have you tried CONDITIONAL FORMATTING combined with some extra COUNTIF columns ?
Suppose: your 5 lookupcolumns are A2 to E2000. In cell F2 you count all values that are equal to A2

Code
``=COUNTIF(lookupcolumns,A2)``

; and drag that code down to F2000. Do the same up to column J (so that you have 5 "COUNTIF" columns that match your 5 lookup columns.
Then, select cell A2 and in Format-Conditional Formatting, select Formula is and type

Code
``=F2>1``

; then goto Format on that same prompt and chooze the format you like, for instance red font. Press OK.
Now copy the format of cell A2 to all the cells you want evaluated.
And there you have it.

Too low-tech ?
It does make the spreadsheet a little bit heavier because you will add 5 new columns with formulas, but it is easy to comprehend.

Good luck
Johan

• Find Smallest In A List

Re: Find Smallest In A List

Sorry, good question.
Let's say: the first one of that same value in the list, then the second one etc.
Johan

• Find Smallest In A List

I am trying to find the smallest values in a list. that part is easy (use SMALL). But, I want the ref to the cell where that value is in, not the value itself.
Additional problem occurs when more than one of these values are the same.
Example: A1 = 1; A2 = 3 ; A3 = 2 ; A4 = 2 ; etc.
If I want the smallest, I want "A1" as result, for the 2nd smallest I want "A3", for the 3rd smallest I want "A4".
So you see that a MATCH formula will not work beceause some of the values are the same.

Any ideas ?

Many thanks
Johan

• If then else if VBA

Re: If then else in VBA

Appologies for the late reply but I have been struggling with this issue for the whole weekednd and beyond.
Root-cause turned out to be an End If that was positioned at the wrong place.
I also found out that that negation "ElseIf .getstring(8, 49, 4) <> 2400 Then ..." is not required afterall.
Bottomline is that my program does what it is supposed to do now.

Many thanks for all your input.
Johan

• If then else if VBA

I am trying to get the below statement work in VBA, but I always get the following error message: "Else without if"
[vba]
If Trim(.getstring(8, 49, 4)) = "" Then RSR_OBJECT!ACTDEPTTIME = Null
Else If .getstring(8, 49, 4) = 2400 Then RSR_OBJECT!ACTDEPTTIME = 23 & ":" & 59
Else if .getstring(8, 49, 4) <> 2400 Then
RSR_OBJECT!ACTDEPTTIME = .getstring(8, 49, 2) & ":" & .getstring(8, 51, 2)
End If
[/vba]
Thanks for any help
Johan

• [Solved] Lookups in non-sortable list

Good day, EXCEL QUESTION
I am basically trying to do a vertical lookup in a non-sortable list (find a name in column A and get the corresponding value from column B).
Vlookup requires a list that is sorted ascending, but such is not possible in my case.

Thanks
Johan

• [Solved] VBA: Closing files with VBA

I tried option 2 but I had to write a Close-function for each workbook. I may presume that Wills option makes a bit more sense ; I will try that one later.
Anyway, it works.
Greatly appreciated ! Many thanks to all !
Johan

• [Solved] VBA: Closing files with VBA

Hi Will, ea.
As I said before, the formula works perfectly.
But now I was trying to see what it does when one of the files is already closed ==&gt;&gt; it gives me an error message, of course (because the files I'm asking to close is already closed).

Is there some code I can add that checks if the file is open, and if yes then close, if no then disregard. Something like that...

Johan

• [Solved] VBA: Closing files with VBA

It cannot possibly be THAT simple ??!!
But yes, it can !!!
And it works just fine.
The only thing that doesn't close is Excel itself, but I guess that it won't be possible to close Excel from within Excel.
Thanks
Johan

• [Solved] VBA: Closing files with VBA

Hi all,
I have lets say 3 Excel workbooks open, and I want to close the 2 that are not ctive by pushing a button.
I tried writing a simple code like:
Windows("ARPs.xls").Activate
ActiveWindow.Close
Windows("flt_summary.data").Activate
ActiveWindow.Close
but I get a prompt asking: "Do you want to save the changes you made ?"
No, I don't want to save the changes, I just want the workbooks or files to close, so every time I have to click NO.
Waste of time, I am sure VBA knows a way around that but I don't know it.

Anyone ???

While we're at it: is it perhaps possible to close ALL the files by clicking a button in Excel, even the active workbook ?

Many thanks
Johan

• [Solved] Formulas: Date format in Access

Hmmm,
our company is American, so we are kind of forced to use the US settings.
I wil work my way around it. I think I'll use the Medium date.

Thanks anyway
Johan

• [Solved] Formulas: Date format in Access

Good day,
The default setting for Date format in Access is MM/DD/YY.
But in Europe we usually turn this around = DD/MM/YY format.
Does anyone know how to force this format in a table in ACCESS without having to use the MEDIUM DATE format ?

Johan

• [Solved] Formulas: The 25th of a Month in Access

Guess what,
I entered the formula in the Default Value of the Expiration Date in the table ... and it works.

So I don't have to do it in the query afterall.
I admit, in the query would be more logic, but technically it works fine in the table.

Thanks for everything
Johan

• [Solved] Formulas: The 25th of a Month in Access

Yeh, it works !! In the query at least.
Thanks a million
Do you know if it is possible to make this function in the table itself, or is such impossible in Access ?

Johan

• [Solved] Formulas: The 25th of a Month in Access

Andy,
I will try your suggestions. Looks more simple than I anticipated.

Many thanks
Johan

• [Solved] Formulas: The 25th of a Month in Access

Hi everyone,
I have a problem in Access:
I am preparing a database that shows all kinds of products and the date that these products are sold.
I want to automatically show an expiration date as well. That date should be the 25th of the current Month + 2.
Example: product X is sold today 11SEP03 ==&gt;&gt; the expiration date should be 25NOV03.
Can a function for this be added in the table itself or must that be done in a query ?

I have been looking through the various available functions but nothing fits.

I do not know how to create functions myself.

Many thanks for any hints.

Johan

• Formulas for rounding up numbers

Hi James,
I checked Access ; it doesn't contain a ROUNDUP function in its expression builder.

In case that all of the results are between 0 and 1, and every result greater than 0 should read "1", then an IIF function will do (calculated field):
Roundup: IIf([fraction]&gt;0,1,0)

Where [fraction] is the name of the field showing your 0.23 result.

You don't even have to include the field that makes the 230/1000 calculation ; you may make that calculation in the calculated field itself.

hope this helps
Johan

• Excel Rounding

Hi there,
try the ROUNDDOWN function.
Example:
=ROUNDDOWN(A1,3)
Where A1 contains 31.354842%

The result will be 31.3%

Hope this helps
Johan

• Filtering data in Access table

Hi,

I am asked to create a report in Access that shows specific data from an Access table.

Origin Destination Occurrence
CDG NRT Missed connection
FRA MXP Damaged
EWR STN Something else
CDG FRA DDDD
CDG AMS DKDKDK
STN EWR FEUREU

Above is a small sample of a fake list showing origin, destination and occurrence.
I would need to produce a report that gives me specific origin-destination pairs.
For example: I want to ask:
- everything from CDG to NRT
- and everything for destination STN
- and everything with origin FRA
- etc.

To do one of these things a simple expression in the query would suffice, but I get stuck with the combination of the various questions that need to be asked.

The result must show in one and the same report.

Any ideas are most welcome.
Johan