Can you use VLOOKUP to return value if conditions are met?

So i am working on a summary page for a worksheet that has couple hundred rows.
Those rows are "grouped" together based on key categories(rows with category names in all uppercase)

So my first attempt is to see if i can do a lookup in column A and pull back all rows that are all uppercase.

So i played around with this formula and since the categories are all different lengths, i think this would be safe amount to check

Please Login or Register  to view this content.

This returns TRUE, so since its true, can vlookup be used to return that row on my summary sheet?

Given that you are using 365 and you have a formula that produces TRUE, I would think you could use FILTER to return the rows you want.

Post the workbook.

Oh i know. but the workbook we have at this moment is all confidential information and not so easy to clean up to provide, thats why i didnt post.
I was more looking for formulas that could be used..

Ill put some generic workbook together that is setup in the same way and post when i get a minute today.

Ok,I have attached a stripped down version of what we are working with..
I provided a summary tab that shows the expected results.. in that tab, there are 2 examples of expected results, so in the final summary report there will only be 1 weekly summary, but in my sample file i have provided
2 examples based on the data in the main tab.

Im looking at it and looks ok but the activity list isnt growing based on the new change i made.
I added a X to day 11/13/2021, so on the summary page, i would need to see communication and bridge details

The summary page needs to pull the data from the All Data 2021 tab

But i think the idea is correct, just need to see how to base the summary off the other tab and have it populate based on the dates with values.

With a Helper Column in column C:

Formula:

Please Login or Register  to view this content.

Originally Posted by TMS

With a Helper Column in column C:

Formula:

Please Login or Register  to view this content.

TMS,

This looks alot closer to what im looking for, can you explain the formula in column c?
When i click into the formula, i see that its selecting that entire row, but how is it calculating a total for the rolling 7 days?
To test it i added a X to cell 013 which is 11/18/2021, and the value in column C stayed at 1 instead of 2, just want to better understand how that formula works so i can trouble shoot if needed in the production file.

C6:

Formula:

Please Login or Register  to view this content.

6:6 refers to row 6 and will autoincrease as it is dragged down.
INDEX(6:6,4) refers to column 4 on row 6. It is static. D6
INDEX(6:6,11) refers to column 11 on row 6. It is static. K6
INDEX(6:6,4):INDEX(6:6,11) then refers to the range D6:K6
SUM(INDEX(6:6,4):INDEX(6:6,11)) is the sum of the values in the range.

If you delete columns, the SUM will always refer to the same range.

All of which makes me realise that the 11 should probably be 10

And the FILTER should be:

Formula:

Please Login or Register  to view this content.

Try select start date in F1

and E2

PHP Code: 

=LET(z,'All Data 2021'!$A$4:$BI$23,st,F1,h,INDEX(z,2,),fh,SEQUENCE(ROWS(z))<3,y,FILTER(FILTER(z,(INDEX(z,,2)=0)+fh),(h>=st)*(h<st+7)+(h="activity")),xx,FILTER(y,MMULT(N(y),SEQUENCE(8))),FILTER(y,MMULT(N(y),SEQUENCE(8))+(SEQUENCE(ROWS(y))<3))) 


TMS

Im playing around with your file now and seems to work as expected, im moving the code over to the production file to see if i can get it working.

Bo_ry

Ill be testing yours shortly as well, I have time today to work on this and will be testing both out and presenting to the manager to see what they think.


Thank you both so much for the assistance..
Ill post back once i implement and let you know how it went.

Well ran into the first problem, so i guess my sample file i provided was not the best example, because in the production file, we are hiding columns as those past dates have already been reviewed.

So right now the production file has the first columns A - E visible as thats part of the Activity data to present, But the individual days start on column F and go all the way to CX and since today is 11/12/2021, that means that next to column E you see column AV since that is yesterday 11/11/2021

So using your file TMS im little lost on how to grab AV today and next week have that automatically return starting sunday 11/14/2021

Does that make sense?

If i can ill upload another sample file, but it wont be until later this afternoon as im now stuck in meetings for a few hours.

Originally Posted by Pete_UK

What you can try to do is to insert blank columns in the files that have been supplied above in order to get the layout of those files to match with the layout of your production file. The formulae should automatically adjust to suit the inserted columns, so it should then be relatively easy to copy/paste them into your production file.

Hope this helps.

Pete

So in the working sample above, just insert empty place holder columns into that file and test how it reacts with the working formulas? correct...

Ill give that a try, if not, ill try and strip down the production file and post that.. have one more meeting to go so ill be back after that and see what i was able to get working..

Maybe try the Bo_Ry solution. That references a start date in cell F1 in the output sheet.

So here is a updated more realistic layout of the production file.
I added the same number of columns and hid all days up until yesterday like the production file.
In the summary tab you can see that the current formula as it is pulls in columns into the layout that shouldnt be shown

Im testing the second sample file provided to see how that one work with the production data.

Originally Posted by Bo_Ry

Try select start date in F1

and E2

PHP Code: 

=LET(z,'All Data 2021'!$A$4:$BI$23,st,F1,h,INDEX(z,2,),fh,SEQUENCE(ROWS(z))<3,y,FILTER(FILTER(z,(INDEX(z,,2)=0)+fh),(h>=st)*(h<st+7)+(h="activity")),xx,FILTER(y,MMULT(N(y),SEQUENCE(8))),FILTER(y,MMULT(N(y),SEQUENCE(8))+(SEQUENCE(ROWS(y))<3))) 


