• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

updating formula for multiple cells

C

coolgaff

New Member
hi i have 1 tab that has 21 columns across by 16 rows down within this are formulas that reference a template on another page which the data is manually added first, current cell formula is =SMC_Ticket_Grading!E10

i have manually been adding this bit to each cell IF(SMC_Ticket_Grading!E10="","",where the cell ref match up so the complete new cell formula will be like this =IF(SMC_Ticket_Grading!E4="","",SMC_Ticket_Grading!E$4)


is there any way for me to drag or do more than one cell at a time ensuring that the column letter and row number change as i have 280 cells to change to this new formula, and i dont fancy doing each one at a time.file available if needed to review/fix.


cheers
 
Coolgaff,

You need to be careful with your use of $ signs in cell references.

Your formula will be locked to Row 4 due to teh $ sign at the end

=IF(SMC_Ticket_Grading!E4="","",SMC_Ticket_Grading!E$4)


You may want to try:

=IF(SMC_Ticket_Grading!E4="","",SMC_Ticket_Grading!E4)


Have a read of

http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/
 
Thanks, this partially works but the column letter stays the same i need the column letter to change as i drag down the way


as i drag this formula down the rows the number does change but the letter does not how can i get the letter to change?


=IF(SMC_Ticket_Grading!E10="","",SMC_Ticket_Grading!E10)
 
sorry my mistake i need the number to stay the same in each cell but the letter to change as i drag down the way


example

firstcell =IF(SMC_Ticket_Grading!E10="","",SMC_Ticket_Grading!E10)

second cell =IF(SMC_Ticket_Grading!F10="","",SMC_Ticket_Grading!F10)

third cell =IF(SMC_Ticket_Grading!G10="","",SMC_Ticket_Grading!G10)
 
so to lock the Numbers

=IF(SMC_Ticket_Grading!E$10="","",SMC_Ticket_Grading!E$10)


Please read:

http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/
 
Thanks this is what i have been doing but as i drag the cell down i need the column ref letter to change. currently the formula above keeps everything the same i have tried the $ in all different options.
 
Whoops

Drag it Across and then Copy and Paste Special, Transpose.
 
You must log in or register to reply here.
Back
Top