[Solved] Formula in col B, adjacent only to populated cells

[lesbentley]

Place formula in column B, adjacent only to populated cells.

I have a formula "=VLOOKUP(A4;$E$4:$F$50;2;0)" (that Zizi64 kindly pointed me to). Column A can contain anywhere between one and several thousand entries. All data cells in column A are contiguous, there are no blank cells after the start of the data until the end of the data.

The results of the formula are to be displayed in column B.

Is there a way to automatically apply or insert the formula "=VLOOKUP(A4;$E$4:$F$50;2;0)" to/into every cell in column B that is next to a populated cell in column A, but not into those cells that are below the end of the data in column A?

I would seem such a shame to have to add the formula to 9,999,999 cells, when in some cases only 6 cells may have data in them.

Someone suggested "=IF(ISBLANK(A4);"";VLOOKUP(A4;$E$4:$F$50;2;0))". Perhaps I am using it the wrong way, as it does not seem to help. There were no instructions on where to place it, and I am a newbie, who needs a lot of hand holding.

Below is a version of the spreadsheet with the formula manually inserted into some cells in column B.

Vloop-TEST-0002.ods

This is what I have so far.

(14.04 KiB) Downloaded 78 times

[RusselB]

The second formula will check if A4 is blank, and if it is return a null character to the cell that the formula is in (probably B4 from what I understand)

To easily copy the formula as you have requested, enter the formula into the first row of column B that has an entry in column A.
Then put the cursor back on that same cell....it probably moved when you hit Enter to finish entering the formula.
Next put the mouse on the handle (black square at the bottom right of the highlighted cell)...NOTE: It can be tricky to get the mouse onto the handle.
Once you have the mouse positioned, double click with the left mouse button (or right button if your mouse is configured for left handed operation)

[lesbentley]

Thanks for the clear and detailed explanation. I was hoping to be able to automate the process. The problem is, that to create blank spreadsheet covering all contingencies, I would need to paste the formula into 9,999,999 cells. This might be be OK in those spreadsheets with 8,827,633 data entries, but would seem to create an unnecessarily large file in cases where the spreadsheet only contains 6 cells with data. On the other hand I don't want to have to custom edit every individual spreadsheet, one for a data range of 6, one for a data range of 75, one for 162, and another for 1,862. It would take for ever, and make the project unviable.

I am hoping there is a way to automate the application/insertion of the formula into only those cells in column B that are adjacent to populated cells in column A. Either by a formula, or a macro, or a combination of both, or any other means. I should add that I know nothing of creating macros in Calc, and almost nothing about spreadsheets in general.

[FJCC]

RusselB's instructions explained exactly how to do what you are asking. His method will automatically fill the formula down column B for as many rows as are populated in column A. Please try it and let us know if you have trouble.

[lesbentley]

Apologies RusselB. Your solution worked.