Reference a cell within a row and then populate with the value that is last in the column

**Brumbot** · 01-07-2013, 04:54 AM

Hi all,

I am having a problem with a spreadsheet I am working on. I have a table that contains a bunch of heading and then some values under those headings for each month. Another table then populates with the 12 most recent values from this table, and a chart runs from that table to give a graph that only display data from the last 12 months.

The problem is that currently the values in my 2nd table are only displaying the first 12. The way I would want it work is to read the heading of the 2nd table (currently in BW5), find that value in row 3:3 to find the correct column, and then return the last value in that column. From there I can work out the rest to populate the other 11 rows. The range to search in is B7:BJ100.

I'm sure the answer is right in front of my eyes but I just can't seem to get it to work.

Thanks for the anticipated help

**Fotis1991** · 01-07-2013, 05:11 AM

I think that will be usfull to upload a sample workbook.

**Brumbot** · 01-07-2013, 05:19 AM

Thanks for the swift reply Fotis.

Here is the part of the workbook that I am struggling with:

Test book.xlsm

The way it currently works is - as data is fed into the main table, the right hand table will update the headings based on the top 5 values in the most recent row. The months will update to the most recent 12 months too. Currently, the RPN values do not update with the most 12 most recent under each heading - they just find the first 12.

**Fotis1991** · 01-07-2013, 05:54 AM

As my poor English does not help me

, can you pls tell me in columns BK & BL, which are the expected(correct) results and why?

**Brumbot** · 01-07-2013, 06:23 AM

Originally Posted by Fotis1991

As my poor English does not help me

, can you pls tell me in columns BK & BL, which are the expected(correct) results and why?

BK and BL are correct. Apologies, I don't think I made it clear enough.

The problem is with the 'chart feed' table, starting in BV column to the right of the 'top 5 FMEA' table. The expected value in BW7 would be 330, as it is the result that links the column header (electrodes >7 days wait) and the date (Feb-13) in the left hand main table.

I suppose instead of referencing the header 'electrodes >7 days wait' and then looking for the bottom values, I could use a formula to find the intersection of 'electrodes >7 days wait' in row 3:3 and the date 'Feb-13' in column A:A?

I hope this clears things. Please let me know if it still needs clarification. I know it is complicated, it confuses me a bit too :P

**Brumbot** · 01-07-2013, 10:52 AM

Bump. Still awaiting a response. Any help is appreciated.

**bebo021999** · 01-07-2013, 11:58 AM

Try it in BW7, copy accross and down

Please Login or Register  to view this content.

**Brumbot** · 01-07-2013, 12:27 PM

Hi Bebo, thanks for the response.

I tried this and it worked for the first column but didn't for the other 4. I played around with indrect(address) myself and managed to get it working with:

Please Login or Register  to view this content.

It seems to be working right now. Sorry for all the fuss!

Reference a cell within a row and then populate with the value that is last in the column

LinkBack

Thread Tools

Rate This Thread

Display

Reference a cell within a row and then populate with the value that is last in the column

Re: Reference a cell within a row and then populate with the value that is last in the col

Re: Reference a cell within a row and then populate with the value that is last in the col

Re: Reference a cell within a row and then populate with the value that is last in the col

Re: Reference a cell within a row and then populate with the value that is last in the col

Re: Reference a cell within a row and then populate with the value that is last in the col

Re: Reference a cell within a row and then populate with the value that is last in the col

Re: Reference a cell within a row and then populate with the value that is last in the col

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions