Calculating a column with another sheet

**mstieler** · 12-15-2011, 09:16 AM

I've got a decently-large-sized personal project ahead, and I'm hoping someone can make this much simpler than what I've been doing.

I have 2 sheets (well, 4 in the Excel file, but only 2 being utilized for this particular bit):
Sheet 1 has Names and quantities
Sheet 2 has amounts, with each column as a different date, with an updated amount as time rolls on.

What I'm trying to do is enter amounts on Sheet 2, and have each Name that matches that populate on Sheet 1.

For example:

Sheet 1:
Item Alpha : 5 : [blank]
Item Beta : 7 : [blank]
Item Gamma: 14 : [blank]
Item Alpha: 12 : [blank]
Item Gamma: 3 : [blank]
Item Beta: 4 : [blank]
Item Gamma: 25 : [blank]

Sheet 2:
Item Alpha : 8.00
Item Beta : 19.50
Item Gamma: 4.25

What I'd like is those [blank] to be filled with the product of the two numbers, so Sheet 1 would look like so:
Item Alpha : 5 : 40.00
Item Beta : 7 : 136.50
Item Gamma: 14 : 59.50
Item Alpha: 12 : 60.00
Item Gamma: 3 : 12.75
Item Beta: 4 : 78.00
Item Gamma: 25 : 106.25

I don't mind a copy/paste bit, as some of the names, while all meaning the same, were entered oddly. However, when I try to copy/paste normally (i.e. "=B4*Sheet 2!B17" ) it replaces B4 & B17 (in this case) to C4 & C17, D4 & D17, etc. instead of staying B4 and just changing B17 to C17, D17, etc.

So I'm not 100% sure just how I could get this to work nicely (or even at all) but I'm hopeful.

Thanks for the possible help or at the very least contemplating this situation.

**NBVC** · 12-15-2011, 09:31 AM

Try:

=B4*Vlookup(A4,Sheet2!$A$2:$B$4,2,FALSE)

Assuming your table in sheet2 is in Sheet2!$A$2:$B$4

then copy down

**mstieler** · 12-20-2011, 03:05 AM

Okay, an update:

I'm not 100% certain how the VLOOKUP function works, so I'm not sure just what would need to go where. I guess the problem could stem from some of the text (as above) being like:

Item Alpha : 5 : [blank]
Item Beta : 7 : [blank]
Item Gamma: 14 : [blank]
12 Alpha : [blank]
3 Gamma : [blank]
4 Item B : [blank]
25 Item G : [blank]

Unless there's something I'm completely missing with VLOOKUP, I'm not certain just how it would work. I'm working on getting the names to be more uniform; the names are all in Column A, quantities in column B, with the formulae for multiplication from the other sheet in columns D-K. On the other sheet, names are in column A, with the amounts being pulled to the other sheet in columns B-I (B to D, C to E, and so forth). One thought I had that could possibly make this easier:

Can I copy/paste formulas that only one part changes? Example:

=B4*$Page 2.B17 [copy]
=B4*$Page 2.C17 [when pasted to the next column over]

as opposed to what currently happens:

=B4*$Page 2.B17 [copy]
=C4*$Page 2.C17 [when pasted to the next column over]

Is this even possible, or just something I'm overlooking?

**abousetta** · 12-20-2011, 03:17 AM

It would be much easier to visualize if you could post a dummy workbook and specificially show what you are after by manually filling in some of the cells that you want the formula to populate and explain how you got those values.

abousetta

**mstieler** · 12-20-2011, 04:01 AM

Makes sense to do that, duh.

I've already got the formulas set for the "Vertical + Update" sheet, column D; they look as such:

=B4*Items!B17

Substituting that row's number for "B4", and that item's listing for the corresponding column on the "Items" sheet. The item descriptions here are arbitrary; I named them simply to correspond with their row numbers on the "Items" sheet.

I also have all the formulas filled out for "Vertical + Update", Row 4.

What I'd like is for each column on "Vertical + Update" to work from the above formula. The dates at the top of the columns in both sheets should show which numbers from "Items" go where on "Vertical + Update" (numbers from "31 Dec" to the "12/31" column, and so on).

I didn't upload the whole thing (2300 rows worth....) but this should give you an idea of what I'm working with, and hopefully how to get this blasted thing functioning well.

If more info is needed, please let me know.

**NBVC** · 12-20-2011, 08:44 AM

In D4:

=IF($A4="","",B4*VLOOKUP($A4,Items!$A$2:$I$87,COLUMNS($C$1:D$1),FALSE))

copied down and across the columns.

**mstieler** · 12-21-2011, 04:32 AM

Originally Posted by NBVC

In D4:

=IF($A4="","",B4*VLOOKUP($A4,Items!$A$2:$I$87,COLUMNS($C$1:D$1),FALSE))

copied down and across the columns.

Awesome! That looks like it's going to work perfectly. Changing title to "Solved" :D

And bonus, it even helps me to root out item names I missed setting to fit the rest.