Create worksheet

[Beverley2185]

Hi I wonder is someone can help me I starting dog grooming and need toncreate a daily worksheet for every day included in that sheet I need to enter lots of info then I would like to create monthly worksheet with all dogs booked and if I click on that months worksheet I want to see separate weekly and daily work sheet I have no clue about speed sheet

[#2]

@Beverley2185 

You may keep all your records in one sheet and based on it create PivotTable which shows you monthly, weekly, etc summary. Of filtered table(s) if you are not going to aggregate any data.

[#3]

@SergeiBaklan 

Hi thank you for info but I have no idea what pivot table is or how to create

[#4]

Can I please do example just so you know what I mean
Monday 26th Feb
Time..... dog name and breed....style...cost....comb att....paws...teeth... ect
All that on one line .so I need
7am
8am
9am
Ect
So I need this daily and also weekly and monthly veiw

[#5]

@Beverley2185 

I started tinkering with this problem before you had responded to Sergei. With that in mind, see what I had worked up below and in the attached workbook. Note: The workbook requires Excel 365 or Excel for the web. This is part of what I expect you to see:

We need more information from you to give the best ideas and solutions:

1. Which spreadsheet are you using? Excel 365 for Windows? Excel 365 for Mac? Excel for the web? An older version of Excel?
2. Please list the types of data that might be captured for each visit. E.g., start date-time, duration, owner, dog name, breed, birthdate (approximate; so spreadsheets can calculate age), vaccinations, notes on condition, behavior, types of grooming work to be performed (wash, cut…), fee, owner address, phone, email…
3. Do you need this workbook to help you in scheduling? (An Excel form might be used to capture information and verify available times.)
4. Will anyone else besides you use this workbook? (The reason for this question is that VBA code (e.g., macros) might be useful in some solutions. But if others can also edit the workbook, they could introduce malware.)

As Sergei indirectly indicated, you do not need a "worksheet for every day", but I would expect at least four worksheets (eventually): Clients (owner and dog Info), Visits (containing all scheduled grooming work, past and future), Daily, and Weekly, where the last two draw information from the Visits sheet.  Monthly and Annual sheets might be useful for summaries.  (Pivot tables are better at summarization, but you might not need that level of flexibility.)

Excel tables will be a good way of storing and referring to information. That's a more advanced technique than just storing data in ranges of cells, and navigating and maintaining a table is a little different, but it will likely be worthwhile. (E.g., pressing Tab while in the last cell of a table row navigates to the first cell of the next table row.) Using Excel tables allows you to write more meaningful formulas; e.g., a formula calculating the dog's age that starts with

=LET( when, MIN( [@Deceased], TODAY() ),

rather than

=LET( when, MIN( N3, TODAY() ),

@SergeiBaklan, could you supply the correct syntax for a data validation formula that FILTERs from data in an Excel table row, plus a separate Excel table (tblVisits)?  My attempted formulas for column [Dog] (such as below) have not worked.  I want to filter to just the Dog names that apply to the [Owner Name] selected in the column just to its left.  (Another criterion to be added later.)

=FILTER( tblDogs[Dog], (tblDogs[Owner Name] = tblVisits@[Owner Name]) )

[#6]

This is brilliant thank you on daily one I want to be able to print one every day with time all way down amd also do I need to manaly put in the day and date as I want to make book dogs in for the next visit or is it possible for me to display on paper and take screenshot of what I'm looking for to send to u

[#7 · Accepted solution]

Ho it's exel for windows

[#8]

@SnowMan55 

Perhaps we may combine all togeteher

=LET(
    tbl_rows, FILTER(
        tblVisits,
        (tblVisits[Start Date-time] >= $C$1) *
        (tblVisits[Start Date-time] < ($C$1 + 1)) *
        (tblVisits[Canceled] = 0),
        NA()
    ),
    desired_array, SORT(
        CHOOSECOLS(tbl_rows, 2, 3, 4, 5, 6, 8, 9, 11),
        1
    ),
    getDog, LAMBDA(visit,
        LET(
        dog, XLOOKUP(
            INDEX(visit, 1, 2) & INDEX(visit, 1, 3),
            tblDogs[Owner Name] & tblDogs[Dog],
            CHOOSECOLS(tblDogs[[Breed]:[Other Notes]], 1,3,6,7,8 ),
            ""),
        dogClean, SUBSTITUTE(dog, "", ""),
        HSTACK( visit, dogClean )
        )
    ),
    REDUCE(
        {"Start","Owner Name","Dog","Wash","Cut","Exp. Dur'n","Exp. Fee","Visit Notes","Breed","Age","Phys. Cond'n","Behavior","Other Notes"},
        BYROW(desired_array, LAMBDA(v, LAMBDA(getDog(v))) ),
        LAMBDA(a,v, VSTACK(a,v()))
    )

)

see second Daily sheet.

[#9]

I have hand written what I need I have taken photo of it but how do I attach it to this ?

[#10]

@Beverley2185 

If you mean to attach the file and you don't have this option

put the file on OneDrive, or Google Drive, whatever, share it with everyone and post the link on the file.

If to share screenshot, copy the image (Ctrl+C), click on this icon editing the post

click on grey are in opened window, paste image by Ctrl+V, Ok.

[#11]

https://photos.app.goo.gl/tzS3Vcy6gpjNEvGY6

[#12]

@SergeiBaklan 

I have sent link

[#13]

@Beverley2185 

Sorry, I could help with this or that formula but not with design of the project from scratch. Not enough time. Perhaps someone else could jump in.

[#14]

No problem I think I have designed it all in one sheet I done it in date order example on 26th Feb I wrote all info for that date then below I have 27th Feb and all info but if there away I can hide the info so that it just shows when I double click on the date as it will save me having to scroll down