+ Reply to Thread
Results 1 to 9 of 9

Summarizing data based "as if" a cell contained different number

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    Orlando
    MS-Off Ver
    O365
    Posts
    24

    Summarizing data based "as if" a cell contained different number

    Current sheet contains labor rates for 4 different cities (city 1, city 2, city 3, city 4). The city is chosen by a drop down list selection and enters value of 1-4 into cell M3 based on city selected. Labor is calculated based on the city selected and total rate, burden, combined is calculated in the worksheet. Summarizing the data for only the selected city is simple.

    I would like to be able to summarize the data for all the cities regardless of the city selected so they can be compared in a table. The selected city will have an extensive summary page, but I would like to be able to summarize the totals of some information from all of the cities for comparison (Rate, Total, combined). Not just the selected city.

    Is it possible to collect this data when the calculations are based on a single cells value? Or can you collect date as if the cell contained a different value at the same time?

    I've attached a brief example sheet, the actual workbook has extensive information and calculations based on the single cell (M3) value for location. I think if I could solve this simple sheet I would be able to figure out how to do the actual workbook.

    Thank you for your time.

    (I posted this on the Mrexcel forum but received no replies)

    https://www.mrexcel.com/board/thread.../#post-5846385
    Attached Files Attached Files
    Last edited by muadebe; 03-04-2022 at 02:58 PM. Reason: Added link

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Summarizing data based "as if" a cell contained different number

    Fill in the summary table manually and explain how you calculate the figures.

    I think the issue is that no-one knows quite what you are trying to achieve.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,926

    Re: Summarizing data based "as if" a cell contained different number

    You could make the first table more resilient by using this formula in H9:

    =INDEX($F$24:$M$28,MATCH($F9,$E$24:$E$28,0),MATCH(VLOOKUP($M$3,$Q$3:$R$6,2,0),$F$22:$M$22,0))

    and this one in I9:

    =INDEX($F$24:$M$28,MATCH($F9,$E$24:$E$28,0),MATCH(VLOOKUP($M$3,$Q$3:$R$6,2,0),$F$22:$M$22,0)+1)

    (The only difference is the +1 near the end of the formula, shown in red). Then you can copy these down. They will give you the same results as the multiple-IF formulae that you currently have, and will still work if the order of the items in column F were to change. You could also link the names of the cities on row 22 directly to the list in column R (e.g. by using =R3 in F22, and =R4 in H22, and so on), so that if you need to change the city names you only need to do this once in column R.

    Although that doesn't directly answer your question, it does show you how you can pick the appropriate rate or burden given the city name, so hopefully that will help you to apply it to the table from row 15 onwards. Shout out if you need any further assistance.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    08-10-2012
    Location
    Orlando
    MS-Off Ver
    O365
    Posts
    24

    Re: Summarizing data based "as if" a cell contained different number

    I think the issue is that no-one knows quite what you are trying to achieve.
    The worksheet is designed to calculate pricing for 4 different locations.
    Selecting a location dumps all the correct pricing information into each worksheet cell so the correct price for that specific location is calculated
    There's a summary sheet that adds everything up and totals the entire workbook for the specific location selected. (Price, total hours per operation, burden, etc.)
    Price is totaled for all the different tabs (each tab is a different part)

    We would like to be able to see what the total price would have been for the other 3 locations next to the actual location which was selected and computed on the sheet. So if City 1 was selected and run through the sheet, cities 2, 3, & 4 total price would show up next to the summary of city 1 so we can compare.

    The only other way I can think of is manually changing the location after completing data entry into the worksheets, and then hard numbering the comparison price. I was hoping this could be done using a formula.

    Hope that helps explain what I'm trying to do. Thanks again for your time.

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

    Re: Summarizing data based "as if" a cell contained different number

    Are you still uisng Excel 2013? Unless you are using O365, many/most of the formulae in thsi sheet are likely to be array formulae.

    If the formulae 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...

    I removed all of your shading to enable you to see exactly where I have changed things. A different colour = a different formula.

    It works on my Excel version... it MIGHT work on yours. We'll see!!
    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

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,318

    Re: Summarizing data based "as if" a cell contained different number

    Administrative Note:

    Welcome to the forum.

    We would love to help you with your query; however, it has been brought to our attention the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly.

    Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    HTH
    Regards, Jeff

  7. #7
    Registered User
    Join Date
    08-10-2012
    Location
    Orlando
    MS-Off Ver
    O365
    Posts
    24

    Re: Summarizing data based "as if" a cell contained different number

    Are you still uisng Excel 2013?
    Thank you for your response. I think this is exactly what I am trying to do (Excel 0365). I will attempt to apply it to the real project next week. I appreciate all your help very much.
    Last edited by jeffreybrown; 03-04-2022 at 03:07 PM. Reason: Please don't use full quotes!

  8. #8
    Registered User
    Join Date
    08-10-2012
    Location
    Orlando
    MS-Off Ver
    O365
    Posts
    24

    Re: Summarizing data based "as if" a cell contained different number

    My apologies. I only mentioned where I had posted it in the original post. I will edit with the direct link.
    Last edited by jeffreybrown; 03-04-2022 at 03:07 PM. Reason: Please don't use full quotes!

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

    Re: Summarizing data based "as if" a cell contained different number

    Before you do ANYTHING else, please update your profile to show O365... Click on "my profile" (top of screen), then "about me" then:
    Attached Images Attached Images

+ 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] Best way to assign a "Bucket" to a "file number" based on data from row
    By BG1983 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-27-2016, 11:23 PM
  2. [SOLVED] if formula needed to populate either "1" or "0" based on number of units in another cell
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-07-2015, 11:24 AM
  3. Replies: 2
    Last Post: 04-20-2014, 11:18 AM
  4. [SOLVED] Splitting a Cell based on "/" and adding new Rows based on Part Number Logic
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-30-2013, 10:11 AM
  5. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM
  6. Change "Parent" cell based on results of multiple "Children" cells
    By ccowman in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-14-2012, 02:33 AM
  7. Replies: 0
    Last Post: 07-17-2006, 09:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1