Posts by Natalie

  • Lookup Function And Pivot Table

    Re: Lookup Function And Pivot Table


    Quote from shg

    That's not correct. The last argument to VLOOKUP specifies (if non-zero or True) a range lookup (find largest value <= lookup value), or (if 0 or False) requires an exact match.


    With range lookup False, the function will return #NA! if there is no exact match.



    sorry that didnt help me i may be a bit ignorant here.....you need to spell out what you mean


    thanks

  • Lookup Function And Pivot Table

    Re: Lookup Function And Pivot Table





    that didnt help it returns back value and n/a# error i need it return back text


    thanks

  • Lookup Function With Pivot Table

    Re: Lookup Function With Pivot Table


    I've tried this formula but it comes up with #VALUE error any suggestion on my formula?


    Your help would be great!


    =GETPIVOTDATA('TOP 6 BEST SELLERS'!B6,'Myer store codes'!B5:C73)


    Thanks
    again
    Natalie

  • Lookup Function And Pivot Table

    hi,


    Sorry I think I already pasted this in the wrong forum apologies! Ive looked at past threads but cant seem to get my finger around it.....any idea.....thanks in advanced!


    trying to do a lookup function with a pivot table. the data below is stored in a different wrk book to that of the pivot.


    I have codes in the pivot which i want to return name and code (as below) once lookup function is used.


    this is what i tried: lookup('[store numbers_final.xls]Sheet1'!$B$60:$C$79,'[store numbers_final.xls]Sheet1'!$C$60:$C$79,m7)


    (m7-Cell where i want the answer to my formula placed.)


    ive also tried:


    =VLOOKUP(B7,'[store numbers_final.xls]Sheet1'!$B$60:$C$79,'[store numbers_final.xls]Sheet1'!$C$60:$C$79,B7)


    =LOOKUP(B9,'[Myer store numbers_final.xls]Sheet1'!$C$60:$C$79,'[Myer store numbers_final.xls]Sheet1'!$B$60:$C$79)


    none of these work the last one above gave mw #N/A error


    Look up table:


    Column B (in seperate workbook)
    text to return in column next to pivot table: (or is it possible to return into a column inserted into the pivot?)
    Parramatta-202
    Penrith-222
    Perth City-41
    Ringwood-6
    Roselands-209
    Southland-7
    Sydney City-219
    Toowoomba-65
    Tuggeranong-239
    Wagga-259
    Warringah Mall-207
    Wollongong-237
    Frankston-12
    Geelong-14
    Ballarat-15
    Bendigo-16
    Logan-55
    Cannington-42
    Albury-17
    Belconnen-631


    Column C
    202
    222
    41
    6
    209
    7
    219
    65
    239
    259
    207
    237
    12
    14
    15
    16
    55
    42
    17
    631




    many thanks
    natalie

  • Lookup Function With Pivot Table

    hi,


    trying to do a lookup function with a pivot table. the data below is stored in a different wrk book to that of the pivot.


    I have codes in the pivot which i want to return name and code (as below) once lookup function is used.


    this is what i tried: lookup('[store numbers_final.xls]Sheet1'!$B$60:$C$79,'[store numbers_final.xls]Sheet1'!$C$60:$C$79,m7)


    (m7-Cell where i want the answer to my formula placed.)


    Look up table:


    Column B (in seperate workbook)
    text to return in column next to pivot table: (or is it possible to return into a column inserted into the pivot?)
    Parramatta-202
    Penrith-222
    Perth City-41
    Ringwood-6
    Roselands-209
    Southland-7
    Sydney City-219
    Toowoomba-65
    Tuggeranong-239
    Wagga-259
    Warringah Mall-207
    Wollongong-237
    Frankston-12
    Geelong-14
    Ballarat-15
    Bendigo-16
    Logan-55
    Cannington-42
    Albury-17
    Belconnen-631


    Column C
    202
    222
    41
    6
    209
    7
    219
    65
    239
    259
    207
    237
    12
    14
    15
    16
    55
    42
    17
    631




    many thanks
    natalie

  • Adding Data With Formula To Table

    hi,


    I have existing tables in ms access.


    I would like to add a column to each of these tables with the following formula in it: =IF(H:H<10,"low stock",IF(H:H>=10,"sufficient stock"))
    how can I do this without using VBA?


    Many thanks
    Natalie

  • Pivot Table

    when creating pivot tables in access can they actually be used in access (ie use drown doowns to chnage criteria) or does access always take you to excel when you open the form? (thats what happens when i open the form from access.


    Thanks Heaps
    Natalie

  • Solve 424 Run Time Error

    Hi,


    I am creating a graph for an ms access data page and once i hit finish to complete the chart i get a 424 run time error - object required.


    Is there any way to fix this besides VBA- dont know how to use VBA.


    Many thanks
    Natalie

  • Sum Function Error With Auto Filter

    Hi
    when using ato filer and I select an item id like to look I try to sum up the total at the bottom but it doesnt give me the correct answer.


    So now i am manually soring and going through the data to get the correct totals.


    Is there any other way around this


    Thanks
    Natalie

  • Bullet Points

    Re: Inserting Paragraph Bullet Points


    that didnt work i typed in =Bullet & "my sentence" it gave me a response og #NAME


    thanks

  • Bullet Points

    Hi,


    I'm constructing a basic report in excel.


    I'd like to add a bullet point for each paragrah, however, excel doesnt seem to have the option Format/paragraph like in MS Word.


    Help would be great
    Thanks.
    Natalie

  • Lookup Function Error

    Re: Lookup Function Error


    Quote from royUK

    I downloaded the workbook, did a simple sort on the lookup_table sheet and all results were then correct


    RoyUK, Thanks for your tip sorting the lookup table rather then the columns where the data was going fixed my problem.


    Thanks for your help!
    Also, Thanks to all others who took the time to help- I really appreciate it.

  • Lookup Function Error

    Re: Lookup Function Error


    that works but only for one cell when i drag the formula down it still returns the wrong text. It need one formula that will return the correct text for information that fills 65,000 rows in excel.


    thanks

  • Lookup Function Error

    Re: Lookup Function Error


    That worked for the one cell but i need a formula which i can copy and apply to the whole column as the text is contained in all 65,000 rows of excel.


    Thanks