Populating a column with data

[ReportMaster]

On one of my sheet I am trying to get all of the values for a certain column ( say column 2) to populate itself with specific data. The data must be pulled from another sheet depending on the value of a cell in that same row ( lets say this would be all the values in column 1). The value of column 2, which is the name of one of seventy categories, points to a certain cell from another sheet that contains a budget amount.

ex sheet 1.
1 2 (this is where the budget should appear)
category 1
category 35
category 16

then on the other sheet the categories are listed adjacent to their budget.

ex.

category 1 $500
category 2 $300
..and so on

This may appear to be the same thing, but the first sheet has up to 1000 records with other data than was provided. The second sheet also has breaks between some of the sets of categories and two different sets of data running vertically on the sheet, so each category must point to a specific cell.


also, on a related note. The first sheet has a column with a money amount. I am trying program a colum that shows the total af all of the amounts where the category is the same.

Thank you in advance for any help! you guys are lifesavers!!!

 

[PHV]

You can take a look at the VLOOKUP worksheet function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

As long as all your categories and there associated budgets are in two columns, and the budget amounts are to the right of the category column, then you can use a simple VLOOKUP, even if you have breaks in your data on the sheet with category and budget, eg:-

With your 'category 1' etc starting A1 on sheet1, and your lookup data being in Cols A and B, starting A1 on sheet 2 (I'll assume 200 records), then on sheet1 in cell B1 put the following formula and then copy down:-

=VLOOKUP(A1,Sheet2!$A$1:$B$200,2,0)

If the value in Col A is not to be found on sheet2 then this will produce an error, which if you wanted can be handled by amending the formula as follows:-

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$200,2,0)),"",VLOOKUP(A1,Sheet2!$A$1:$B$200,2,0))

As for the last part of your note, take a look at SUMIF, which allows you to specify a range, a criteria and a sum range for records meeting that criteria:-

=sumif(range,criteria,sum_range)

=SUMIF(A1:A1000,"Category 1",B1:B1000)

or with the Category 1 bit in a cell, eg cell H1:-

=SUMIF(A1:A1000,H1,B1:B1000)

For multiple criteria you would be looking at the SUMPRODUCT function

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------