Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    CELL DATA IN TABLE (EXCEL XP)

    I WANT TO SEARCH A TABLE AND RECORD THE DATA IN A CERTAIN CELL (IE; COLUMN NAME AND ROW NAME INTERSECTION SPECIFIC) WHAT IS THE FORMULA THAT I WOULD HAVE TO USE. I TRIED CONDITIONAL SUMMING BUT IT DOESN'T DO THE JOB.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    First, would you please turn off the caps lock on your keyboard. Typing in all caps is hard to read and indicates that the typer is shouting.

    Could you upload a workbook that shows what you are trying to do. Your description is not very specific and is hard to understand without something to look at. I think that what you need is the VLOOKUP or HLOOKUP function, but I can't tell for sure from your description and could not tell how to set up the function without knowing what cells the data is located in.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    Your question is too general. Can you post a simple example of what you are looking for?

    If the table is in Excel, there are several Excel functions you can use to return a value from a table of data, based on the arguments used by the Excel function, including:

    =INDEX()
    =VLOOKUP()
    =HLOOKUP()
    =MATCH()

    Look these up in the Help, and also review the "See Also" link in the Help.

    Also, please type in regular case, not upper case.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    Thank you for the response and I will try to be more specific.
    I want to take an amount , salary, from a budget statement of operation (monthly Jan thru Dec)for the month of March and record it in my Monthly Statement of Operation on the salary line for the month. I then derive my variance between the actual salary expense and my budgeted March salary expense. I have the monthly actual to budget financials on one worksheet and have the budgeted below it. this way no matter what the month it is automatically populated with the budget amounts for each month of the year.

    Thanks again,

    Martin

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    Sorry for the cap lock, a habit i got into.

    Thank you for the response and I will try to be more specific.
    I want to take an amount , salary, from a budget statement of operation (monthly Jan thru Dec)for the month of March and record it in my Monthly Statement of Operation on the salary line for the month. I then derive my variance between the actual salary expense and my budgeted March salary expense. I have the monthly actual to budget financials on one worksheet and have the budgeted below it. this way no matter what the month it is automatically populated with the budget amounts for each month of the year.

    Thanks again,

    Martin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    A small matter of Lounge etiquette for the future: Lounge regulars such as Legare Coleman and JohnBF read all posts in a thread, so it is not necessary to post identical explanations and attachments twice. You can indicate in your post that you are replying to both at once. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    Does this formula do what you want in cell D24?

    <pre>=HLOOKUP(F6,B54:M76,15,FALSE)
    </pre>

    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    Although that is a worthy suggestion, I want one formula to note the month on the statement of operations and then depending on the line item, title of the revenue or expense, populate the budget amount column given the budget table below. I would like to copy this formula to each line item and each month of income statement which will be reported accross the worksheet as I demonstrated for Feb statement of operations.

    The HLookup means I will have to change each line item for month.

    Thanks

    Marty

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    If you change my original formula to:

    <pre>=HLOOKUP(F6,$B$54:$M$76,15,FALSE)
    </pre>


    Then you can copy the formula from D24 and paste it into N24 and it should work as you want. If you create additional months across the sheet, you could copy the formula to the new months and it should work.

    Because of the way your data is layed out, I do not see any way to create a formula that you can copy D26, D28, etc. and have it work. If you change the labels in column A so that the monthly lables match the budget labels, then you can use a double lookup that can be copied down the column and from month to month. Right now, "Fringe Benefits" does not match "Fringes", and several of the others are also different. In addition, there is an "Overhead" row in the monthly data, but not in the Budget data. If those labels matched exactly, then the following formula could be put in D24 and copied to all of the other cells:

    <pre>=INDEX($A$54:$N$76,MATCH($A24,$A$54:$A$76,0), MATCH(P$6,$A$54:$M$54,0))
    </pre>

    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    I am enclosing the worksheet with the index formula you suggested. I am probably doing something wrong but it is not recording the budgeted cell number for the revenue or expense item from the respective month in the actual statement of operation on the upper half. I am attaching the worksheet. thanks again

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    Your formula had several errors in it. The one in D14 should have read:

    <pre>=INDEX($A$54:$N$76,MATCH($A14,$A$54:$A$76,0), MATCH(F$6,$A$54:$M$54,0))
    </pre>


    I have attached a workbook that shows how it should be.
    Legare Coleman

  12. #12
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: CELL DATA IN TABLE (EXCEL XP)

    As John Luich used to say on SATURDAY NIGHT LIVE: " THAT'S THE TICKET!"

    It certainly looks like what i wanted to happen with out having a cell tie directly into one specific cell. Now the formula does the work.

    Many,Many, thanks

    Sincerely,

    Martin

Posting Permissions

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