look at a month in every sheet if there is data in the row enter X

[grgrl]

Hello,


i have a summary page with all my electricity bills (company) and when i pay them i put a check mark. I also for every account enter the data in the sheet corresponding to the account and the month. I was wondering if there is a way to make the check mark automatically when the cell next to the month is filled in the appropriate sheet. I know if i do IF(acc123!C32 <>'''';''X'';'''') in the summary page under the month for that account and i fill the cell 32 in the acc123 the X put itself in the summary page but i was wondering if there is a faster way.


i am looking for a way for the summary page to look at the month lets say august in every sheet and if there is data in the row of august the X puts itself if not blank

Thank you in advance


summar.PNG123.PNG456.PNG

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: I did it for you as you are new.Please read forum rules AND the yellow banner on top of the page to add an example sheet

[grgrl]

Thank you sorry i didn't really know what do write that is why i wrote please help. I would of never though the way you wrote it

[Mrrrr]

You could try the formula below:
=IF(INDEX(INDIRECT("'"&$A2&"'!$B$1:$B$12");MATCH(B$1;'123'!$A$1:$A$12;0))>0;"X";"") ---- INCOMPLETE, see below

In the formula above I forgot to also add the INDIRECT part in MATCH (sorry ), so here is the correct formula below:
=IF(INDEX(INDIRECT("'"&$A2&"'!$B$1:$B$12");MATCH(B$1;INDIRECT("'"&$A2&"'!$A$1:$A$12");0))>0;"X";"")

EXCEL_2019-11-05_23-27-31.png

I created sheets 123, 456 and added vertical months just like in your example.
Didn't add values in 123 for april, july and oct-dec, didn't add values in 456 for jan, feb, may, aug-dec.

You can drag it down and right for all your Accounts and months.

I hope this does what you wanted.

Also attached file below

[grgrl]

Thank you for the reply Would you be kind to explain me the following part in your equation &"'!$B$1:$B$12" from the INDIRECT("'"&$A2&"'!$B$1:$B$12")
Sorry newbie

[Mrrrr]

INDIRECT("'"&$A2&"'!$B$1:$B$12") replaces '123'!$B$1:$B$12, so I am breaking them into pieces:

"'"&$A2&"'! --- replaces the sheet name '123'!, so you don't have to always write the sheet name manually on each row (for each Account)
$B$1:$B$12 --- is the range to look at in that sheet, just like it was before when writing that part as '123'!$B$1:$B$12

There are multiple quotation marks, single and double; added spaces to make more visible:
" ' " & $A2 & " ' ! $B$1:$B$12 "
replaces
' 123 ' ! $B$1:$B$12

The double quotation marks are used to specify text that will be written AS IS in the formula and they are used to be able to include the cell reference of $A2 inside the formula.
Sounds a bit complicated, but it's not.

I hope I've explained it properly, sometimes I tend to go too much into details