Linking cells and fetching them to another worksheet

ozi

New member
Joined
Feb 15, 2012
Messages
6
Reaction score
0
Points
0
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!


View attachment specimen_calc.xlsx
 
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,
 
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.
 
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!
 

Attachments

  • specimen_calc_2.xlsx
    26.1 KB · Views: 25
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.
 
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? :D

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!
 
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?
 

Attachments

  • xlgf751.xlsx
    25.6 KB · Views: 26
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 :D 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 :nod:
 

Attachments

  • 16-2-2012 0-38-05.jpg
    16-2-2012 0-38-05.jpg
    4.5 KB · Views: 23
Last edited:
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. :)
 
ah... :) all well at the end.
europe here, yup.

thanks again ;)
i'm off to (trying) to manipulate my production workbook.
 
just to tell you, that everything works reall flawless! thank you agan!
 
Back
Top