Indirect Formula

[paulcherianc]

Dear Experts!

Can you transform the below 3 formulas to Indirect method.

1. OT
=SUMIF(Timesheet!M7:AM7,">10")-COUNTIF(Timesheet!M7:AM7,">10")*10

2.HOT=SUM(Timesheet!AQ7:AZ7)

3.Total Days=COUNTIF(Timesheet!M7:AM7,">0")+COUNTIF(Timesheet!AQ7:AZ7,">0")

Please help!

 

[AliGW]

Which bit of the formula will be referred to in a cell? Is it the sheet name?

 

[paulcherianc]

I am keeping this formula in another sheet named "Main Data". Reference Sheet is "Timesheet"

 

[paulcherianc]

References on the formula are changing when I delete any column. So, in order to keep them I hope indirect formula can help me. Is there any other workaround for this issue?

 

[AliGW]

Don't understand, sorry - you are going to need to attach the workbook and give an example of the problem.

 

[paulcherianc]

Hi Ali, I have attached a sample file. Can you please have a look.

 

[AliGW]

Try fixing the arrays like this:

=SUM(Timesheet!$AQ$7:$AZ$7)

 

[paulcherianc]

Sorry it is not working.

 

[AliGW]

OK - sorry, I don't have any other suggestion.

 

[NARAYANK991]

Hi ,

Probably this :

=SUM(INDIRECT("Timesheet!$AQ$7:$AZ$7"))

Narayan

 

[paulcherianc]

For better understanding, I have included the macro button to cut and paste all Fridays to the very end of the document. Then you may notice the changes happening on the formula highlighted on page 2. I need the green ones to be stable.

 

[paulcherianc]

Hi ,

Probably this :

=SUM(INDIRECT("Timesheet!$AQ$7:$AZ$7"))

Narayan

Hello Narayan,

Tried with your Formula. It's working fine. But when I am copying it down the formula references are static. Is that possible to change line by line?

 

[paulcherianc]

OK - sorry, I don't have any other suggestion.

It's Okay. Many thanks for your kind suggestions.

 

[NARAYANK991]

Hi ,

Something like this :

=SUM(INDIRECT("Timesheet!" & "AQ" & ROW() & ":" & "AZ" & ROW()))

Narayan

 

[paulcherianc]

Hi ,

Something like this :

=SUM(INDIRECT("Timesheet!" & "AQ" & ROW() & ":" & "AZ" & ROW()))

Narayan

Wow! It works !!!

Would you please try to give a solution for my other two :


OT =SUMIF(Timesheet!M7:AM7,">10")-COUNTIF(Timesheet!M7:AM7,">10")*10

Total Days=COUNTIF(Timesheet!M7:AM7,">0")+COUNTIF(Timesheet!AQ7:AZ7,">0")


Many Thanks for your kind help!!!

 

[NARAYANK991]

Hi ,

Use the same technique.

=SUMIF(INDIRECT("Timesheet!" & "M" & ROW() & ":" & "AM" & ROW()),">10")-COUNTIF(INDIRECT("Timesheet!" & "M" & ROW() & ":" & "AM" & ROW()),">10")*10

=COUNTIF(INDIRECT("Timesheet!" & "M" & ROW() & ":" & "AM" & ROW()),">0")+COUNTIF(INDIRECT("Timesheet!" & "AQ" & ROW() & ":" & "AT" & ROW()),">0")

Narayan

 

[paulcherianc]

You are a Genius!!! Thanks a million!