Confused by ' " and & in constructing formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1091634%22%20slang%3D%22en-US%22%3EConfused%20by%20'%20%22%20and%20%26amp%3B%20in%20constructing%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091634%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20in%20D5%3A%20(and%20subsequent%20rows)%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(SUMIFS(Expend%2C**bleep**%2CData!%24G%24%3CFONT%20color%3D%22%23FF0000%22%3E2%3C%2FFONT%3E%3A%24R%24%3CFONT%20color%3D%22%23FF00FF%22%3E2%3C%2FFONT%3E%2CBudget%2CINDIRECT(%22Data!M%24%22%26amp%3BVLOOKUP(ROW()%2CCodeMatch%2C2%2CFALSE))))%3C%2FP%3E%3CP%3EThere%20are%20about%20100%20lines%20in%20the%20sheet%20but%20the%20reference%20to%20%24G%24%3CFONT%20color%3D%22%23FF00FF%22%3E2%3C%2FFONT%3E%3A%24R%24%3CFONT%20color%3D%22%23FF00FF%22%3E2%3C%2FFONT%3E%20is%20not%20consecutive%20so%20I%20can't%20do%20a%20pull-down%20on%20all%20the%20rows.%20I%20have%20the%20value%2C%20in%20this%20row%2C%20of%20'%3CFONT%20color%3D%22%23FF00FF%22%3E2%3C%2FFONT%3E'%20in%20column%20H5.%20The%20value%20in%20column%20H%20will%20be%20different%20for%20each%20row.%20What%20is%20the%20syntax%20to%20place%20the%20value%20in%20column%20H5%20in%20this%20formula%20instead%20of%20hard%20coding%20it%20for%20each%20row.%20I've%20tried%20the%20following%20but%20none%20seem%20to%20work%3A%3C%2FP%3E%3CP%3E%22Data!%24G%24%26amp%3BH5%26amp%3B%22%2F%22%3A%22%26amp%3B%24R%24%22%26amp%3BH5%22%20and%20about%2010%20other%20variations%20of%20placing%20the%20quotes%20and%20%26amp%3B%20and%20I%20have%20finally%20kicked%20the%20cat%20for%20not%20helping%20me!%20Note%20that%20the%20VLOOKUP%20is%20returning%20the%20value%20I%20want%20to%20put%20in%20the%20array%20but%20I%20got%20even%20more%20tangled%20up%20trying%20to%20add%20the%20VLOOKUP%20twice%20in%20the%20array!!!!%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1091634%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1091776%22%20slang%3D%22en-US%22%3ERe%3A%20Confused%20by%20'%20%22%20and%20%26amp%3B%20in%20constructing%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460090%22%20target%3D%22_blank%22%3E%40DCBanks%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20attach%20your%20sample%20Excel%20file%20with%20manually%20entered%20results%20and%20the%20logic%20of%20each.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1092276%22%20slang%3D%22en-US%22%3ERe%3A%20Confused%20by%20'%20%22%20and%20%26amp%3B%20in%20constructing%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1092276%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20the%20full%20book.%20TSB%20is%20the%20income%20and%20expenditure.%20Each%20row%20has%20an%20analysis%20code%20(col%20H)%20and%20a%20budget%20code%20(col%20T).%20The%20Budget%20sheet%20consolidates%20various%20combinations%20of%20analysis%20and%20budget%2C%20to%20analyse%20the%20whole%20income%20and%20expenditure%20over%20individual%20combination%20of%20codes%20would%20would%20be%20OTT.%3C%2FP%3E%3CP%3EEach%20row%20of%20the%20budget%20sheet%20consolidates%20analysis%20and%20budget%20codes%20using%20the%20data%20in%20the%20data%20sheet.%3C%2FP%3E%3CP%3EIf%20you%20look%20at%20row%2020%20of%20the%20budget%20sheet%20the%20figure%20of%20%C2%A3394.93%20is%20a%20consolidation%20of%20all%20'running'%20costs%20over%26nbsp%3B%206%20analysis%20codes.%20It%20does%20this%20by%20looking%20up%20in%20the%20data%20sheet%20G10%3AM10%20and%20the%20analysis%20codes%20are%20IN%2C%20AD%2C%20PO%2C%20SO%2C%20OF%2C%20ST%20and%20the%20budget%20code%20is%20RUN.%20I%20could%20hard%20code%20each%20row%20in%20the%20budget%20sheet%20formula%20(see%20example%20of%20this%20in%20the%20P%20%26amp%3B%20L%20sheet)%20but%20I%20want%20to%20let%20different%20clients%20change%20what%20each%20budget%20figure%20should%20contain.%20In%20the%20Data%20sheet%20I%20have%20indicated%20which%20row%20it%20should%20use%2C%20N10%20in%20that%20sheet%20tells%20me%20the%20row%20on%20the%20budget%20sheet%20is%20row%2020.%20I%20have%20also%20reversed%20this%2C%20H20%20in%20the%20budget%20sheet%20tells%20me%20it%20is%20row%2010%20on%20the%20Data%20sheet.%20I%20want%20the%20value%20in%20H20%20in%20the%20formula%20in%20Budget!D20%2C%20you%20will%20note%20the%20row%20is%20absolute.%20I%20want%20it%20to%20pick%20up%20the%20value%20in%20H20%20and%20put%20it%20in%20the%20Data!%24G10%3A%24R10%20part%20of%20that%20formula.%20This%20is%20a%20good%20example%2C%20obviously%20I%20will%20start%20at%20row%205%20of%20the%20budget%20sheet%20and%20pull%20down.%20Hope%20that%20helps%20(maybe%20drive%20you%20nuts%20but....)%3C%2FP%3E%3CP%3EDavid%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi

