+ Reply to Thread
Results 1 to 17 of 17

Counting and entering data as based on a another cells value

  1. #1
    Registered User
    Join Date
    12-04-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Counting and entering data as based on a another cells value

    Hi All,

    I hope someone will be able to help with this.

    TAB1, called Travel Log, contains Date, Trip #, Section #, and Passenger #.

    I want to take the Passenger # total and sequentially number rows in TAB2, called Passenger Names, while also pulling the Date, Trip #, and Section #.

    Each trip has sections, each section has different passengers. I need to track the passenger names for each section.

    I would like it to operation as follows:
    When a passenger # is entered in TAB1 row 1; the Date, Trip #, Section #, and Passenger #, is entered in TAB2. If there are 10 passengers, there will be 10 rows entered.
    When a passenger # is entered in TAB1 row 2; the Date, Trip #, Section #, and Passenger #, is entered in TAB2. If there are 3 passengers, there will be 3 rows entered starting on row 11.

    I have included an example in TAB2 of my attachment of what the the finished product should look like as based on the data entered in TAB1.

    Thank you all so much for your help!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Counting and entering data as based on a another cells value

    In A2 and copied across and Down to Column C
    Please Login or Register  to view this content.
    In D2 then down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,398

    Re: Counting and entering data as based on a another cells value

    Please update your profile - 10 is not a valid Office version (it's your Windows version). Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Counting and entering data as based on a another cells value

    An alternative. I created a record number in the raw data:
    =IF(B2="","",SUM(1+N(A1)))

    I created two Named Ranges (Date and Record) to adjust the formula ranges automatically. CTRL-F3 to view/edit. One of them is:

    ='Travel Log'!$E$2:INDEX('Travel Log'!$E:$E,MAX('Travel Log'!$A$2:$A$1000)+1)

    the other, similar. Then, in the results sheet, in A2, I replicated the record number the required number of times:

    =IFERROR(INDEX(Record,MATCH(0,INDEX(--(COUNTIF($A$1:A1,Record)=IF(Date=0,1,Date)),0),0)),"")

    and finally, to return the data, in B2, copied across and down:

    =IFERROR(INDEX('Travel Log'!B:B,$A2+1),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Counting and entering data as based on a another cells value

    Dear Glenn Kennedy

    Column E does not tally with expected result.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Counting and entering data as based on a another cells value

    I shouldn't use Excel before coffee has been taken !!

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Counting and entering data as based on a another cells value

    So,thanks to Kvsrinivasamurthy for spotting my error. If you are interested in the alternative solution, this fixes my error in column E. In E2, copied down:

    =IF(A2="","",IF(INDEX('Travel Log'!E:E,$A2+1)=0,0,COUNTIF(A$2:A2,A2)))
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-04-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting and entering data as based on a another cells value

    Hi Glenn Kennedy. Thank you so much! This works just as I needed.
    I cut and pasted these formulas into my working spreadsheets thinking that I would be able to edit it slightly and it would all work.
    Unfortunately, I cannot figure out the last few tweaks. If you could please take a look see what I forgot to change. That would be awesome. This is a redacted version of my working sheet. Thanks again.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Counting and entering data as based on a another cells value

    Have you seen my previous post

  10. #10
    Registered User
    Join Date
    12-04-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting and entering data as based on a another cells value

    Quote Originally Posted by kvsrinivasamurthy View Post
    Have you seen my previous post
    Hi kvsrinivasamurthy. Thank you for all of your help. Looks great. I did run into 2 issues with this one.

    #1 = When I delete all of the data from the "travel log" tab and start recording. If there is only 1 row data, the passenger # does not populate correctly in the "passenger names" tab.
    #2 = When I get beyond row 8 of entries in the "travel log" tab, the "passenger names" tab no longer populates.

    Thanks you so much for your help!

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Counting and entering data as based on a another cells value

    Hi thanks fore replying. I missed your ealier comment. I'm not sure what I did... but it'w working now.

    You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae in the second sheet, column A, are enclosed within a pair of { }, these are array formulae.

    These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-04-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting and entering data as based on a another cells value

    Quote Originally Posted by Glenn Kennedy View Post
    Hi thanks fore replying. I missed your ealier comment. I'm not sure what I did... but it'w working now.

    You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae in the second sheet, column A, are enclosed within a pair of { }, these are array formulae.

    These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Thanks for the info. I downloaded and reviewed the corrected spreadsheet. Any idea why the "Passenger Names" tab is not populated beyond Number 11 in Column A. Note, Column A should be populated to Number 19, as per the "Flight Log" tab Column A. Thanks again!

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Counting and entering data as based on a another cells value

    Yep. Dead simple. I'm stupid. I used the wrong formula for the Named Ranges. Hopefully fixed now. Formulae copied down through shaded cells only.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-04-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting and entering data as based on a another cells value

    Quote Originally Posted by Glenn Kennedy View Post
    Yep. Dead simple. I'm stupid. I used the wrong formula for the Named Ranges. Hopefully fixed now. Formulae copied down through shaded cells only.
    Great. Thank you.

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,896

    Re: Counting and entering data as based on a another cells value

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  16. #16
    Registered User
    Join Date
    12-04-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting and entering data as based on a another cells value

    Quote Originally Posted by Glenn Kennedy View Post
    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Hi Glenn,

    Another issue I ran into while testing. Is that the "Passenger Names" Tab only inserts data up to row 50. After this it stops working. Any chance you can look into that? Thanks.

  17. #17
    Registered User
    Join Date
    12-04-2020
    Location
    USA
    MS-Off Ver
    2010
    Posts
    7

    Re: Counting and entering data as based on a another cells value

    Quote Originally Posted by chadnick View Post
    Hi Glenn,

    Another issue I ran into while testing. Is that the "Passenger Names" Tab only inserts data up to row 50. After this it stops working. Any chance you can look into that? Thanks.
    Never mind. Solved it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Entering zero into cells based on start / end dates in another cell
    By ikench in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-05-2017, 12:06 PM
  2. Transferring Data based on entering number in column
    By briancambier in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2014, 06:16 PM
  3. [SOLVED] Help on entering data from one sheet based on multiple criteria
    By Dena in forum Excel General
    Replies: 2
    Last Post: 09-11-2012, 10:51 AM
  4. Entering data into cells
    By petrolh34d in forum Excel General
    Replies: 4
    Last Post: 08-19-2009, 10:43 AM
  5. entering data based on a lookup table
    By rgdwar1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-19-2008, 09:03 AM
  6. Entering in data in specific cells using range protect or locking the cells
    By howard101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2008, 11:05 AM
  7. Data entering into cells based upon date
    By PenguinBacon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-21-2007, 10:23 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1