Looking for a cell with # with a date range then looking up to the date

[chrisandsally]

Hi

I have a problem that I would your help with. I have attached a spreadsheet.

I need a formulae in sheet 2 that looks at sheet 1. Within a defined date range. That looks at any cell with a #AAA (AAA being an employee initials) It needs to find the last # in that defined date range then look up to the date. The date is what I need to be presented.

Hopefully the attached sheet will show what is needed.

Help.xlsx

Thank you,

[sktneer]

Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In E5

Please Login or Register  to view this content.

and copy down.


******************************

Remember an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[chrisandsally]

Thank you Sktneer however this works for a fixed period I need it look up between the defined range C2 - C3. In fact coming to thing about it I do need to 'from' date just the 'to' date C3. So what is the last # up to the date in c3.

Thank you for what you have done thus far

[jhren]

In E5

Please Login or Register  to view this content.

Ctrl+Shift+Enter

Copy down

[chrisandsally]

Thank you so much jhren, it is working perfectly. Rep coming your way. Please could you explain ("Sheet1!R3C"&$C$2-42001&":R7C

I am trying to amend to formulae into my main sheet from the bare bones example sheet. Ant cant get it to work. I am making it into an array. Crtl - shift - Ent etc.



My sheet1 is called 2015 but having problem with the R3C and R7C bits. I am guess it is Row and Column. My formulae is

Please Login or Register  to view this content.

Sheet1 = 2015 My Rows are 4 to 60
name tag of year is the row of data 1/1/2015 to 31/12/2015 on 1st sheet now 2015
D9 contains the initials

From date is in C3
to date is in C4

Thank you for your help.

[sktneer]

Other than what jhren has suggested, you may also try this Array Formula in this case...

Please Login or Register  to view this content.

and copy down.

[chrisandsally]

Thank you sktneer, your formulae works a charm and I have successfully amended into the real sheet from the example sheet.

Thank you again for your time.

[sktneer]

You're welcome. Glad I could help.
And thanks for the feedback as well.

[chrisandsally]

sktneer

I have just spotted a little extra that is required it needs to look on two different sets of rows. E4 - E28 and E41 - E53

How can this formulae be amended

Please Login or Register  to view this content.

I can't seem to get it to work

Please Login or Register  to view this content.

Where named range of lastdos = ='2015'!$E$4:$NP$28

and lastdosn = ='2015'!$E$41:$NP$53

Thank you.

[sktneer]

Do you mean that you need to look for initials in the range '2015'!$E$4:$NP$28 and range '2015'!$E$41:$NP$53 and return the max date from these two ranges?

[chrisandsally]

Hi sktneer

Yes that is correct. thank you for this.

[sktneer]

Can you post a sample file with the data in these two ranges along with the expected output shown manually?

[jhren]

Thank you so much jhren, it is working perfectly. Rep coming your way. Please could you explain ("Sheet1!R3C"&$C$2-42001&":R7C

I am trying to amend to formulae into my main sheet from the bare bones example sheet. Ant cant get it to work. I am making it into an array. Crtl - shift - Ent etc.



My sheet1 is called 2015 but having problem with the R3C and R7C bits. I am guess it is Row and Column. My formulae is

Please Login or Register  to view this content.

Sheet1 = 2015 My Rows are 4 to 60
name tag of year is the row of data 1/1/2015 to 31/12/2015 on 1st sheet now 2015
D9 contains the initials

From date is in C3
to date is in C4

Thank you for your help.

You have the gist of it. R3C & R7C are the start of R1C1 style addresses and the latter part is a conversion formula changing your start and end dates to column numbers in the example. I didn't save my revised copy of your file, but if I recall correctly it became R3C4:R7C11 (i.e. D3:K7)... whereas the column number changes according to the start and end dates.

If your actual file has the data in row 4 to 60, that exactly what you'd change the 3 of R3C and 7 of R7C to, respectively (R4C and R60C).

The minus 3 near the end is the column offset from column A to first column of dates (i.e. "dateh1" in example vs. "year" in actual)

[sktneer]

Try this and make changes in the range reference as per your actual workbook.

Please Login or Register  to view this content.

I think you will need to change C3 to C4. See if this helps.

[chrisandsally]

Hi Sktneer and Jhren

Thank you Jhren for your reply but currently i have used sktneer suggestion.


The problem is that I have two separate ranges to lookup.

I have attached another example file

Help2.xlsx

Thank you

[sktneer]

As per your new file, try this......

Please Login or Register  to view this content.

[chrisandsally]

Thank you that works but it picks up the initals with or without the #.

I need it to look at only #AAA not AAA e9 has the # infront for easy lookup. So close.

Thank you again, me thinks only one more step.

[sktneer]

In the suggested formula, the lookup value is D9, change all the instances of D9 to E9.

[chrisandsally]

Thank you

what does this ref do COLUMN($B$41)+1)))=0, The B41 what sheet is this referring to 2015 or console?

I want to try and learn how your formulae works.

[sktneer]

My bad....
Actually it should refer to Sheet 2015, I forgot to include the sheet reference, though it wouldn't make any difference in the calculation but it should have been written with the sheet reference as well.

[chrisandsally]

Thank you. Should I enter it in? When you say it make no difference why does if point to that specific cell on that sheet?

[sktneer]

Yes you should reference the sheet as well, this would be syntactically correct.

[chrisandsally]

Thank you so much

so why that specific reference value, i cant work that out!

[sktneer]

That will return the column counting of the referenced range like 1,2,3,4.... and so on which will be multiplied with the True and False returned by the conditional statements.

Just select the various parts of the formula in formula bar and hit F9, that will show you that how the specific part of the formula would work.
Another way is to evaluate the formula and monitor each step in the calculation. You will find Evaluate Formula under Formula Tab, use it to step through the formula.

[jhren]

See if this works for you...

[chrisandsally]

Thank you jhren.

That is a nice formula. Could it be amended, as sometimes there is a number after the initial like #AAA5 currently it does not pick this up.

Thanks

[jhren]

Change from...

Please Login or Register  to view this content.

...to...

Please Login or Register  to view this content.

[chrisandsally]

Thank you so much for you help.