Formula Referencing Cells in Different Sheets

Cowbilly

New Member
Joined
May 24, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am a self-taught Excel user so I have no formal formula training. What I am trying to accomplish is this (if it can be done), highlight a cell in Sheet 1 of a workbook if a cell in Sheet 2 contains any text (such as an "X").

Sheet 1 will be a snapshot of tasks accomplished/not accomplished. If the cell next to the task name is highlighted, then the task is accomplished.
Sheet 2 (and subsequent sheets) are individual tasks further broken down by sub-tasks. I plan to place an "X" in a cell once a sub-task is complete.

Once an "X" is placed next to the sub-task on Sheet 2, Sheet 3, Sheet 4, etc., I should see it reflected at a glance on Sheet 1. Is this possible?

Thanks in advance for time and assistance!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
140
Office Version
  1. 365
Platform
  1. Windows
This is not so much a matter of Excel's capability, but how this problem is to be solved.

Let's say in your worksheets...
Sheet-1 contains Task Name
Sheet-2 also contains Task Name, also a sub Task Name
Sheet-3...as Sheet-2 above

Set up hidden indicators
You can set up hidden indicators in Sheet-2 and other sheets.
(how this is set up depends on how your worksheet is set up)

vLookUp
With these hidden indicators, you can vLookup from Sheet-1 to look up the hidden indicators.
If you get a positive result, write something in the Sheet-1 cell (that you want to show a color).

Conditional Formatting
Then you can use conditional formatting to show a desired color if the cell is not blank.

Hiding the vLookUp result in Sheet-1
If you do not want to see the vLookUp results, you can format the cells to hide the content.
Select the cells (or column) that you want to hide any text, then choose Format -> Custom Format -> and enter three semicolons ( ; ; ; )
CustomFormat.jpg


Show us a sample
Can you show us how your spreadsheet looks like?
 

Cowbilly

New Member
Joined
May 24, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you for the response Larry. I will try to digest what you submitted when I get a chance. The project I was working on is low priority with a due date still a couple of months away while I currently have higher priority projects with closer due dates. I don't have the spreadsheet built yet so I cannot attach a copy. I was trying to think, in advance, about how it should be done, which is what prompted the question.
Thanks again so very much!
 

Forum statistics

Threads
1,144,629
Messages
5,725,381
Members
422,622
Latest member
Paranthem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top