+ Reply to Thread
Results 1 to 12 of 12

QR code/ Time/ Date / location over two sheets

  1. 03-09-2022, 03:42 AM #1
    SqueakySneaker
    SqueakySneaker is offline
    Registered User
    Join Date
    03-07-2022
    Location
    Australia
    MS-Off Ver
    Office365
    Posts
    7

    Cool QR code/ Time/ Date / location over two sheets

    Hi Brains Trust. First post to this amazing forum.

    I’m needing some help to compare Sheet 1 to Sheet 2.

    Background of the project first.
    I have 48 sites that need to be attended to and checked, some daily some not.
    Each day I would like to check Sheet 2 with an update of the sites that have been checked in with a QR code.

    EG: A person scans a QR code that Checks them in with a GPS location / Time/date on sheet 1 (columns C, D, G).
    I would like to make this show up in sheet 2 saying that site has been attended to.

    I think the best way would be to Compare (Sheet 1 - C, D, G) with (Sheet 2 - A, D, E)
    I have to compare multiple columns due to the fact that there is sometimes two of the same in Sheet 2, D.

    I am currently doing this manually as this is well above my brain capacity, so any help would be much appreciated.

    BONUS: If the cell on sheet 2 could change to green for check in and red for no check in is an added bonus. Or perhaps a way of expressing this in Sheet 2 F with CHECKED IN and NO.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-09-2022, 05:42 AM #2
    AskMeAboutExcel
    AskMeAboutExcel is offline
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: QR code/ Time/ Date / location over two sheets

    Assume you want this to go into column F in Sheet 2?

    If so, suggest putting the date you are looking for into cell F1
    Then put following formula in cell F2 and copy down:
    Please Login or Register  to view this content.
    You can then add a Conditional Format to Column F so that it highlights based on the value returned
    <<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts
    Reply With QuoteRegister To Reply
  3. 03-09-2022, 07:04 PM #3
    SqueakySneaker
    SqueakySneaker is offline
    Registered User
    Join Date
    03-07-2022
    Location
    Australia
    MS-Off Ver
    Office365
    Posts
    7

    Re: QR code/ Time/ Date / location over two sheets

    Thanks AskMeAboutExcell, It Seems to work for some of the check ins but not all. I think it also needs to check Sheet 2 A column. These are different from the actual suburb Name and linked to the QR code. Is this possible??
    Reply With QuoteRegister To Reply
  4. 03-09-2022, 09:12 PM #4
    AskMeAboutExcel
    AskMeAboutExcel is offline
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: QR code/ Time/ Date / location over two sheets

    It depends what data is returned by the QR code. With the sample provided, there's not enough information to understand how to match the QR data with Sheet 2 A column.

    If the 'Electorate' is included within the text returned by the QR location data then sure, it's just another condition to add to the COUNTIFS formula
    Reply With QuoteRegister To Reply
  5. 03-09-2022, 09:58 PM #5
    SqueakySneaker
    SqueakySneaker is offline
    Registered User
    Join Date
    03-07-2022
    Location
    Australia
    MS-Off Ver
    Office365
    Posts
    7

    Re: QR code/ Time/ Date / location over two sheets

    Yes the "Electorate" is included within the QR code. Could you please help me with how to add to the COUNTIFS please?
    Reply With QuoteRegister To Reply
  6. 03-09-2022, 10:57 PM #6
    AskMeAboutExcel
    AskMeAboutExcel is offline
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: QR code/ Time/ Date / location over two sheets

    Assuming the electorate is shown in column H of Sheet 1 then amend cell F2 on Sheet 2 to the following and copy down:

    Please Login or Register  to view this content.
    Last edited by AskMeAboutExcel; 03-09-2022 at 10:58 PM. Reason: clarity
    Reply With QuoteRegister To Reply
  7. 03-09-2022, 11:32 PM #7
    SqueakySneaker
    SqueakySneaker is offline
    Registered User
    Join Date
    03-07-2022
    Location
    Australia
    MS-Off Ver
    Office365
    Posts
    7

    Re: QR code/ Time/ Date / location over two sheets

    The Electorate information is held in "F" on Sheet 1. Do I just change the "H" in the formula above to "F"? I just tried that with no success. Sorry, but I am a complete novice at this.
    Reply With QuoteRegister To Reply
  8. 03-09-2022, 11:40 PM #8
    AskMeAboutExcel
    AskMeAboutExcel is offline
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: QR code/ Time/ Date / location over two sheets

    From your example shown I understand that the data maps as following:

    - Sheet 1 Column F match Sheet 2 Column D
    - Sheet 1 Column E matches Sheet 2 Column E
    - Sheet 1 Column H (assuming the QR Data here includes the Electorate in the text) matches Sheet 2 Column A
    - Sheet 1 Column C matches Sheet 2 date in cell F1

    The formula follows this logic.

    If any of that is wrong or has changed, we can help adjust the formula accordingly.

    There are other reasons that it may not work e.g. if the data doesn't exactly match, but we would maybe need to see more real world data to troubleshoot
    Reply With QuoteRegister To Reply
  9. 03-10-2022, 01:14 AM #9
    SqueakySneaker
    SqueakySneaker is offline
    Registered User
    Join Date
    03-07-2022
    Location
    Australia
    MS-Off Ver
    Office365
    Posts
    7

    Re: QR code/ Time/ Date / location over two sheets

    - Sheet 1 Column H (assuming the QR Data here includes the Electorate in the text) matches Sheet 2 Column A - NO
    I need Sheet 1 Column F (named suburbs in my sample) to Match Sheet 2 Column A.
    Reply With QuoteRegister To Reply
  10. 03-10-2022, 01:52 AM #10
    AskMeAboutExcel
    AskMeAboutExcel is offline
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: QR code/ Time/ Date / location over two sheets

    I think the data in your sample spreadsheet is a little 'mix and match' - hence the confusion

    In Column F of Sheet 1, some of the data matches Column A from Sheet 2, some of the data matches Column D from Sheet 2. Meanwhile some of the data from Column D in Sheet 1 also matches some of the Column D data in Sheet 2.

    Can certainly be resolved with some fairly simple formulas, but need more guidance on the intended logic of how you would intend to match these. A good starter point is to do this manually for a number of lines and explain the approach.
    Reply With QuoteRegister To Reply
  11. 03-10-2022, 03:07 AM #11
    SqueakySneaker
    SqueakySneaker is offline
    Registered User
    Join Date
    03-07-2022
    Location
    Australia
    MS-Off Ver
    Office365
    Posts
    7

    Re: QR code/ Time/ Date / location over two sheets

    I believe this is part of my problem also, because some of Sheet 2 (Col. A) just reads QLD. This is due to some Suburbs having two locations.

    Possibly I could just get away with having the address columns match up. Sheet 1 Column E to Match Sheet 2 Column E.
    Then I can just search by date and use conditional formatting to generate the No/Attended in Sheet 2 Column F.
    I think that I maybe trying to do too much and sometime simple is best. Unfortunately I am totally new to Excel so even the simple things seem hard at the moment.
    Reply With QuoteRegister To Reply
  12. 03-10-2022, 03:46 AM #12
    AskMeAboutExcel
    AskMeAboutExcel is offline
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: QR code/ Time/ Date / location over two sheets

    What about this approach - will count if:

    - Sht1 Col E = Sht2 Col E AND
    - Sht1 Col C = Sht2 Date

    and if either of these three scenarios are true
    - Sht1 Col F= Sht2 Col A
    - Sht1 Col F= Sht2 Col D
    - Sht1 Col D= Sht2 Col D


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Google Sheets: Calculate End Date & Time from Start Date & Time for given Duration
    By vigneshslm in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 02-13-2021, 09:54 PM
  2. [SOLVED] Unique counter based on the location code and time of an audit
    By quentin5000 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-02-2018, 04:00 AM
  3. Microsoft Date and Time Picker Control - Keeps Moving Location
    By iamspardacus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-16-2015, 08:53 PM
  4. [SOLVED] Loop & Match considering location and period of date&time
    By tudorr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2013, 06:12 AM
  5. [SOLVED] Saving temporary text file to particular location with the name of current Date and Time
    By muralidaran in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-06-2012, 10:46 AM
  6. Saving in a second location with date/time stamp
    By lxndeb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-29-2009, 01:42 PM
  7. Date / Time Picker V6.0 - Control moves location on userform!
    By Mark in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1