Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Linking cells and fetching them to another worksheet

  1. #1

    Question Linking cells and fetching them to another worksheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi!

    I'm new to this forum so please, excuse me if I'll be making silly comments. I'm not an Excel power user but rather a very average one.
    Anyway, I got to an idea I'd like to fulfil.

    I have an Excell file with two worksheets (database + calc).
    One worksheet serves as a "database". It has multiple collumns with separate values (description + value 1 + value 2 + value 3)
    Another worksheet serves as a callculator of these values. At the moment I'm doing all the job manualy (copy / paste) - from the source to a callculator so it's able to calculate values. I need to copy / paste description and value 1 and value 2, etc...
    And it's very time consuming.

    I'd like to automatize the process.
    I was able to make a "dropdown menu" in the callculator worksheet, of the first column - description values from the "database" worksheet.
    But the hardest part it just yet to come...

    I need to (somehow) link the cells together (desc. + 1 + 2 + 3) so the callculator will know how to properly fetch all the rest data values.
    Example can be seen in a specimen file attached.

    I hope I made it clear enough.
    Thank you very much!


    specimen_calc.xlsx

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi there,

    Use this formula in B3:E9

    =IF(ISBLANK($A3),0,VLOOKUP($A3,database!$A$1:$E$22,MATCH(B$2,database!$A$1:$E$1,FALSE),FALSE))

    Hope it helps,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    well, drop box aside, you can use a simple vlookup to fetch the data. just put the following in cell B3 on the calc page:
    =VLOOKUP(A3,database!$A$2:database!$E$22,2,FALSE)

    this line is c3. Note the 2nd to the last location was 2, now is a 3. this refers to the column of the database.
    =VLOOKUP(A3,database!$A$2:database!$E$22,3,FALSE)

    Hope this helps.

    Haha Ken posted while I was still typing.
    I like his better, it has error checking on it.

  4. #4
    whoa thanks for such a swift response!

    well... after trials 'n' errors i have to addmit that i have troubles.
    sure, at first i just tried to paste the formula into my specimen worksheet (i though i'd later modify it for the real "production" one...). silly me. it just ain't that easy

    so, pasting doesn't help.
    now i'm trying to figure it out what does this formula means.
    i see you both included the columns E which already has it's own formula and reall shouldn't be included. it's my fault i left it in the specimen example.

    if it's not too much bother, i'd kindly ask you to look again at a new specimen.
    please note, that in the "calc" sheet, column E has it's own forumula and should not be fetched. (i removed column E from the database).

    damn thing just doesn't want to work
    thanks a lot!
    Attached Files Attached Files

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Alrighty, give this a go:

    =IF(ISBLANK($A3),0,VLOOKUP($A3,database!$A$1:$D$22,MATCH(B$2,database!$A$1:$D$1,FALSE),FALSE))

    Basically I just updated the database range to shorten the table to D rather than E.

    The crux of this is that we're using a VLOOKUP to look up the value of column A in your table of data, then return the appropriate column. Where Simi put in a hardcoded column to return for each, I used a Match statement to look up what column needed to be returned. After that I just wrapped an error handler around it to make sure it never returned #N/A for you.

    Have a read through VLOOKUP in the help. It's an AWESOME function which, if you master it, will open up worlds for you in Excel.

    If you need any more help making this work, don't hesitate to post back.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    pffff... thanks Ken. it's obvious you're a master and i'm a n00b.
    anyway - let me get one thing clear - putting this formula in my specimen on your side makes it work?

    it doesn't on my end. i get a formula error. and just to let you know - i tried this modification, to exclude the D table earlier by myself but i didn't work.
    there is one thing i don't really understand though; is it ok that the function looks up from the A1 instead of A2 when the relevant data starts?

    thank you!

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    it's obvious you're a master and i'm a n00b.
    LOL! But you're trying to learn, which is more than a lot of people out there.

    So yes, it does work on my side. I've attached a copy here with it working. Can you let me know if you still see a formula error?

    I'm also curious, what version of Excel are you using?
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    ha! it's a typo problem. see the screenshot! in your working version there are ";" instead of "," in the example code given. modifying the formula works i'm so happy now. i'm using office 2010 - it may have anything to do with it? THANKS MAN! sure, i'm bookmarking this community for future usage
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	16-2-2012 0-38-05.jpg 
Views:	14 
Size:	4.5 KB 
ID:	467  
    Last edited by ozi; 2012-02-16 at 12:44 AM.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hah! You're not going to believe this, but that's not a typo. In fact, I copied my formula from the web into your sheet and uploaded it to you. I actually use commas.

    Are you in Europe, or somewhere else? It's actually a regional setting that makes the change. Whatever country you're in, it uses semi-colons as a delimiter. North american standards (at a minimum) use commas.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    ah... all well at the end.
    europe here, yup.

    thanks again
    i'm off to (trying) to manipulate my production workbook.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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