Return an array which changes according to latest date in heading at the top of table

Hello

I have attached a worked example. For the sake of simplicity, I have included the return array and the table in the same sheet.

I need to have a vertical array returned into a column in one sheet which contains a variable parameter (Purchase Order No) from a table in another sheet that contains the relevant Purchase Order, Cost category and date. The vertical array that is returned will always relate to the most recent date that has been entered into the table.

Thanks in advance
david

Hello - thanks for that.

I have attached an updated worked example.

That does work, but not exactly how I hoped. It is my fault for not being more explicit. Can you adjust the formula such that formula takes into account the whole table across all Purchase Orders in the table sheet and the entire Purchase Order reference in the return array sheet?

Your help is greatly appreciated.

David

Hello Dill Pandey

I am sorry but I am being an idiot! The return vertical array is not the same depth as the table array, which is why the PO number is needed beside the returned table array to look at the PO in the table. The returned array needs to look-up against the the PO and the cost category. This way I can just copy the formula without worrying that it is not the same depth.

I am sorry for this confusion. I am unaware that it could have been done simply like you have proposed.

Would you please mind adjusting.

Thanks in advance.

Hello Dili
I am sorry to be problematic. The Return Array is column B in sheet Return Array. It picks-up the most up to date cost categories (as defined by the date). I need the formula to use the Purchase Order No in Table Array sheet to map to the correct cost categories associated with the right Purchase Order No. I need to be able to match to both PO No and Cost category so I can just use the same formula assuming the rows between each PO set in Sheet Return Array is a different number of rows that is between each PO set in Sheet Table (presently 1 row).

Thanks in advance. Should I work up another example so it is easier? I am sorry this has proven complicated for you.

David

Hello there,

I have amended the examples (v1.2).

I hope this is clear. The returned data (result data) goes into Column B of Sheet Return Array fetched from the table in Sheet Table (Source data). The formula is in Cell B3 of Sheet Return Array and needs to be copied down the vertical array (Column B). Obviously where there is a break in the returned data, the formula needs to skip the rows. Note the depth of the returned data column (Column B)does not correspond in size to that data table. Further the PO's may not be in numerical order (i.,e PO1, 2 etc).

I hope I have made this sufficiently clear so that I don't waste your time further.

Thanks in advance.
David

Dear DILIPandey

File attached. Solution works (outstanding), but having difficulty in transferring to actual spreadsheet layout.

Thanks for that proposed solution - that works. I have to honest and tell you that I do not quite understand the logic of the count and where that lies in the syntax structure.

Therefore, I am struggling with transposing the formula and adjusting the offsets to the actual file format where the columns in which some of the references are in are in fact different.

Could you please look at "Cash Shift Actual Format" and tell me how to implement the proposed solution into the Actual file.

This would be greatly appreciated. I am being totally stupid, but I cannot see how to implement this.

David

Hello DILIPandey,

Sorry, I forgot to explain the new locations in the new file "Cost Shift Actual Format v1.0" so you may compare and transfer the formula you have proposed which works in the example.

Refer to the file "Er Revised CAPEX" - this is where the result data is going to. I should have added that the correct formula location is in Cell C195 (marked RED). This is where the result data starts in Cell C195 and will be copied down Column C wherever there is a parameter the two parameters PO (column H) and Cost Category (Column B) exist.

The source data (table) is coming from "Ericsson BOQ CAPEX". In the data source table the cost to be returned per the latest date is starting in Column E and extends right. The most recent cost set as defined by date is sent (resulted) to the result date. So the correct cost data is picked up for the the right PO, it is matched to PO No in column C and cost category in column D.

Thank you very much for your help on transferring the formula. I cannot see how to change the offsets as I do not quite understand how the Count function is working.

David