The Excel Thread

Thanks for the help Sawyer. I want it to show like the below ideally, without there being big gaps/FALSE returned between the two numbers.

Balladeer wrote:Hm, can you give a dummy example? E.g. a fake table with fake labels, what inputs you're putting in and what output you'd want (from the fake table)? My solution would usually be to use index-match at anything like this but I want to make sure it'd work.

I have table like the below where each column has a five letter word before the country name and each row is a month/year. I'm trying to create a formula that is in another table and it reads the month/year, country and five letter word which in their own columns. It then looks up the corresponding value in the table below.



So if my reference table had Jul-23, Orange, UK in its columns, the formula would return 745.

Super Dragon 64 wrote:

Balladeer wrote:Hm, can you give a dummy example? E.g. a fake table with fake labels, what inputs you're putting in and what output you'd want (from the fake table)? My solution would usually be to use index-match at anything like this but I want to make sure it'd work.

I have table like the below where each column has a five letter word before the country name and each row is a month/year. I'm trying to create a formula that is in another table and it reads the month/year, country and five letter word which in their own columns. It then looks up the corresponding value in the table below.

So if my reference table had Jul-23, Orange, UK in its columns, the formula would return 745.

You may be able to use an index and match approach, like below.


Here's the formula:
=INDEX($C$6:$G$9,MATCH(J6,$B$6:$B$9,0),MATCH(CONCATENATE(K6," ",L6),$C$5:$G$5,0))

It indexes the table data ($C$6:$G$9) by the row where $B$6:$B$9 matches the date in J6 and the column where the $C$5:$G$5 matches the concatenated text in K6 and L6 with a space in between.

Edit: Match isn't case sensitive so it won't work if your 5-letter codes may differ only by case.

Potterton wrote:

Super Dragon 64 wrote:

Balladeer wrote:Hm, can you give a dummy example? E.g. a fake table with fake labels, what inputs you're putting in and what output you'd want (from the fake table)? My solution would usually be to use index-match at anything like this but I want to make sure it'd work.

I have table like the below where each column has a five letter word before the country name and each row is a month/year. I'm trying to create a formula that is in another table and it reads the month/year, country and five letter word which in their own columns. It then looks up the corresponding value in the table below.

So if my reference table had Jul-23, Orange, UK in its columns, the formula would return 745.

You may be able to use an index and match approach, like below.


Here's the formula:
=INDEX($C$6:$G$9,MATCH(J6,$B$6:$B$9,0),MATCH(CONCATENATE(K6," ",L6),$C$5:$G$5,0))

It indexes the table data ($C$6:$G$9) by the row where $B$6:$B$9 matches the date in J6 and the column where the $C$5:$G$5 matches the concatenated text in K6 and L6 with a space in between.

Edit: Match isn't case sensitive so it won't work if your 5-letter codes may differ only by case.

I think that worked perfectly, thank you!

If only someone had predicted this.

Quick Excel formula question if anyone has any suggestions

I have a spreadsheet where we compare the current months figures+YTD against the same for last year. The numbers are spread across different tabs in the sheet and some headers appear multiple times with different values so the formula I am using is

=XLOOKUP('P&L'!A9,'2022 monthly'!$A$7:$A$11,'2022 monthly'!$L$7:$L$11)

Now, I wanted to see if there is a way I can simplify the way we update this spreadsheet every month without needing to so in and adjust each formula line to read the next column (So changing L7-L11 to M7-M11)

Could I do this by have the month and year as column headers in each sheet and then just simply changing the month to update the numbers? So for next month, in the main front sheet, all I would need to do is change the month at the top of the page to November 22 and then all the relevant numbers in that column would update?

Thinking maybe throwing an =IF function and the front of the above might do it but i've tried a couple of times and I keep getting #SPILL errors

I'd use an OFFSET if I were you. OFFSET('2022 monthly'!$L$7:$L$11,0,x) where x is a formula based on the month.

Incidentally I'm answering this to procrastinate away from doing some conditional formatting.

Bot sure if this is possible but this is the best place to ask ....

Trying to make a drop-down list with options to add numbers to it. So when the provider fills in the form to say what they have done they can select from the drop down and then add hw many times they have done a certain task.

Calls to social worker - 12
Calls to brokerage - 3
Visits to legal team - 5

Is it possible to have a drop down with the option to add numbers?

And if it is .. is it then easy to pull all that info through for reporting later?

Is the drop down list for the type of task? If you change the Error Alert style to Information it should allow you to enter information after the task text but it would allow anything to be entered I think. I would have a separate column next to the task to enter frequency personally.

Thank you I'll give it a try