Automatically populate a table with slected data based on criteria

Hi All

This is my first post on the forum so apologies if I've posted this in the wrong area. I am an experienced excel user but haven't got round to learning VBA yet.

I have a problem with some data handling at the moment.

I have 6 tables of data that relate to different locations and shading types. I am trying to automate the spreadsheet to pull the data from one of these tables into a new table on a different worksheet based on selection criteria.

For example, the first two tables refer to the SE and unshaded or intermittant shading respectively. In the main workbook I've setup an IF statement to give SE a value of 1, shaded a value of A and intermittant shading a value of B. I've then combined these two to give a reference of 1A or 1B. Based on this selection I would like the spreadsheet to pull in the information from the respective table into a new table in a different worksheet.

I have named the ranges se_unshaded, se_intermittant etc and have tried to use the offset command to reproduce the information but to no avail.

I'm not sure if this type of action requires a macro or whether there's a trick I'm missing.

If anyone could point me in the right direction I would be eternally grateful.

I've attached an extract of the spreadsheet to show what I'm trying to do.

Cheers

Rog

Excel Forum Help.xlsx

Hi All

I posted a problem with automatically selecting data earlier on this week. Had a wonderful reply that worked perfectly but have had some trouble adapting it to suit the final workbook. I have attached the full spreadsheet here and was wondering if anyone could recommend what changes are required to the code.

Basically I have two if statements that return values based on selection in one worksheet. I have then combined them using the & statement to return a nemed range reference in a cell. This named range refers to data that I want to pull in from another worksheet. The current macro works but because I only posted an extract of the spreadshett ti only updates the values if you specifically select and change the cell. The original guy who solved this for me created a list validaiton so when you selected the name from the list the data updated. I need to monidfy this so that it updates automatically without the user having to slect the cell.

I confess I have absolutely no idea about how to programme VBA so I am really stumped on this one. On another note, could anyone recommend a good book or two I could get so that I can start learning how to programme VBA? I'm very keen to learn.

Thanks for your time

Rog

Admittance calcs update forum version.xlsm

Hi Richard

Tried that but I now get the following message:

Run-time error '1004':

Method 'Range' of object'_Worksheet' failed

It highlights this line in yellow

If Not Intersect(Target, Range("Selection")) Is Nothing Then

SOrry to be a pain and thank you so much for taking the time to help with this.

Cheers

Rog

Richard you are a god in mortal clothes!

Thank you so much for all your help on this.

Many many thanks again

Rog