How to apply formula to whole column, when cells are temporarily blank, without dragging down?
Our take
In column H, I will have the prices of books I'm ordering (I'm a librarian). In column K, I want to have those prices with $2.54 added to each price to reflect the cost of processing. So I have the simple formula =H+2.54, but I want to apply it to every cell in column K. I haven't started ordering books yet, so the cells are all blank. If I just do the drag-down method, every cell will show $2.54, and that will skew other data in the spreadsheet regarding how much has been spent so far. So I want to have the formula apply to all the cells in column K *while leaving the cells temporarily blank*.
The tips I've been reading about this are mostly about creating a table, which seems too complicated for an Excel dummy like me. I'm looking for a simple way to do this, if there is one. It seems like the IF or ISBLANK functions might be another way, but if so, I'm hoping someone can give me the exact formula I need to make this happen. I don't have the intuitive Excel skills to figure this out based on the tips I've seen online.
Thank you!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- What formula should I be using to populate one column’s data automatically based on my entry for another column?Hi! I need some help here. first off, I’m not allowed to use a v-lookup for this file due to some organisational restrictions, so i’m unable to create a table 2 to facilitate the lookup values. I’m only allowed to use table 1 itself, which is where I have to put in all my values. I have been trying to use index/match instead but can’t see to figure the right formula out. so basically, what I need is for my manual input of data in column J to populate the associated remark in column K. • column J input “NA” = column K input “visit 2” • column J input “nil” = column K input “insufficient blood” • column J input “1” = column K input blank these are the only 3 that I require. let’s say I require it for 30 rows. so my range of rows to populate would be K2:K30 based on the directly adjacent data of J2:J30. how can I do this in formula or automation? thanks in advance! submitted by /u/yanny-jo [link] [comments]
- If K2 equals X1 (pulled from sheet 2) then I2 equals X2 (pulled from sheet 2)I am trying to create a formula where I have a drop down in column K linked to sheet 2 column 1. I want column I in sheet 1 to auto populate based on the response from dropdown K. To be specific, column K contains GL names (office supplies, miscellaneous, lunches, etc.), and I want all office supplies to be 5200, misc. to be 5300, etc. I keep getting different errors when trying to use XLOOKUP/VLOOKUP/IF. Thank you so much for your help! submitted by /u/batmandi [link] [comments]
- Increment row but not column when copying a formula?Trying to fix an old spreadsheet at work. The current formula is this: =SUM(R6,R16,R26,R36,R46,R56) I need to build 4 more columns to the right of the existing one with the same formula, that increments the row but leaves the column the same, like this: =SUM(R7,R17,R27,R37,R47,R57) I don't think I can use $ for this as that always locks the column right? Photo Example below. I need to add three more Flav columns on the right hand side, pulling from lists like on the left hand side. https://preview.redd.it/v9iyxye9xrwg1.png?width=604&format=png&auto=webp&s=c0ad4b7502a0ec344221e29965de8dda99ed045e submitted by /u/fullofpaint [link] [comments]
- Change contents of cells based on a drop downSo, I'm trying to generate a spreadsheet to make the forms we use in the office to issue keys. When people are given an apartment, we have a sheet with all the key numbers on it, and then they sign for it. At the moment, we've got 22 different PDFs and Word documents, and it doesn't work. So, what I want to do is this. I've built the template in Excel and created a Reference sheet which will have all the numbers in it. What I am trying to do is set it up so that when you use the Apartment number from a dropdown, it populates with all the key numbers. This sounds like it should be easy to do, but my Excel brain is broken. Advice would be much appreciated! submitted by /u/Hot_Syrup_1774 [link] [comments]