linking across worksheets/tabs

[mike KBR]

Hello everyone! i wanted to thank yall for helping me
learn very quickly on excel..now everyone here asks me!
I have learned tremendously here!
thanks again in advance..now my quick question:

I have created a workbook consisted of worksheets that
are broken up by jobsites in landscaping. each tab is a
specified area to be done. each tab ( area) is to be
designed differently with different lists of plant taken
from the plant list given. now i have one tab that is the
master "plantlist" with name and size and cost per item.
Can I or How do i link each name of plant to each plant
that is on the tab sheet or do i have to manually link
each plant name to each of the tabulated sheets each time
the plant name is presented?? or is there a function or
conditioned linking i can use? perhaps a macro? anything
simple to help facilitate my editing. I found that plant
cost varies each month according to availability of
growers and costs and i have to change each one. very
time consuming. also ..as i add a new area or lists , can
it be updated? ** basically i want to be able to go to
the master plant lists page/tab and change the costs and
any info for that plant item and see it change across any
tabs or sheets that consisist of that plant item.

 

[Roger Govier]

Hi Mike

On your master sheet lets say Sheet1 you have plant names in A1:A100 and
prices in B1:B100
On your subsequent sheets, if the plant names are in column A, and you want
the prices to appear in column B, then enter in cell B2
=VLOOKUP(A2,'Sheet1'!$A$1:$A$100,2,0)
Copy down the cells in the range you require.

Repeat for other sheets.

 

[Guest]

awesome..i heard about vlookup. thanks!!it works..

 

[mike]

roger..could you break down what each digit means in yur
formula?? so i can better learn this ? Do my name have to
be in sequence? or can i have a "vlookup" for each
individual name? ie: in A1 a name #1 and then price in
B1..then skips a few rows and then plant name #2 in A5
and price in B5??
thanks.
i guess ia m asking if the lists have to be identical
form sheet to the master list.?

 

[Roger Govier]

Hi Mike

Firstly apologies, the formula I posted was incorrect, the correct formula
should have been
=VLOOKUP(A2,'Sheet1'!$A$1:$B$100,2,0)

Vlookup means look down a column of data within a range to find a match.
A2,
In this case, we are saying look for the Value that is contained in cell A2
in the first column of the data range that exists

'Sheet1'!$A$1:$B$100
on Sheet2 in the Cells A1 to B100 i,e look for the value in A2 on this
sheet within the cells A1:A100 on Sheet1.

,2

If you find a match, then take the value that is in the second column of
this data range, in this case in column B, on the same row as the match was
found.

,0
this is the optional 4th argument of the Vlookup Formula, and is equivalent
to the word FALSE.
Using this means that the formula will work, eben if the data isn't in
alpabetical order in column A

In answer to your question, the lists don't have to be identical.

The size of the data table can be as many columns as you wish, containing
more data such as description, planting date etc. so your data range could
be $A$1:$F$100

and
=VLOOKUP(A2,'Sheet1'!$A$1:$F$100,5,0)
would find the value from Column E on the same row.

I hope this helps, but if all is still not clear, post back

 

[Roger Govier]

Must be time for me to pack up for the evening, the fingers are't typing
what I am thinking

'Sheet1'!$A$1:$B$100
on Sheet2 in the Cells A1 to B100 i,e look for the value in A2 on this
sheet within the cells A1:A100 on Sheet1.

should have read
on Sheet1 in the cells A1 to B100

 

[Guest]

yes it clarified alot.. and it helps. along with the
help section adn ou explaining..couldnt be better. thanks
again and give your fingers a rest..lol

 

[Guest]

i got it to work but..what i did was create another blsnk
page with the names of plants and then the respective
vlookup form in the next line/cell. so that all i have to
do was copy and paste it whenever the respective name
came up on new jobs..i noticed wheni do that it actually
changes the col_index ref number.. any reason why it does
or do i have to input the function each time?