Forum Discussion
Populating cells with a Date across 12 TABS
- look at both as decimal (or percentage) are they the same or off by 100? It looks like the rate schedule is a true decimal number (i.e. 0.00088 or 0.088%) while the other number is coming in as % units (i.e. 0.088 which if formatted as % would be 8.8%) so basically you need to convert from % UNIT to decimal value.
=(ROUND(K6,3)= ROUND(100*K$3,3))
or BETTER yet change the formula in K3 to be =100*... so
=100*XLOOKUP(LEFT($I5,3),LEFT('Rate Schedule'!$B5:$B16,3),'Rate Schedule'!C5:C16,"error")
Carl_61 Ok since you didn't understand the previous formula I went back and made it even more complicated. lol. This version will fill in each set of 5 columns based on the 'Work Sheet' tab. It uses a lookup so the bldg#s will match up BUT when 2 rows have the same bldg# it will repeat the data for both lines (not sure if that is desired or not). Because some lines have end dates from prior month I created a cell $A$2 that defines what month this sheet is looking at based on the max month date found.
I also broke the TO DO list out of the GO list. As for the formula i will try to break it down here:
=LET(lastrow, COUNTA($A:$A)+ROW($A$5)-COUNTA($A$1:$A$5),
bldgList, $A$6:INDEX($A:$A,lastrow),
cols, {2,3,4},
readInfo, INDEX(worksheet, XMATCH(bldgList,worksheet[Bldg '#]),cols),
newRow, HSTACK(CHOOSECOLS(readInfo,1,2),
INDEX(readInfo,,1)-$I$6:INDEX($I:$I,lastrow) &" kw",
INDEX(readInfo,,2)-$E$6:INDEX($E:$E,lastrow),
TEXT(INDEX(readInfo,,3),"0;0;;@")),
out, IF(INDEX(readInfo,,2)-DAY(INDEX(readInfo,,2))+1<>$A$2,"",newRow),
out)So the function LET will let us create temporary variables. In the case the 1st variable is lastrow and the formula for that is COUNTA($A:$A)+ROW($A$5)-COUNTA($A$1:$A$5) which basically finds the last row that has a blgd number. line 2 is bldgList which just creates a reference to all the cells in column A from row 6 to the last row (i.e. all the bldg numbers). line 3 is just which columns in the table on WorkSheet we are interested in (i.e. gas vs electric vs water). Line 4 creates a list of all the rows from the WorkSheet that correspond to the bldg numbers on this sheet. lines 5:8 create take the 3 pieces of data and makes them into the 5 columns of data. and finally line 9 says if date being pulled from the WorkSheet isn't this month then make it blank, otherwise return all 5 cells for this row.
I will try to look at the macro parts later and for now you can check this update out
