I am attaching my file here. If you notice I have on column B a formula for left that takes the firs 9 characters and gives me that name. This works great except that this is inside a power query table. whenever I do a refresh on the power query I get a #REF error because for some insane reason it isn't finding the column although the column never changes only the input within the row changes. With that said I am trying to do the same thing but with an xlookup. I would like to look for the name in Column D but only the first 9 characters of that name and then I need the output to be those first 9 characters.
I hope I explained it correctly. here is my attempt:
I hope I explained it correctly. here is my attempt:
| Sample_File_forecast1.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | First Characters | Project Name | New Business (RFS) | Total Charges | |||
| 2 | Project 1 | project 1 | #N/A | project 1 name | 602.95 | ||
| 3 | Project 2 | project 2 | project 2 name | 363.5 | |||
| 4 | Project 3 | project 3 | project 3 name | 1500 | |||
| 5 | Project 4 | project 4 | project 4 name | 2520 | |||
| 6 | Project 5 | project 5 | project 5 name | 5500 | |||
| 7 | total | 10486.45 | |||||
T&M | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2 | C2 | =XLOOKUP('T&MMthlyFile'!B:B,'T&MMthlyFile'!A:A,'T&MMthlyFile'!B:B) |
| B2:B6 | B2 | =LEFT(D2,9) |
| E7 | E7 | =SUM(E2:E6) |
