+ Reply to Thread
Results 1 to 19 of 19

IF function - display cell from another worksheet

  1. #1
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    IF function - display cell from another worksheet

    Hello, please help!
    I have an IF function that I need to link to a cell in another worksheet.
    The formula I am using is:

    =IF(D30="Lg Doc Copies","Pricing!B7")

    D30 in the one worksheet says Lg Doc Copies (yes I am sure :P)
    D30 just stays blank, but I want it to display the content of B7 in the Pricing worksheet.

    Much thanks!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: IF function - display cell from another worksheet

    Remove the quotation marks from around "Pricing!B7". Where are you putting this formula? It will not change D30, but should change the cell in which the formula is placed. Also what should happen if D30 does not equal "Lg Doc Copies?". That part is missing from your formula.

    The syntax for the =IF is =IF(Criteria, Value if True, Value if False)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    Yes I did try removing the quotation marks. It still did not work. The formula is going in A30.
    In short, I want this to happen:
    If B30 says Lg Doc Copies, I want A30 to display the cell contents of B7 in the Pricing worksheet.

    This is the FULL formula I am using (note: it worked perfectly before trying to reference another worksheet):

    =IF($B30="","", IF($B30="Lg Doc Copies","Pricing!B7",IF($B30="8x11 BW","0.20",IF(B30="11x17 BW",".30",IF($B30="8x11 Color","1.00",IF($B30="11x17 Color","2.00",IF($B30="12x18 BW",".40",IF($B30="12x18 Color","2.50",IF($B30="Line Color Plots","1.50",IF($B30="Medium Color Plots","3.00",IF($B30="Full Color Plots","5.00", IF($B30="BW HP Plots", "1.50", IF($B30="Lg Scans - 1-5", "5.00", IF($B30="Lg Scans - 6+", "5.00", IF($B30="Sm Scans", ".50", IF($B30="Laminate", "4.00", IF($B30="Blank Cardstock", ".35", IF($B30="BW Cardstock", ".50", IF($B30="Color Cardstock", "1.50", IF($B30="8x11 Clear Cover", "1.50", IF($B30="Cerlox - 1/4, 1/2, 5/16", "2.00", IF($B30="Cerlox - 5/8, 3/4", "2.75", IF($B30="Cerlox - 1", "3.00", IF($B30="Cerlox - 1 1/4, 1 1/2", "3.50", IF($B30="Cerlox - 1 3/4, 2", "4.00", IF($B30="Duracopy", "2.50", IF($B30="Mylar BW", "3.50", IF($B30="Mylar Color", "5.00", IF($B30="Canvas", "8.00", IF($B30="Vellum", "1.50"))))))))))))))))))))))))))))))

  4. #4
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    bumpbumpbump

  5. #5
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    jaideb:

    That nested if formula is quite long. I would use a vlookup table to get the data you wanted. I don't know what the rest of your worksheet looks like since it wasn't posted, but I made a sample worksheet with 3 tabs (Main Sheet, Lookup Table and Pricing). In the Lookup Table tab I added all of your possible choices (Lg Doc Copies through Vellum) and their corresponding values (.20 to 1.50). I took all that from your If statement. Next, I used that in Cell B30 of the Main Sheet tab to make a dropdown. Once a value is selected from the dropdown, the formula in cell A30 uses a vlookup to pull the right value. As for Cell B7 in pricing tab, I simply made the lookup table for Lg Doc Copies point to the Pricing!B7 cell to pull it's value.

    This way, you can more easily update your lookup table if details change, and don't have to worry about updating that if statement.

    Does that help?

    Josh
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    That is awesome Josh thank you! That formula is great for the basic pricing!

    However, the main issue that I am trying to address is we have many customers with different pricing. I am trying to link to the worksheet with specific customer pricing. I am attaching my basic spreadsheet, maybe you can see a way for me to do this effectively.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    jaideb:

    I took your spreadsheet and changed a couple of things...I added to the data tab 4 customers and then laid in some prices. I then made 3 named ranges to pick up Customers, Item_Type (vellum, Sm Scans, etc.) and Customer_Pricing_Data. Next I added in cell B3 a drop down to pick up the Customers (from the named range). Next, in the Price Per. box, I used an Index/Match formula to look at 1) the Customer chosen in B3, and 2) the Item in the Description column. It then looks at the data table to find the correct intersection of customer and item and pulls the price. Now you can update your Data table and your template will be good to go. Make sure when adding customers to the data tab to insert new columns from within the existing range (likewise for adding any new Description items, such as Heavy Vellum). This will ensure that your named ranges update and your formulas will continue to work.

    How does this spreadsheet look?

    Thanks!
    Josh
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    It looks good, however I just want to confirm something, is there absolutely no way to reference the pricing table rather than the data? We have over 700 customers that I need to reference (reduced the table for confidentiality and easy reading) and I really don't want to retype all the customers if I can avoid it :P

    Thanks for all of your help!

  9. #9
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    jaideb:

    Yes, we can pull from wherever we want. I think I have what you are looking for now. If the input is "Lg Doc Copies" it will look at the Pricing tab to pull the data from the Customer entered in cell B3 of the Template tab. If it is anything else, it will pull the pricing information from the Data tab...if that is not right...I am not sure I understand what exactly you are looking for.

    Thanks!
    Josh
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    Yes thank you that is exactly what I need!! Now, one more question (hopefully :P), when I try to change the customer names to the proper names, the price per. does not show up any longer :S I cant seem to find where the names are referenced in the formulas :S What process do I need to do to change the customer names/add customers to the list?

    Thanks again!

  11. #11
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    I am glad it is working.

    You will need to change/add the customer names in two places. They need to be updated on both the Data tab (currently B1 through E1) and the Pricing Tab (currently A3 through A6). They need to be updated in both places and the names need to appear and be spelled the same on both lists. You can look at Formulas>Name Manager to see the named ranges that exist on the worksheet. Remember that when adding names to either named range (Customers or Customers_Pricing_Tab) you need to add them within the range to make sure the range updates to include the new customers you add.

    Thanks!
    Josh

    PS: Make sure you check that your cell B3 is updated on your Template tab so it is picking up the new (real) customer names. Otherwise it might still be pointing to company name 2, etc.
    Last edited by jjhayes; 09-11-2014 at 06:59 PM.

  12. #12
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    So I will still need to write out all the customers in the data tab then? I would like to find a way that I can reference ONLY the prices from the Pricing worksheet to show up. I already have the whole Pricing worksheet completed (it was an existing spreadsheet and I copied and pasted it into the workbook). Basically I am trying to avoid rewriting all 700 of our customers and their prices when I already have an entire spreadsheet completed with all their information.

    Maybe you know a more efficient way of doing this?
    Thank you !

  13. #13
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    I have attached the spreadsheet with an explanation of what my goal is at the end when the spreadsheet is done. Is it possible?
    jaideb_sample_3.xlsx

  14. #14
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    jaideb:

    In this attachment, I changed it a bit...it only pulls customer specific price for "Lg Doc Copies" from the Pricing tab. Any other item (Vellum, etc.) is a standard price (doesn't matter who the customer is). In this case, it will pull that price (for Vellum, etc.) from the Data tab. This way, you only need to update customers on the Pricing tab.

    Is that right?

    Thanks,
    Josh
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    jaideb:

    I am a little confused on why there is 2 sets of data. The first is from the Data tab that shows Lg Doc Copies through Vellum, and the second is from the Pricing tab (showing customers running down column A and items type such as "Lg Doc (/sf)" through "double sd" on Row 1). I think some of the items are standard prices regardless of customer and those are what is on the Data.

    So, I took those "standard" items and added them to the Customer list (on Pricing Tab). You will see them starting at cell Y1. Then I adjusted the formula on the template tab to pull from just the pricing tab. The data tab would not be needed in this case.

    The customer dropdown on the template now pulls from the 688 customers on column A on the Pricing Tab and the Item Type now pulls from the 50 items in B1 through AY1 on the Pricing Tab. You would just need to update the one pricing tab to keep your template up to date.

    Not sure if this is where you wanted to end up...

    If yes, then ignore jaideb_sample_4.xlsx

    Thanks,
    Josh
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    Awesome okay, so I could delete the data tab then because you have everything set in the pricing tab? Or is there anything I need to keep from that?

  17. #17
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    I don't think you need the data tab anymore...I kept it just in case. Take a look at the Pricing tab to see the pricing by customer. If it has everything you need, then you can delete the data tab...nothing is referencing to it anymore.

  18. #18
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    Alright! So I used the 4th spreadsheet you sent me. I just have one more question, the formula to link pricing for the Lg Doc Copies is working, however all the other prices such as Lg Scans are only linking to the data spreadsheet rather than the customer pricing spreadsheet. Why is it doing that? How can I change it?

    I really appreciate all of your help! You are seriously a lifesaver!

    Jaide

  19. #19
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    You are using the 4th spreadsheet? Did you see the 5th version in post #15...the 5th version does not reference back to the data tab at all. I think "jaideb_sample_5" should do what you want.

  20. #20
    Registered User
    Join Date
    09-11-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    12

    Re: IF function - display cell from another worksheet

    Yes I saw the 5th spreadsheet the only problem was that it didn't reference the "standard" prices for if they are not inputted into the customer pricing. I think the easiest solution for me would be to not be lazy and use the 5th spreadsheet and just input all the standard pricing into each customer's price list rather than trying to reference separate cells if they are blank etc. Will take a fair bit of time but no harm done, its been slow at work anyhow.

    Thank you so much for all of your help, I really appreciate it! Sorry for being such a pain in the *** :P

  21. #21
    Forum Contributor
    Join Date
    06-18-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    142

    Re: IF function - display cell from another worksheet

    I was happy to help, and I understand what you mean...but if they are standard pricings, can't you literally copy and paste them down entire columns in the pricing tab worksheet? Not a veteran of this forum, but if this one is solved, maybe you can mark it as such using the Thread tools. Good luck to you!

    Josh

+ 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. display multiple cell values in one cell on a different worksheet
    By sheldon8 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-29-2012, 12:58 PM
  2. Replies: 2
    Last Post: 10-23-2012, 05:30 PM
  3. How to get current worksheet name to display in a cell?
    By xfixiate in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-22-2006, 04:30 AM
  4. [SOLVED] Can a cell be linked to display the name of the worksheet tab?
    By Blue Jay One in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-22-2006, 11:15 PM
  5. trying to find how to display worksheet name in cell
    By Yvonne Barber in forum Excel General
    Replies: 1
    Last Post: 09-15-2005, 04:05 PM

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