Weird, I was just coming to the BBS to ask for help with an excel cross-function formula. Haymitch, Not trying to hijack your thread, but can I ask my question after you've gotten your answer?
Haymitch, OK, upon second thought, now I feel silly. It's even easier than using a Pivot Table. Step 1: Add a "Has Value" column to your Source Table Note the Formula: It Concatenates your two value columns, and returns TRUE when there is a value in one of them. For simplicity, I used random integers for your Date column, but notice that they are not in sequential order, per your requirements. Step 2: Using Excel Formulas, dynamically copy the Source Table to a new Sheet This step does no work at all, except to create an identical copy of your Source Table, so you can apply filters to it without altering the Source data. Including the top row, each cell has a very simple formula: =IF(Sheet1!A1<>"", Sheet1!A1, "") Just drag that to the right to create on row; the A1 will auto-increment to B1 in the drag action, and so on to the right. Then drag that row down to the maximum size of the Source Table. Step 3: Now apply two simple Filter Sort the Date column in ascending order Filter the "Has Value" column by "TRUE" Step 4: Dynamically refresh Table 2 when the Source Table changes. Add a row to the Source Table. To dynamically refresh, simple go to the Table 2 and hit submenu Data | Reapply...to reapply the filters. NOTE: you must have created a Table 2 to sufficiently support the maximum number of rows in the Source Data, since it is a row by row copy. If not, just drag the rows in Table 2 to create more. You might have to disable the filters first, before you can support a larger source table, then reapply.
Thank you. I'm going to keep this as simple as possible. I have 2 sheets in one spreadsheet. Both contain the same account names in order. One has summed yearly usage (horizontally across several columns) that I need to pull next to the account names in the other sheet vertically. I'm not sure whether I need to use some combination of the VLOOKUP, MATCH, and IF functions. Basically, I need F2-XG from the Totals sheet to correspond to F2-F641 in the Account sheet.
I'm pretty certain that's the job of a Pivot Table. The Account Sheet would be the Pivot Table of the "Total's Sheet," at least for the purposes of getting the Totals in the same Row as the Account. You shouldn't require any new formulas.
I could, if you gave me a sample dozen rows of your Totals sheet. Otherwise, I'd have to create phony data prior to taking the screen shots and making the step-by-step post. Too much work. Can you just go to youtube and watch videos on Excel Pivot Tables. Give it a shot. Maybe someone else can create some phony data and explain it.
