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.
[TABLE="width: 891"]
<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>[TR]
[TD="class: xl1601, width: 106"]Account[/TD]
[TD="class: xl1595, width: 189"]Description[/TD]
[TD="class: xl1382, width: 80"]January Actual[/TD]
[TD="class: xl1379, width: 80"]February Actual[/TD]
[TD="class: xl1370, width: 80"]March Actual[/TD]
[TD="class: xl1370, width: 80"]April Actual[/TD]
[TD="class: xl1370, width: 80"]May Actual[/TD]
[TD="class: xl1370, width: 91"]June Actual[/TD]
[TD="class: xl1370, width: 105"]July Actual[/TD]
[/TR]
[TR]
[TD="class: xl1602, align: right"]610200[/TD]
[TD] SALARIES MFG SUPERVISION[/TD]
[TD="class: xl3225"]$20,026[/TD]
[TD="class: xl3225"]$18,691[/TD]
[TD="class: xl3225"]$23,719[/TD]
[TD="class: xl3225"]$19,112[/TD]
[TD="class: xl3225"]$23,822[/TD]
[TD="class: xl3225"]$21,036[/TD]
[TD="class: xl3225"]$17,679[/TD]
[/TR]
</tbody>[/TABLE]



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
[TABLE="width: 891"]
<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>[TR]
[TD="class: xl1601, width: 106"]Account[/TD]
[TD="class: xl1595, width: 189"]Description[/TD]
[TD="class: xl1382, width: 80"]January Actual[/TD]
[TD="class: xl1379, width: 80"]February Actual[/TD]
[TD="class: xl1370, width: 80"]March Actual[/TD]
[TD="class: xl1370, width: 80"]April Actual[/TD]
[TD="class: xl1370, width: 80"]May Actual[/TD]
[TD="class: xl1370, width: 91"]June Actual[/TD]
[TD="class: xl1370, width: 105"]July Actual[/TD]
[/TR]
[TR]
[TD="class: xl1602, align: right"]610200[/TD]
[TD] SALARIES MFG SUPERVISION[/TD]
[TD="class: xl3225"]$20,026[/TD]
[TD="class: xl3225"]$18,691[/TD]
[TD="class: xl3225"]$23,719[/TD]
[TD="class: xl3225"]$19,112[/TD]
[TD="class: xl3225"]$23,822[/TD]
[TD="class: xl3225"]$21,036[/TD]
[TD="class: xl3225"]$17,679[/TD]
[/TR]
[TR]
[TD="class: xl1602, align: right"]610410[/TD]
[TD] MFG WAGES DIRECT[/TD]
[TD="class: xl3225"]$43,926[/TD]
[TD="class: xl3225"]$31,455[/TD]
[TD="class: xl3225"]$26,105[/TD]
[TD="class: xl3225"]$24,005[/TD]
[TD="class: xl3225"]$51,673[/TD]
[TD="class: xl3225"]$37,760[/TD]
[TD="class: xl3225"]$30,176[/TD]
[/TR]
</tbody>[/TABLE]

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)

[TABLE="width: 824"]
<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>[TR]
[TD="class: xl1601, width: 106"]Account[/TD]
[TD="class: xl1595, width: 189"]Description[/TD]
[TD="class: xl4235, width: 129"]Type A[/TD]
[TD="class: xl4235, width: 80"]Type B[/TD]
[TD="class: xl4235, width: 80"]Type C[/TD]
[TD="class: xl4235, width: 80"]Type D[/TD]
[TD="class: xl4235, width: 80"]Type E[/TD]
[TD="class: xl4235, width: 80"]Type F[/TD]
[/TR]
[TR]
[TD="class: xl1602, align: right"]610200[/TD]
[TD] SALARIES MFG SUPERVISION[/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[/TR]
[TR]
[TD="class: xl1602, align: right"]610410[/TD]
[TD] MFG WAGES DIRECT[/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[TD="class: xl3225"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Account​

[/td][td]

Description​

[/td][td]

January Actual​

[/td][td]

February Actual​

[/td][td]

March Actual​

[/td][td]

April Actual​

[/td][td]

May Actual​

[/td][td]

June Actual​

[/td][td]

July Actual​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

610200​

[/td][td]

SALARIES MFG SUPERVISION​

[/td][td]

$20,026​

[/td][td]

$18,691​

[/td][td]

$23,719​

[/td][td]

$19,112​

[/td][td]

$23,822​

[/td][td]

$21,036​

[/td][td]

$17,679​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

610410​

[/td][td]

MFG WAGES DIRECT​

[/td][td]

$43,926​

[/td][td]

$31,455​

[/td][td]

$26,105​

[/td][td]

$24,005​

[/td][td]

$51,673​

[/td][td]

$37,760​

[/td][td]

$30,176​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Sheet2

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td]

January Actual​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Account​

[/td][td]

Description​

[/td][td]

Type A​

[/td][td]

Type B​

[/td][td]

Type C​

[/td][td]

Type D​

[/td][td]

Type E​

[/td][td]

Type F​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

610200​

[/td][td]

SALARIES MFG SUPERVISION​

[/td][td]

$20,026​

[/td][td]

$20,026​

[/td][td]

$20,026​

[/td][td]

$20,026​

[/td][td]

$20,026​

[/td][td]

$20,026​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

610410​

[/td][td]

MFG WAGES DIRECT​

[/td][td]

$43,926​

[/td][td]

$43,926​

[/td][td]

$43,926​

[/td][td]

$43,926​

[/td][td]

$43,926​

[/td][td]

$43,926​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.