I have a formula in D5: (and subsequent rows)

=SUMPRODUCT(SUMIFS(Expend,**bleep**,Data!$G$2:$R$2,Budget,INDIRECT("Data!M$"&VLOOKUP(ROW(),CodeMatch,2,FALSE))))

There are about 100 lines in the sheet but the reference to $G$2:$R$2 is not consecutive so I can't do a pull-down on all the rows. I have the value, in this row, of '2' in column H5. The value in column H will be different for each row. What is the syntax to place the value in column H5 in this formula instead of hard coding it for each row. I've tried the following but none seem to work:

"Data!$G$&H5&"/":"&$R$"&H5" and about 10 other variations of placing the quotes and & and I have finally kicked the cat for not helping me! Note that the VLOOKUP is returning the value I want to put in the array but I got even more tangled up trying to add the VLOOKUP twice in the array!!!!

Thanks in advance.

 

2 Replies
Highlighted

@DCBanks 

Please attach your sample Excel file with manually entered results and the logic of each. 

Highlighted

@Twifoo 

Attached is the full book. TSB is the income and expenditure. Each row has an analysis code (col H) and a budget code (col T). The Budget sheet consolidates various combinations of analysis and budget, to analyse the whole income and expenditure over individual combination of codes would would be OTT.

Each row of the budget sheet consolidates analysis and budget codes using the data in the data sheet.

If you look at row 20 of the budget sheet the figure of £394.93 is a consolidation of all 'running' costs over  6 analysis codes. It does this by looking up in the data sheet G10:M10 and the analysis codes are IN, AD, PO, SO, OF, ST and the budget code is RUN. I could hard code each row in the budget sheet formula (see example of this in the P & L sheet) but I want to let different clients change what each budget figure should contain. In the Data sheet I have indicated which row it should use, N10 in that sheet tells me the row on the budget sheet is row 20. I have also reversed this, H20 in the budget sheet tells me it is row 10 on the Data sheet. I want the value in H20 in the formula in Budget!D20, you will note the row is absolute. I want it to pick up the value in H20 and put it in the Data!$G10:$R10 part of that formula. This is a good example, obviously I will start at row 5 of the budget sheet and pull down. Hope that helps (maybe drive you nuts but....)

David