So im trying your file and anything that falls within the days displayed on the summer, the new activities show up as seen in the screen shot attached.
But the days are static for 7 days, is there anyway to have that only display rolling 7 days starting with "yesterday" plus 6?

So since today is 11/12 i would want to see the summary start with 11/11 in column F and display the next 7 days
and how would this example work with the last file i uploaded that had all the columns added and hidden?

Put =TODAY() in cell F1.

Originally Posted by TMS

Put =TODAY() in cell F1.

Ill give that a try in the prod file, thanks that updated the rolling 7 days

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Ill mark it solved once i get it working in the production file, which im working on right now..

@Bo_Ry i created a summary tab in the production file, to keep it simple and as close as possible to your example, i placed the formula in the same cell on this new sheet "E2"

Please Login or Register  to view this content.

I updated the sheet name and the range within the sheet, i didnt touch any of the other code.
I entered it as a normal formula and also tried as an array formula "Ctrl-Shift-enter" and neither method worked.

I get a "#VALUE!" error in that cell.

What am i missing or doing wrong? the only thing i see in the formula, which im not clear on is the portion that says the following: h="activity" what is that and where do i need to setup? or is that concern that actual data?

Can anyone help? I cant seem to find anything i may have missed in trying to make this work. The data tab is named the same, the rows all start in the same position within both files(test and production) the only thing i cant figure out or dont understand is the portion of the formula that has +(h="activity"))

Seems like the problem helping you resolve this issue is that your production file is not truly represented by the sample files you are sharing.

Best bet is to take the production file, get the first 50 (say) rows, and desensitize it.

I don't think anyone is going to come back with a variation on a theme for it to get knocked back again.

The solutions MUST be close. The contributors just need your help for them to help you.

So here is a stripped down version of the production file, I will have to say, that after removing a number of rows, the summary tab shows some information, but not like the sample file that was provided few
posts back.. So not sure what i removed from the data that caused the formula to actually show some data.

This is a copy of the prod file, just basically removed all the rows that had sensitive information. This has all the columns and existing formulas on the data tab, again the only thing missing from this file
is the extra rows, prod file has around 370 rows and this copy only has around 70 rows.. other than that, everything is intact. Past days have been kept hidden as they are in the production file as well.

There is nothing missing from this file at this point(except the sensitive rows)

Can anyone help with making the corrections or provide a explanation of how i can update it to display the correct data?

Last try

=LET(z,'New Format 2021'!$A$4:$CY$69,st,F1,h,INDEX(z,2,),fh,SEQUENCE(ROWS(z))<3,y,FILTER(FILTER(z,(INDEX(z,,2)>0)+fh),(h>=st)*(h<st+7)+(h="activity")),FILTER(y,MMULT(N(y),SEQUENCE(8))+(SEQUENCE(ROWS(y))<3)))

Bo_Ry
I tested your updated formula in the production file and the only thing i change was the CY69 to CY352, i left everything else the same,
but im getting "#VALUE!" error in the cell and the popup shows this message: A value used in the formula is of the wrong data type

Is there limitations on what text can be shown or read using the formula?
Clearly there is some data that it doesnt like, but not sure where to look to see if i can fix the issue.

Changing 69 to 352 in the sample file is not a problem.

Do you have the date in cell F1 on the summary sheet?

If the date is blank or not found, you will get a #VALUE! error.

yes i do

I even tried the date in F1 on the New Data 2021 sheet as well since I'm not familiar with the formula used.

Both places i have the date are formatted as "Date"

The only difference between the sample file i provided and the production file is that the "activity" values can and some do contain parentheses like so:

DC OPS HOURS (John Doe)

The sample file i provided last week, i removed all that from the cells, would that make a difference? I dont want to remove them from the production file if it wont fix the issue..

The date is definitely on the Summary sheet.

The brackets do not affect the formula. Try it in the sample file.

Then im at a loss, because the only thing different between the files is the number of rows, the last sample file i provided was the exact same minus couple hundred rows.
And the screen shot above is from the Summary sheet in the production file.

there has to be something i have overlooked, something like it says that is not properly formatted.. and its probably something simple. I just dont know what to try.

See the attached updated sample file. It might clarify some points.

You might try adding elements into it.

im playing around with this new file today to see if i can get this figured out.. so are the variable declared and assigned within the formula?
In your updated file, i got a new section to show up, so there has to be something else wrong with the production file..

So i understand the z and st variables
But what do these variable do?
h
fh
y

Is h reading in the dates from the row with the header dates?

Well, z is the data area from row 4 down, so h is the entire second row of that data area. That is, the row with Activity in column 1.

fh is used to generate a sequence of TRUE/FALSE entries. It is TRUE for the first two rows, so I assume that is what picks up the headers.

y is clearly a double filter using the previous variables and is used in the generation of the output table. How, exactly, it does that is beyon me at the moment. Not an easy formula to use Evaluate on .

Clearly, whatever you have deleted must make a difference but hard to guess what that is.

Can you not convert the sensitive data to boilerplate text (of the same format) rather than deleting rows.

Looks like the number of rows is not an issue, nor is the content of the areas. So, it must be something to do with what you've take out … and that we cannot guess.

as i have time today, im going to try and basically migrate over the prod data into the file you have provided and see what or where it breaks as i move over data, there is probably something very simple that is causing it and you are right, its all guessing without really knowing what is truly different.

I do appreciate your help in explaining the formula and for providing another working example file. If i can pin point the actual cause ill def post it back here so others can learn from my mistakes lol..