Enter a value to change other cell values to match that throughout multiple worksheets

**swiftworks** · 01-14-2013, 03:07 PM

Hello,

Insert all the values from the workbooks into master(sheet 1), and delete all duplicates is the plan. I've been trying to figure out a way to have Sheet 1 be a master file and be able to put a value in a cell (like done) & search column a(part number), and have it reflect every other value in the rest of the workbook. Then once that value changes in the rest of the workbook have conditional formatting highlight a certain color. I know how to do the conditional part, but not the first part.
.

sheet 1

column a column b
12345 done
12346 not done

sheet 2
reflect 12345 being done
reflect 12346 not being done

sheet 3
& so on

sheet 4
& so on

I can post an attachment if needed

**oeldere** · 01-14-2013, 03:19 PM

for the first part (get all data in sheet Master).

I used the macro below.

Please Login or Register  to view this content.

**swiftworks** · 01-14-2013, 03:32 PM

I went ahead and deleted the duplicates on the master. I would like to have Column C have an entered value (Released or In Design) then have it search the following sheets for column a (the part number) and replace the value in the other sheets to match if they are done or not.

**oeldere** · 01-14-2013, 03:36 PM

You have to give more information, when the data needs to show up. (Released or In Design)

Mabye you can add it in your file (post the desired result in your Master-sheet).

I noticed you deleted column A (which is the name of the sheet).

You don't want to know where the data is located?

**swiftworks** · 01-14-2013, 03:52 PM

I have each sheet done individually right now as whether or not the drawing (which is what this info is for) is released or in design. I would like the master (sheet 1) file status (column c) to populate the status in the other sheets (manually entered on the other sheets as of now) if it can find the part number in that worksheet. What i did for example is put a value in sheet one cell C2 (Released Document), i want the released document to populate anywhere else in the file it could find. For example i deleted column C in sheet 2 and added 10016 to the data. I'd like it to say released document or turn green because the master file represents that the file is completed. For this sheet and all other sheets that the 10016 is in. Each sheet represents a different product.

**swiftworks** · 01-14-2013, 03:59 PM

It doesn't even need to transfer the Released or In design, maybe if it just looked up 10016 in the master, searched the other sheets to find it or not. if it did, it would then look up the value of in design or released and based on the master data, highlight it Green (done) or red ( in design).

**oeldere** · 01-14-2013, 04:09 PM

If you have all data in the MASTER sheet, why would you like to change other sheets.

Why not use the MASTER sheet for counting etc.

**swiftworks** · 01-14-2013, 04:11 PM

I would think it would be easier to sort out per project. just find the project your looking for and boom. We could use filter and sort by that, but I believe it is a cleaner way of presenting data to a department.

**oeldere** · 01-14-2013, 04:18 PM

I don't understand what your up to.

Maybe you can get more information with an pivot table.

See the attached file.

**swiftworks** · 01-14-2013, 04:30 PM

Basically I took this very simple here, maybe this might help my confusing problem.

I want the value of the cell(column B master sheet) to be used to populate column B in the rest of the sheets with the same status. If it would be easier to just change the row color to red or green based on the master status, that would work as well.

**dredwolf** · 01-14-2013, 04:35 PM

Try this in C1 of your sub sheets :

Formula:

Please Login or Register  to view this content.

drag down
you may want to enlarge the Blue sections of the above to allow extra items as well

Hope this helps

**oeldere** · 01-14-2013, 04:35 PM

OK, but once again, why don't (e.g. filter or pivot table) on the master sheet.

It much easier to work with data if all data are in 1 sheet.

**swiftworks** · 01-14-2013, 04:41 PM

when i input that data into C1 on sheet 2, nothing populates, did you try it on the wb?

**swiftworks** · 01-14-2013, 04:44 PM

I am not too sure on pivot tables and with the filter, it wont take one value and populate all the values accordingly. It does however state what goes on where, but this data will often change and having a master file would be better.

**dredwolf** · 01-14-2013, 04:45 PM

Yes I did, but it was the previous one, not your latest..give me a few

**dredwolf** · 01-14-2013, 04:50 PM

For your NEW upload in B2 of second sheet:

Formula:

Please Login or Register  to view this content.

Drag down

**swiftworks** · 01-14-2013, 05:05 PM

Awesome, worked for the basic version. I tried to input the first formula into the attached WB and I did not get the same thing though? I must be inserting it into the wrong cell. I just get a blank value in them

**swiftworks** · 01-14-2013, 05:18 PM

hey nevermind i figured it out thanks a lot mayne

**dredwolf** · 01-14-2013, 05:28 PM

The problem with your upload is you only have 2 items with status descriptions so this will return blanks for all other items, also..your first sub sheet has headers, while all the rest do not, will they?
If so, I would put them in(or at least insert a blank row at the top) before entering the formula into them

**dredwolf** · 01-14-2013, 05:30 PM

You are very welcome

Enter a value to change other cell values to match that throughout multiple worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Re: Enter a value to change other cell values to match that throughout multiple worksheets

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions