Excel booking system

[HansV]

But we do NOT want to convert the values to text here - we want to keep the leading zeros in the values (as opposed to just displaying them by applying a custom number format).

[DocAElstein]

Hi Hans,
I am not quite sure who or what you are referring to. I confess I have not looked at detail at the whole file, and some of my code lines were removing the leading zero which may be undesirable.

To elaborate: I might sometimes be changing something that is
_ looking like a number but held in text form, ( numbers held in a string variable),
into a,
_ number in number form, (so more like a number that is, .. well more sort of held like number ).
Pseudo like, changing
"36"
to
36
So I might sometimes also, inadvertently, be doing something undesirable like pseudo like changing
"03"
to
3

But (I think ) I was not converting any values into text though, Pascal (p45cal) neither- Mostly we were talking about getting rid of that warning. My ways perhaps do mess a bit with the actual things, whereas Pascal’s just prevents the warning showing, which might be better.
I thought maybe never the less something in what I did might be useful
Sorry if I missed something and/ or confused things.

Alan

( P.S. The reason for me using the word Text in a cell, was just to test/ check what I was doing in different data possibilities, as was my reasoning for including an empty cell. Maybe with hindsight some other word, or words, would have been better, something like xYz123b )

[johnv]

Dear HansV,

It's me again.
In the attached file, I've marked yellow N14:N15;P14:P15.
The O column lists Sundays, where I can have an appointment at 15:00 (=M3) hours.
I want the N column to automatically show the postal code of the appointment I have right before and the P column to automatically show the postal code of the appointment I have right after.
So in the case of 27.10.2024, I don’t want 12345 as output, but 10266, as that appointment is closest to 15:00.
If I do not have an appointment before of after, I’d like e.g. “--” as output.
I’ve tried to find the right formula on the internet, but no luck so far.

Thank you in advance!

One more question I have. When I have 1123 in M7, it shows both the Sundays where I already have an appointment in a postal code starting with 1123 and the Sundays where I don't have appointments yet, at all. How can I let the O column only show those Sundays where I already have an appointment in a postal code starting with 1123? I have tested the Excel sheet with real data and it looks promising!

Sorry, no.

See the attached version.


Mappe1 (1) (4).xlsx

[HansV]

Perhaps in N14:
=XLOOKUP(O14+$M$3, $B$3:$B$11+$D$3:$D$11, $E$3:$E$11, "", -1)
And in P14:
=XLOOKUP(O14+$M$3, $B$3:$B$11+$C$3:$C$11, $E$3:$E$11, "", 1)

[johnv]

Thank you so much!
I have copied the formulas multiple times in O3:O24;Q3:Q24 (see attachment).
Question #1: Is it possible to have the formulas only once in O3 and Q3, just as the formula in P3 is only once (so that it automatically repeats itself as long as there are dates in the P column)?
Question #2: E.g. Q13 now says “01234”, but I’d like e.g. this cell to say “--” as there no appointments on 29.09.2024 (=P13). Likewise, O16 now says “12345”, but I’d like e.g. this cell to say “--” as there no appointments on 03.11.2024 (=P16).
Thank you so much in advance!

Perhaps in N14:
=XLOOKUP(O14+$M$3, $B$3:$B$11+$D$3:$D$11, $E$3:$E$11, "", -1)
And in P14:
=XLOOKUP(O14+$M$3, $B$3:$B$11+$C$3:$C$11, $E$3:$E$11, "", 1)

[HansV]

In O3:
=IF(COUNTIFS(B3:B11, P3#, D3:D11, "<"&M3)=0, "--", XLOOKUP(P3#+M3, B3:B11+D3:D11, E3:E11, "--", -1))
In Q3:
=IF(COUNTIFS(B3:B11, P3#, C3:C11, ">"&M3+M24/24)=0, "--", XLOOKUP(P3#+M3, B3:B11+C3:C11, E3:E11, "--", 1))

[johnv]

Thanks again so much!

I've added the Settings sheet, because the date in the Events sheet are copied from a database and dependent on the settings there, the end time, postal code and city might be in different columns.
I’ve added the Private Appointments sheet, to list private appointments that one has and that one wants to consider when Excel proposes days for events.
In the Terminator sheet, on the left (“Specific”) only days are listed when there are events on that same day with a postal code starting with L4:M10.
I’ve moved all help columns to the (Formulas) sheet.

1) Is there a way to combine '(Formulas)'!G2:M2 into a single formula and combine '(Formulas)'!x2:x2 into another single formula?
2) Is there a way to let all help columns in the (Formulas) sheet continue down as long as there are dates in Events!A:A ('(Formulas)'!B2:N2) and in 'Private Appointments'!A:A ('(Formulas)'!Q2:M2)?
3) The private appointment (27 October) does fulfil the specific criteria, but it’s not listed under under Specific in the Terminator-sheet.

Thank you in advance!

[HansV]

1) Perhaps, but it would become very complicated and difficult to maintain.
2) Same answer as 1)
3) You cannot combine ranges using +, use VSTACK instead. For example in Terminator!F15:

=IF(OR(F3:F7=""), "E3:E7 ist leer", UNIQUE(FILTER(VSTACK(Events!A$2:A$100000,'Private Appointments'!A$2:A$100000), VSTACK('(Formulas)'!N$2:N$100000,'(Formulas)'!AE$2:AE$100000), "(No Results)")))

[johnv]

Thanks so much for your help.
I need some final finetuning for the file (see attachment) to work effectively for us:

In worksheet Terminator:
- In F3 I’ve added a menu with the seven weekdays. I’d like F16 and Q16 to list possible appointments on the weekday selected in F3. How can F16 and Q16 be adapted accordingly? (In worksheet Events, 04/10/2024 e.g. is a Friday.)
- I don't seem quite able to set F10, F11 and Q16 right. I have as start date 01/09/2024 and as end date 30/11/2024, but in Q16 it already starts with 11/08/2024 and it already ends with 03/11/2024.
- How can I adapt B16:E16, G16:J16, M16:P16 and R16:U16 in such a way that e.g. in D17 it says 15678 as on 06/10/2024 I have a private appointment that starts at 14 o’clock.

In worksheet Formulas:
- How can G2 be adapted in such a way that it checks whether the postal code of the appointment right before or right after starts with the corresponding numbers? (Now it namely checks whether the postal code of a random appointment on that day starts with certain numbers.)

Thank you in advance!

[HansV]

The attached workbook tackles the first two questions.
I have no idea about the other two. It has become too complicated.

Mappe1 (1) (4) (2).xlsx

[johnv]

Thank you so much for your help.
In Events!G2:Events!H8 (see attachment), I'd like to replace the texts with formulas.
The formulas in column G should list the postal code of the appointment on the same day immediately before the appointment of the current row.
And the formulas in column H should list the postal code of the appointment on the same day immediately after the appointment of the current row.
If there are no appointments on the same day immediately before and/or after the appointment of the current row, it should say FALSE.
Thanks again.

[HansV]

See the attached version.

Mappe1 (1) (4) (2) (ab 0808).xlsx

[johnv]

Thank you very much!

I do sincerely apologise; I namely made a thinking mistake.
I do need to make it dependent on TERMINATOR!E5 (see attachment).
In Veranstaltungen!H2:H8 I wrote down what the outcomes need to be when TERMINATOR!E5 = 01:00 (Veranstaltungen!G3).
And in Veranstaltungen!K2:K8 I wrote down what the outcomes need to be when TERMINATOR!E5 = 16:30 (Veranstaltungen!J3).
It would also be fine to combine the columns H:I into one column and L:M into another column, whatever works better.

I am grateful for your assistance!

[HansV]

Please explain how TERMINATORIES!E5 should affect the result.

[johnv]

I'm sorry for not being clear!
If TERMINATOR!E5 is 01:00, there's no other appointment immediately before on 06/10/2024 (hence FALSE in H2).
But, there's one immediately after (hence 01234 in I2).
There are 2 more appointments on 06/10/2024 (11:00, 13:00), but they are not immediately before or after 01:00 (hence FALSE in H3:I4).

[HansV]

"But, there's one immediately after (hence 01234 in I2)."

But... but... but... that is the appointment in row 2 itself. I thought you wanted to find an appointment AFTER the appointment in that row.

[johnv]

Hi there,

I got a question, which still has to do with the same Excel-file I am working on (see attachment).
Imagine I have a row with apple, orange, and kiwi (B2:D2).
How do I get the column letter of e.g. kiwi (B4), which should be D?
This example only has three words, but in my situation, it could be that the row extends until after AA and beyond.

Thank you in advance!

[HansV]

For example:

=TEXTBEFORE(ADDRESS(1, XMATCH(B4, $2:$2), 2), "$")