Matt Pinkston
asked on
Need an excel formula that looks a multiple tabs of a workbook
I have an excel spreadsheet that has two tabs
Customers
Line Item
In Customers column M is a dropdown of values from column A in Line Item
When I select an Item from the drop down I want to multiply the corresponding column B from Line Item by Column J of the current line in Customers and the result will be where the formula was in Column N Customers
Customers
Line Item
In Customers column M is a dropdown of values from column A in Line Item
When I select an Item from the drop down I want to multiply the corresponding column B from Line Item by Column J of the current line in Customers and the result will be where the formula was in Column N Customers
ASKER
formula should end up being
Column J * Column B from Line Item Sheet where Column A Line Item Sheet = Column M from Customers
Column J * Column B from Line Item Sheet where Column A Line Item Sheet = Column M from Customers
I'm not following well enough.
Are you able to write that formula if everything were on the same sheet?
Are you able to write that formula if everything were on the same sheet?
ASKER
they are not on same sheet
Sheet1 - "Customers"
Column J - Number of Subscriptions
Column M - Product (Drop Down Selection)
Column N - Formula I need
Sheet 2 - "Line Item"
Column A - Product
Column B - Price
Find match for Sheet1.Column M in Sheet2.Column A
Multiply Sheet1.Column J by Sheet2.Column B
Sheet1 - "Customers"
Column J - Number of Subscriptions
Column M - Product (Drop Down Selection)
Column N - Formula I need
Sheet 2 - "Line Item"
Column A - Product
Column B - Price
Find match for Sheet1.Column M in Sheet2.Column A
Multiply Sheet1.Column J by Sheet2.Column B
"they are not on same sheet "
I understand that. I'm trying to sort out if your issue is with how to write the basic formula or if it is an issue with looking at a different tab.
"Find match for Sheet1.Column M in Sheet2.Column A ": the VLookup function would be the useful one here to find the match and to retrieve the corresponding ColumnB.
I understand that. I'm trying to sort out if your issue is with how to write the basic formula or if it is an issue with looking at a different tab.
"Find match for Sheet1.Column M in Sheet2.Column A ": the VLookup function would be the useful one here to find the match and to retrieve the corresponding ColumnB.
If you'd provide a sample spreadsheet with sample data you'd be much more likely to get a more detailed answer here.
Scenario:
You choose items in column A (options from list on other sheet). When an item is chosen you also need a value from that list populated into the neighbouring cell, column B.
Column N of the sheet will then be column J times column B
Suggestion:
Column B can be a lookup of the item in column A
You choose items in column A (options from list on other sheet). When an item is chosen you also need a value from that list populated into the neighbouring cell, column B.
Column N of the sheet will then be column J times column B
Suggestion:
Column B can be a lookup of the item in column A
ASKER
I was looking for help with the formula itself not sure of the syntax
ASKER
2021 August Pricing Strategy.xlsx
Perhaps adding the test file will help
I need help building a formula for tab Customer column N
when column M in that same tab is selected a lookup to tab Line Item should look for a match in Line Item column A
then take the amount from the matching row column B and multiple it by tab Customers column J
Perhaps adding the test file will help
I need help building a formula for tab Customer column N
when column M in that same tab is selected a lookup to tab Line Item should look for a match in Line Item column A
then take the amount from the matching row column B and multiple it by tab Customers column J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PERFECT!!!!!
If you want to reference cell b3 on the second tab (named Sheet2) just use Sheet2!B3 as the reference to the cell. Otherwise, treat it as you would any other cell.
Is that what you are needing?