Using Vlookups, improve lookup methods

[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"

Please Login or Register  to view this content.

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.


maybe if i attach the two files someone who knows what they are doing might be kind enough to have a quick look for me?
id be very grateful if you sorted out the "problem" but if its not too much trouble id also like to know what i did wrong, and how i can solve it myself next time.

thanks in advance
andy.

[abagnall]

If i need to provide anymore information in order to help someone, help me im happy to try and provide it.

[abagnall]

can anyone help? ive been thinking about my problem today, and i could do with a way of making sure the Vlookup stays with the correct column, even when a new column is inserted. as at the moment the data id need when just move to the right when a new column is inserted.

[dredwolf]

for your opening post you can try this :
on Sheet1 in ShowSales2013,
create a named range called showdata, including Columns A-I minimum, I used the following : showdata =Sheet1!$A$2:$I:$3002
Then on bandformblank bandsheet, change your VLookup in F3 to this :

Please Login or Register  to view this content.

for the others, change the Red 4 to the proper column number

as to the maintaining the proper column references, where would you be inserting the columns?
you could probably use the match() on column headers instead of the actual number:

Please Login or Register  to view this content.

for the support bands youd have to change the Column headers to unique names(ie-SUPPORT1,SUPPORT2,SUPPORT3,SUPPORT4) on the band sheet in show sales

Hope this helps

Edit-
Should probably change the [ShowSales2013.xls]Sheet1!$A$2:$I$2 to a named range as well,then inserting columns shouldn't create #REF! errors

[abagnall]

just quickly wanted to say thanks dredwolf.
i will try as you suggest and let you know how i get on.

in answer to your question "where would you be inserting the columns?" - potentially they could be inserted anywhere, so im thinking you might be right using MATCH....... guess im going to have to try and learn how to use MATCH now

[abagnall]

ive tried as you suggested, and it kind of worked... with the date. so thank you.

so i tried using the same formula.

Please Login or Register  to view this content.

to pull the other data, changing the "date" to the new title id given the other column .

Please Login or Register  to view this content.

but this times its returning a #N/A message. any ideas?

[abagnall]

ive been stupid, i named the range and had the formula look for the range not the column title as you said.

ive changed it and it all seems to be working.

im going to test it a little longer, see if i can find any other problems, but at the moment its looking good!
so thank you


....ill come back and mark it "solved" in a short while

[dredwolf]

If its returning #N/A, then it probably is not finding "su1" in the match function,
so Id check to make sure "su1" is within the A2:I2 range...if not you'll have to expand the range, say to A2:Z2

hope this helps

[dredwolf]

You are welcome

[abagnall]

this is a solved case now thanks to the people above.

ive attached the files again, just so if anyone has the same problem they can see how i ended up fixing it.

thanks all who helped.