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 ?
Posts by 126389
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
; 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
; 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.
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.
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 ?
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.
I am trying to get the below statement work in VBA, but I always get the following error message: "Else without if"
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)
Thanks for any help
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.
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 !
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 ==>> 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...
Thanks in advance
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.
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:
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.
While we're at it: is it perhaps possible to close ALL the files by clicking a button in Excel, even the active workbook ?
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.
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 ?
Thank you in advance
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
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 ?
I will try your suggestions. Looks more simple than I anticipated.
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 ==>> 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.
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):
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
try the ROUNDDOWN function.
Where A1 contains 31.354842%
The result will be 31.3%
Hope this helps
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
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.