Vlook up and DV over 2 workbooks

[abagnall]

this is my first attempt at anything like this, i am wanting to link two workbooks together with the aim that when a option is selected in a drop down menu in workbook "band form", it auto populates the rest of the form with data from the second workbook "showsales2013". date, ticket price, support acts etc etc.......

as a example,
the drop down in workbook "band form" is a list of bands (list data also taken from showsales2013) ive got this bit working ok (i think ), each band is playing on a different day so i have used this formula to get the "date" from "showsales2013"


<code>=VLOOKUP(F4,[ShowSales2013.xls]Sheet1!$1:$65536,4,0)</code></pre>
this seems to work until i insert a new row and/or column (in the showsales wookbook - which needs doing from time to time as new bands get booked to play), then i get #REF in the date cell on the "band form blank".
i used pretty much the same formula to pull the other needed data (price/support acts etc ) from showsale2013, and the result is the same, #REF.


ive got the two excel files which im willing to attach for people to have a look at, but i cant seem to find how to attach them,

any help would be great.
thanks in advance
andy

 

[Andrew Poulsom]

I can't reproduce your #REF! error in Excel 2003. Inserting a row on Sheet1 of ShowSales2013.xls has no effect on your formula because it is referencing the entire worksheet.

 

[abagnall]

im using 2007, if that make a diff. ive just tried again and got the same #ref error.
as i say im new to this, i tried to set the formula out in a logical way to me, learning it for the first time, thinking that if i included the whole sheet it would work when extra rows/colmuns are added. im not sure why its not working!
but thinking about it all day, ive concluded that i was wrong to begin with, the columns need to be "set" the the formula.
as at the moment if someone inserts a column the reference i have in the formula would then be out one. the row information(DV drop down) is (i think) ok as its the whole column thats pulled from showsales, regardless of whats added.

thanks andrew for taking the time to look at my issue. im not up on all the terms so im probably making it harder than it is.

again
thanks

 

[Andrew Poulsom]

Yes, I can reproduce what you are seeing in Excel 2007, which is rather surprising. Can't you avoid referencing entire rows/columns?

 

[abagnall]

i think youre right, think i have no choice but to try avoiding referencing entire rows/columns. would a solution be to name tables and then use some sort of MATCH formula? (both of which i dont know how do to at this point) again, thanks for help/advice.

 

[Andrew Poulsom]

You could use a dynamic name:

Excel Names -- Use Range Names in Formulas