Mixing
Search workbook for matching texts and replace value in all adjacent columns
Kinda newb in excel so I could appreciate your help.
So, right now I have a workbook with 49 sheets with different bank renovation quotations to give.
Some of the item descriptions are equal throughout the whole workbook and some are unique.
What I wanted was an excel formula that could let me chose a price for one item, lets say "wall construction" of 10,00$ and that would replicate for all the same description items in all the sheets.
Each item is in a separate line and the prices are 3 columns to the right of the item description (that would be the search term).
I think if there's a way to search for all cells with the same text and replace the cell 3 columns to the right that would do the job.
Unfortunately I've been playing with vlookup, index etc for a while but I need your help.
excel search replace
asked Jan 1 at 22:16
user3007089user3007089
113
add a comment |
Kinda newb in excel so I could appreciate your help.
So, right now I have a workbook with 49 sheets with different bank renovation quotations to give.
Some of the item descriptions are equal throughout the whole workbook and some are unique.
What I wanted was an excel formula that could let me chose a price for one item, lets say "wall construction" of 10,00$ and that would replicate for all the same description items in all the sheets.
Each item is in a separate line and the prices are 3 columns to the right of the item description (that would be the search term).
I think if there's a way to search for all cells with the same text and replace the cell 3 columns to the right that would do the job.
Unfortunately I've been playing with vlookup, index etc for a while but I need your help.
excel search replace
asked Jan 1 at 22:16
user3007089user3007089
113
So there is one worksheet that has a unique master list of all of the items listed on all of the other 49 worksheets and you want to type in a new price for an item and have that price distributed to all 49 worksheets that contain that item. Is that an honest and accurate summary of what you want to do?
– user10852207
Jan 1 at 22:44
It can't be done with a formula. VBA is the tool you need. Can you accept that as an answer?
– Andreas
Jan 1 at 22:44
@Andreas - why can't this be done with a simple vlookup?
– user10852207
Jan 2 at 0:34
@user10852207 because vlookup only looks up values it can't replace values. There is no formula that can replace a value in another cell
– Andreas
Jan 2 at 7:39
add a comment |
Kinda newb in excel so I could appreciate your help.
So, right now I have a workbook with 49 sheets with different bank renovation quotations to give.
Some of the item descriptions are equal throughout the whole workbook and some are unique.
What I wanted was an excel formula that could let me chose a price for one item, lets say "wall construction" of 10,00$ and that would replicate for all the same description items in all the sheets.
Each item is in a separate line and the prices are 3 columns to the right of the item description (that would be the search term).
I think if there's a way to search for all cells with the same text and replace the cell 3 columns to the right that would do the job.
Unfortunately I've been playing with vlookup, index etc for a while but I need your help.
excel search replace
asked Jan 1 at 22:16
user3007089user3007089
113
Kinda newb in excel so I could appreciate your help.
So, right now I have a workbook with 49 sheets with different bank renovation quotations to give.
Some of the item descriptions are equal throughout the whole workbook and some are unique.
What I wanted was an excel formula that could let me chose a price for one item, lets say "wall construction" of 10,00$ and that would replicate for all the same description items in all the sheets.
Each item is in a separate line and the prices are 3 columns to the right of the item description (that would be the search term).
I think if there's a way to search for all cells with the same text and replace the cell 3 columns to the right that would do the job.
Unfortunately I've been playing with vlookup, index etc for a while but I need your help.
excel search replace
excel search replace
asked Jan 1 at 22:16
user3007089user3007089
113
asked Jan 1 at 22:16
user3007089user3007089
113
asked Jan 1 at 22:16
user3007089user3007089
113
asked Jan 1 at 22:16
user3007089user3007089
113
asked Jan 1 at 22:16
user3007089user3007089
113
113
So there is one worksheet that has a unique master list of all of the items listed on all of the other 49 worksheets and you want to type in a new price for an item and have that price distributed to all 49 worksheets that contain that item. Is that an honest and accurate summary of what you want to do?
– user10852207
Jan 1 at 22:44
It can't be done with a formula. VBA is the tool you need. Can you accept that as an answer?
– Andreas
Jan 1 at 22:44
@Andreas - why can't this be done with a simple vlookup?
– user10852207
Jan 2 at 0:34
@user10852207 because vlookup only looks up values it can't replace values. There is no formula that can replace a value in another cell
– Andreas
Jan 2 at 7:39
add a comment |
So there is one worksheet that has a unique master list of all of the items listed on all of the other 49 worksheets and you want to type in a new price for an item and have that price distributed to all 49 worksheets that contain that item. Is that an honest and accurate summary of what you want to do?
– user10852207
Jan 1 at 22:44
It can't be done with a formula. VBA is the tool you need. Can you accept that as an answer?
– Andreas
Jan 1 at 22:44
@Andreas - why can't this be done with a simple vlookup?
– user10852207
Jan 2 at 0:34
@user10852207 because vlookup only looks up values it can't replace values. There is no formula that can replace a value in another cell
– Andreas
Jan 2 at 7:39
So there is one worksheet that has a unique master list of all of the items listed on all of the other 49 worksheets and you want to type in a new price for an item and have that price distributed to all 49 worksheets that contain that item. Is that an honest and accurate summary of what you want to do?
– user10852207
Jan 1 at 22:44
So there is one worksheet that has a unique master list of all of the items listed on all of the other 49 worksheets and you want to type in a new price for an item and have that price distributed to all 49 worksheets that contain that item. Is that an honest and accurate summary of what you want to do?
– user10852207
Jan 1 at 22:44
It can't be done with a formula. VBA is the tool you need. Can you accept that as an answer?
– Andreas
Jan 1 at 22:44
It can't be done with a formula. VBA is the tool you need. Can you accept that as an answer?
– Andreas
Jan 1 at 22:44
@Andreas - why can't this be done with a simple vlookup?
– user10852207
Jan 2 at 0:34
@Andreas - why can't this be done with a simple vlookup?
– user10852207
Jan 2 at 0:34
@user10852207 because vlookup only looks up values it can't replace values. There is no formula that can replace a value in another cell
– Andreas
Jan 2 at 7:39
@user10852207 because vlookup only looks up values it can't replace values. There is no formula that can replace a value in another cell
– Andreas
Jan 2 at 7:39
add a comment |
1 Answer
1
active
oldest
votes
First create a "Master List" tab that will serve as your Quotebook with the service in column A and the price in column B.
Assuming your quote sheets are set up with a list of services needed in column A put the following formula in column B:
=VLOOKUP(A1,Masterlist!A$2:B$90,2,False)
A1 = The cell with the service you need to price
Masterlist!A$2:B$90 = The range of your Quotebook
answered Jan 2 at 16:53
Emily AldenEmily Alden
407213
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53999367%2fsearch-workbook-for-matching-texts-and-replace-value-in-all-adjacent-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
First create a "Master List" tab that will serve as your Quotebook with the service in column A and the price in column B.
Assuming your quote sheets are set up with a list of services needed in column A put the following formula in column B:
=VLOOKUP(A1,Masterlist!A$2:B$90,2,False)
A1 = The cell with the service you need to price
Masterlist!A$2:B$90 = The range of your Quotebook
answered Jan 2 at 16:53
Emily AldenEmily Alden
407213
add a comment |
First create a "Master List" tab that will serve as your Quotebook with the service in column A and the price in column B.
Assuming your quote sheets are set up with a list of services needed in column A put the following formula in column B:
=VLOOKUP(A1,Masterlist!A$2:B$90,2,False)
A1 = The cell with the service you need to price
Masterlist!A$2:B$90 = The range of your Quotebook
answered Jan 2 at 16:53
Emily AldenEmily Alden
407213
add a comment |
First create a "Master List" tab that will serve as your Quotebook with the service in column A and the price in column B.
Assuming your quote sheets are set up with a list of services needed in column A put the following formula in column B:
=VLOOKUP(A1,Masterlist!A$2:B$90,2,False)
A1 = The cell with the service you need to price
Masterlist!A$2:B$90 = The range of your Quotebook
answered Jan 2 at 16:53
Emily AldenEmily Alden
407213
First create a "Master List" tab that will serve as your Quotebook with the service in column A and the price in column B.
Assuming your quote sheets are set up with a list of services needed in column A put the following formula in column B:
=VLOOKUP(A1,Masterlist!A$2:B$90,2,False)
A1 = The cell with the service you need to price
Masterlist!A$2:B$90 = The range of your Quotebook
answered Jan 2 at 16:53
Emily AldenEmily Alden
407213
answered Jan 2 at 16:53
Emily AldenEmily Alden
407213
answered Jan 2 at 16:53
Emily AldenEmily Alden
407213
answered Jan 2 at 16:53
Emily AldenEmily Alden
407213
407213
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53999367%2fsearch-workbook-for-matching-texts-and-replace-value-in-all-adjacent-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So there is one worksheet that has a unique master list of all of the items listed on all of the other 49 worksheets and you want to type in a new price for an item and have that price distributed to all 49 worksheets that contain that item. Is that an honest and accurate summary of what you want to do?
– user10852207
Jan 1 at 22:44
It can't be done with a formula. VBA is the tool you need. Can you accept that as an answer?
– Andreas
Jan 1 at 22:44
@Andreas - why can't this be done with a simple vlookup?
– user10852207
Jan 2 at 0:34
@user10852207 because vlookup only looks up values it can't replace values. There is no formula that can replace a value in another cell
– Andreas
Jan 2 at 7:39