Similar functionality as SUMIF

[jcountry22]

Sheet 1: is set up as follows with ACCOUNT is cell A1.. I have other accounts following 610200 going down from A2 to A59 with the description and amounts as seen below in other cells.

Account	Description	January Actual	February Actual	March Actual	April Actual	May Actual	June Actual	July Actual
610200	SALARIES MFG SUPERVISION	$20,026	$18,691	$23,719	$19,112	$23,822	$21,036	$17,679

<colgroup><col width="106" style="width:80pt"> <col width="189" style="width:142pt"> <col width="80" style="width:60pt" span="5"> <col width="91" style="width:68pt"> <col width="105" style="width:79pt"> </colgroup><tbody>
</tbody>

Sheet 2: i have the accounts and description set up the same just a row down.. From C# H# i have different product lines. Above those I have a drop down menu with the options of: Jan Actual, Feb Actual, Mar Actual, etc (from Sheet 1)

to start -- I'm trying to only have the numbers from sheet 1 populate on sheet 2 for the selected month from the drop down menu.

Example. On sheet 2, i select Jan. actual. I want that $20,026 to show up in all of my product line cells (from C#-H#). I want to be able to easily change the drop down and have all my # change to the corresponding # from sheet 1.

This is similar to a SUMIF functionality, i just can't seem to get it to work. Any help would be greatly appreciated.

 

[Marcelo Branco]

Maybe something like this (not tested)

Formula in C3 copied across and down
=VLOOKUP($A3,Sheet1!$A$2:$Z$59,MATCH($A$1,Sheet1!$A$1:$Z$1,0),0)
Assumes
Data in Sheet2 begin in row 3
Dropdown in A1 (adjust to suit)

Hope this helps

M.

 

[jcountry22]

I'm not quite sure on =Vlookup($A3 --- which sheet is this suppose to be reading from?

Below are the exact setups on my worksheet per tab.

Sheet1: Starts in A6

Account	Description	January Actual	February Actual	March Actual	April Actual	May Actual	June Actual	July Actual
610200	SALARIES MFG SUPERVISION	$20,026	$18,691	$23,719	$19,112	$23,822	$21,036	$17,679
610410	MFG WAGES DIRECT	$43,926	$31,455	$26,105	$24,005	$51,673	$37,760	$30,176

<colgroup><col width="106" style="width:80pt"> <col width="189" style="width:142pt"> <col width="80" style="width:60pt" span="5"> <col width="91" style="width:68pt"> <col width="105" style="width:79pt"> </colgroup><tbody>
</tbody>

Sheet 2: C5 contains my drop down menu, C6 is = to C5 and text below starts in A7: (TYPES A-F are different product lines)

Account	Description	Type A	Type B	Type C	Type D	Type E	Type F
610200	SALARIES MFG SUPERVISION
610410	MFG WAGES DIRECT

<colgroup><col width="106" style="width:80pt"> <col width="189" style="width:142pt"> <col width="129" style="width:97pt"> <col width="80" style="width:60pt" span="5"> </colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Questions
Where exactly is the dropdown with options January Actual, February Actual, ...? C5 or C6?
What you mean by
"C5 contains my drop down menu, C6 is = to C5..."

M.

 

[jcountry22]

drop down menu is on Sheet2, cell C5

I made cell C6 = to C5, so my formulas starting in C8 can read from from C6 as opposed to the drop down menu section. I guess either one would work. I just wasn't sure if it would be more complex to read directly from the drop down menu.

 

[Marcelo Branco]

This worked for me

Sheet1

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​
2​
3​
4​
5​
6​	Account​	Description​	January Actual​	February Actual​	March Actual​	April Actual​	May Actual​	June Actual​	July Actual​
7​	610200​	SALARIES MFG SUPERVISION​	$20,026​	$18,691​	$23,719​	$19,112​	$23,822​	$21,036​	$17,679​
8​	610410​	MFG WAGES DIRECT​	$43,926​	$31,455​	$26,105​	$24,005​	$51,673​	$37,760​	$30,176​
9​

Sheet2

	A​	B​	C​	D​	E​	F​	G​	H​
1​
2​
3​
4​
5​
6​			January Actual​
7​	Account​	Description​	Type A​	Type B​	Type C​	Type D​	Type E​	Type F​
8​	610200​	SALARIES MFG SUPERVISION​	$20,026​	$20,026​	$20,026​	$20,026​	$20,026​	$20,026​
9​	610410​	MFG WAGES DIRECT​	$43,926​	$43,926​	$43,926​	$43,926​	$43,926​	$43,926​
10​

Formula in C8 copied across and down
=VLOOKUP($A8,Sheet1!$A$7:$Z$100,MATCH($C$6,Sheet1!$A$6:$Z$6,0),0)

M.

 

[jcountry22]

WORKS FOR ME AS WELL!! Thank you for the help Marcelo

 

[Marcelo Branco]

You are welcome. Glad to help

M.