Help needed with what excel formula to use. Need to display data for only specific cells

**radekrat** · 05-12-2023, 05:39 PM

Good Evening All

I am really hoping for your help with this one since I have no idea what formula can be used.
I am trying to make my and my colleagues life easier

I have a list of Location names that drivers need to visit daily, some are getting cancelled depending on the day of the week. If pick up is cancelled - arrival times will be deleted.
The List is different for each day and there would be tabs for each day of the week.

Trying to populate a list for the staff offloading where they will only see the arrivals that have not been cancelled. The list needs to populate in "Mon record" tab.

Need a formula in "Mon record" tab in cell A2 - that will display Pick up Location only if it has planned arrival time on the "Mon" tab. If there is no arrival time for the Location - it would go to the next row and apply same logic which in result would only display data in the "Mon record" tab for the pick ups that have not been cancelled.

The same logic would apply to Tuesday, Wednesday etc.

If pick up is cancelled - arrival times will be deleted and would not want any data for this row to be copied in to the "Mon record" tab.

Any advise is greatly appreciated. I have attached a sample sheet.

I am using Office 365 enterprise Version 2202 (Build 14931.20660)

**sas5610** · 05-12-2023, 06:54 PM

You can achieve this by using index and match function.
Paste and drag formulas in each column in Mon sheet as follow:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Cancelled deliveries entries will appear blank but you can filters those and keep the cells with records

Hope this solution works for you!

**radekrat** · 05-12-2023, 07:50 PM

Hi

Thanks a lot for looking into it.

is there a way to avoid having empty rows of data that need to be filtered?

**TMS** · 05-12-2023, 08:09 PM

Maybe this:

Formula:

Please Login or Register  to view this content.

**sas5610** · 05-12-2023, 09:30 PM

Yes, there is a way through using offset or counta but can't wrap my head around it now. It's very late here but will get back to you

**Gregb11** · 05-12-2023, 10:34 PM

Trevor's formula is a good one in post#4.
A word of caution for you though. I notice you have a note that there's a data area where there will be manual input by admin clerical. Because the data displayed is dynamic, that if someone changes the data in "Mon" sheet after any clerical data is entered, it will go against the wrong row.
Of course you'll say that "We only enter the clerical data after the data in Mon sheet is completed". Next think you know, someone forgot to enter something on Mon sheet and enters after clerical data has been entered. Anyway, like I said, just a word of caution that it's always risky to write data next to data resulting from a dynamic formula.

**radekrat** · 05-13-2023, 04:34 AM

I am getting #NAME? error when pasted this formula in A2 of "Mon Record" tab

What am I doing wrong?

**AliGW** · 05-13-2023, 04:49 AM

You need to upgrade to build 2208 or later for CHOOSECOLS to work. Just run updates from Excel to get up-to-date.

**TMS** · 05-13-2023, 04:52 AM

Then

Formula:

Please Login or Register  to view this content.

Or

Formula:

Please Login or Register  to view this content.

**sas5610** · 05-13-2023, 05:38 AM

Nice formula, I am rusty It's way more efficient compared with Index and Match.

**radekrat** · 05-13-2023, 09:00 AM

TMS thank you for your help, managed to get it working

Fantastic, I would never be able to do it.

**TMS** · 05-13-2023, 09:50 AM

You're welcome. Thanks for the rep.

Help needed with what excel formula to use. Need to display data for only specific cells

LinkBack

Thread Tools

Rate This Thread

Display

Help needed with what excel formula to use. Need to display data for only specific cells

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Re: Help needed with what excel formula to use. Need to display data for only specific cel

Thread Information

Users Browsing this Thread

Similar Threads

Display Data in specific cells per row but skip rows with zero value in a specific column.

Formula needed (cells containing specific texts/numbers)

[SOLVED] Cell subtract cells with specific text formula needed please

Formula needed to determine if specific date is included between 2 cells

I need a formula to display a cells value if it falls between a specific data range, help!

VBA needed to extract data from specific cells in different worksheet

macro needed to sum specific data in specific cells

Bookmarks

Bookmarks

Posting Permissions