Column Duplication on different sheets then copying other cell values. (complicated)

[baconranch]

Hi,

I am having somewhat of a difficult time figuring this out. I lost most of my VB knowledge years back, but I still have enough understanding to manipulate coding similar to what I need, but I can't find anything for this.

Background: I have 3 pages total. One is a work summary, another is a list of clients. I need to have a master sheet that imports the information from both sheet into one sheet. The info I need is in different places on both of the other sheets.

Here is what I'm trying to do. On "Master" sheet, I have a list called client number (Column B). I need this list to be a duplicate of column Q from the "Client List" sheet. I know I can copy it, but I need Column B on the Master sheet to update automatically if additional clients are added to the client list (I don't have this done yet).

My next problem is I then need it so that if the client number is, for example, 55555 ( say in cell B1 on "Master" sheet), It can pull info from another sheet accurately.

Ex. On my Master sheet, Column A is for the customer Name. I would like it to take the number 55555(from B1) and run it through column B on my "Wrk Summary" page. When it comes across 55555( which could be located in any cell) I need it to pull a value(or text) from a different column in the same row. (Master sheet B1 = BX on "Wrk Summary" therefore column A1 will need to be equal to "Wrk Summary" CX)

I hope this makes sense, if not I can create a sample worksheet, however I cant post what I have no as it is sensitive info.

Please help as I need this done soon as the timeline is approaching

 

[baconranch]

So thought i would let people know. I managed to use a =IF(MATCH(C:C,cst_nm,0),City,0) formula for each of the data I was after. C:C is the clients number column, and then the cst_nm is the defined name of the column on the wrk summary sheet. City is the Master sheet list ofthe column that matches with defined name for the City on the wrk summary.

This worked well for every column of information I wanted (surprisingly) save for one.

I have a year column. On the wrk summary, I had to take a value and assign a year to it. I did this:

=IF(I2>26300000,2016,IF(I2>26000000,2015,IF(I2>25700000,2014,IF(I2>25400000,2013,IF(I2>25000000,2012,IF(I2>24800000,2011,IF(I2>24500000,2010,IF(I2>0,"N/A"))))))))

This assigns a year value based on a sticker number we assigned them. When I tried the

=IF(MATCH(C:C,cst_nm,0),year,0)
formula it worked for the first 453 rows. At 454 and ongoing to the final row (8000, for now) the cells all list a 0 rather then a year or the "N/A".

I cant seem to figure out why this would be happining. In all the other info columsn i have (address, phone, month and day) it all worked fine. I think it is something to do with the fact year column is a formula rather then just a value. But I can't figure out why the first 453 worked!!!!

Any suggestions?

 

[baconranch]

Okay so Wrk Summary ONLY HAS 453 ROWS!!! So all the other columns worked, because it was pulling the info of the "client list" page. Now that im pulling from the wrk summary, the info is wrong, because I 'think' what its saying is that;

On the Master sheet, Column Year now displays the renewal year.

the formula I now have is =IF(MATCH(C:C,Nameyear,0),Year,0).

Nameyear is the defined name for the list of client numbers on the Wrk Summary Page

Year is the column that displays the year (formula mentioned in previous post).

This formula worjks perfectly for the first 453 rows.

My client list is well above that 453, as will my Wrk Summary page once data is inputted.

Right now I think the problem is that since the Wrk Summary only goes to 453, if a Client number, says 55555 is on my master page in cell 500, it looks for the info in row 500 on the wrk summary, which would be 0 (0 is what is being displayed in the master). It seems the formula doesnt realize that client 55555 is listed in row 10 on the Wrk summary, so its not pulling the info from the info from row 10 on the Wrk summary, it only knows that since 55555 is listed as a client on both master and Wrk Summary, but listed in row 500 on Master, the info should be in Wrk Summary at the same place.

How would i make it so that it would siphon through to the row that 55555 is in on the Wrk summary, and not on the placement of the Master sheet.

 

[baconranch]

WOOT! yeah, woot!

Got it working. So what I was doing was completely wrong. I only just realized that the numbers it was pulling up were inaccurate. However, now my information is correct.

I used a INDEX MATCH, and with the info being the same as above, my formula became:

=INDEX(wrkyear,MATCH(C2,Nameyear,0))

SOLVED!